PostgreSQL 模式

PostgreSQL 模式基本上是一个名空间：它包含有名的对象（表、数据类型、函数及运算符），其名可能会和其他模式的现有对象相同。

模式名：创建的模式名。名不能以 pg_ 开头，因为这些名是保留给系统模式。

拥有者：拥有模式的用户名。如果省略，默认为运行命令的用户。

PostgreSQL 表

关联式数据库使用表来保存数据，全部数据作都在表上完成或生成另一个表作为结果。表是由行和列，以及行列相交的栏位组成。从一般的角度来看，列在一个表中描述数据的名和类型；行在一个表中代表列组成的记录，从左至右由相应列的名和类型描述。每一个在行中的栏位是和该行的其他栏位含蓄地相关。点击“表”按钮即可打开表的对象列表。

当要打开有图形栏位的表时，在表上右击并在弹出菜单中选择“打开表（快速）”，用更快的性能打开图形表，BLOB 栏位（图片）将不会被加载直到点击单元格。如果需要在打开表时 Nicat 加载全部的图片需要点击“打开表”。

可以创建一个表快捷方式，在对象列表中的表上右击并在弹出菜单中选择“创建打开表快捷方式”。这个选项是用来提供一个打开表的便捷方式，可以直接输入数据而无需打开主 Nicat。

要清空一个表，在已选择的表上右击并在弹出菜单中选择“清空表”。此选项仅适用于清除全部现有记录而不重设自动递增值。如果需要在清除表的同时重设自动递增值，请使用“截断表”。

如何为 PostgreSQL 增加系统表字段

1为pg_database增加一个字段 datdummy，打开 /src/include/catalog/pg_database.h：

CATALOG(pg_database,1262) BKI_SHARED_RELATION BKI_ROWTYPE_OID(1248) BKI_SCHEMA_MACRO

{NameData datname; / database name /

Oid datdba; / owner of database /

int32 encoding; /syench/scripting/script_lua.c: lua_setglobal(state, "_rand_gaussian"); character encoding /

NameData datcollate; / LC_COLLATE setting /

NameData datctype; / LC_CTYPE setting /

bool datistemplate; / allowed as CREATE DATABASE template? /

bool datallowconn; / new connections allowed? /

int32 datconnlimit; / max connections allowed (-1=no limit) /

Oid datlastsysoid; / highest OID to consider a system OID /

TransactionId datfrozenxid; / all Xids < this are frozen in this DB /

TransactionId datminmxid; / all multixacts in the DB are >= this /

Oid dattablespace; / default table space for this DB /

#ifdef CATALOG_VARLEN / variable-length fields start here /

aclitem datacl[1]; / access permissions /

#endif

} FormData_pg_database;

/ ----------------

Form_pg_database corresponds to a pointer to a tuple with

the format of pg_database relation.

----------------

/

typedef FormData_pg_database Form_pg_database;

/ ----------------

compiler constants for pg_database

----------------

/

#define Natts_pg_database 13

#define Anum_pg_database_datname 1

#define Anum_pg_database_encoding 3

#define Anum_pg_database_datcollate 4

#define Anum_pg_database_datctype 5

#define Anum_pg_database_datistemplate 6

#define Anum_pg_database_datconnlimit 8

#define Anum_pg_database_datlastsysoid 9

#define Anum_pg_database_datfrozenxid 10

#define Anum_pg_database_datminmxid 11

#define Anum_pg_database_datacl 13

它们终会被脚本 genbki.pl 利用生成 postgresql.bki文件，用在 initdb 初始化 data cluster 时，有兴趣可以自己学习一下，PG编译系统也是一个充分展示强大 perl 语言的系统；

3、在 dattablespace 下增加新定义：

#define Natts_pg_database 14

...

#define Anum_pg_database_datdummy 13

#define Anum_pg_database_datacl 14

预定义的数据库必须也要修改，_null_ 前边增加 100的字段为 datdummy 数据：

DATA(insert OID = 1 ( template1 PGUID ENCODING "LC_COLLATE" "LC_CTYPE" t t -1 0 0 1 1663 100 _null_));

4、编译运行，会发生什么，编译正常，然后 initdb，竟然也神奇的通过了，难道这就好了吗？

Tip：Linux 下不停在一个目录下改代码，可能会遇到莫名的程序问题，试试 make clean

5、那么创建一个数据库试试看：CREATE DATABASE quanzl; 成功了，是不是感觉似乎还缺点什么？

预订义的数据库比如template1，我们可以在catalog里边定义 BKI 脚本，比如上边的例子，给它一个初始值。程序里也必须有所改动才能成为可作属性；

6、参照语法修改，创建一个 CREATE DATABASE xxx DUMMY nnn语法，修改结构体 CreatedbStmt 增加新属性，语法分析阶段将此值读入，创建数据库时将它写入属性；

new_record[Anum_pg_database_datdummy - 1] = 1234;

此部分代码在 src/backend/commands/dbcommands.c 中，自行阅读好了，写程序就这么简单。:)

postgreSQL创建一个触发器函数：更新过student1表的数据后，更新student1_stats表中数据。

PostgreSQL中大概是这样创建触发器:

首先需要创建触发器调用的函数:

create or replace function tg_update()

returns trigger

as $$

-- 更新SQL, 可以使用NEW和OLD分别取新记录和旧记录

update student1_stats src

set ....

where ...;

return null; -- 要返回null

end;

$$ language plpgsql;然后，创建触发器:

create trigger trigger_name

after update on studen1

for each row execute procedure tg_update();

PostGreSQL Json数据存储和条件查询

创建一张志愿者的数据表,记录每批参加志愿活动的人员名单。其中人员信息保存在json字段中。

知识点 : (1)postgresql中自增长的id创建。 (2)修改表字段语句。 (3)标准sql中table name ,colu--2.3 查询在某个表空间上的对像mn name双引号。

查询年龄大于等于25岁以上的志愿者

知识点 : (1)查询结果的的row number生成。 (2)获取json对象中的子对象。 (3)转换json对象属性的数据类型。

怎样让postgresql创建数据库时默认为UTF8编码

新建一个模板，比如

template_utf8

，设置好utf-8以及plpgsql之类的lang还有插件，从这个数据库新建就全是uft-8了。

如果是更改的话，需要先把数据库导出为sql,

再新建，而后导入。导入前，修改sql里的create

语句，指定字符集。有汉字的话，另存为utf-8的编码，再导入。

怎样设置PostgreSQL中字段和表名对大小写敏感

加双引号，建议在编写 SQL 脚本的时候，使用单引号做字符串常量的标识，虽然在 MS SQLServer 中双引号和单引号均可作为字符串常量的标识符，但是在 PostgreSQL 中对此就有严格的功能区分（不能用双引号作为字符串常量的标识符），为了保险起见，建议对所有的数据库对象名均使用双引号（"）将其显式约定，以保证大小 写的严格匹配。

postgresql 查看表建立哪些索引

在数据库运维工作中，经常会有数据目录使用率较高需要调整的情况，通常会给数据库建立多个表空间，

并分别位于不同的盘上，这时需要做的工作就是调整库中现有表和索引的表空间，下面简单总结下这块维护

工作的内容，以下都是基于 PostgreSQL 9.0.1 做的测试。

一 查询某个表所在表空间的简单方法

PostgreSQL 提供类似" \ "命令很方便得到相关信息，命令如下：

skytf=> \d test_2

Table "skytf.test_2"

Column | Type | Modifiers

--------+-----------------------+-----------

id | integer |

obj_id | integer | not null

name | character varying(64) |

Indexes:

"idx_hash_name" hash (name)

"idx_test_2" btree (id, obj_id)

Tablespace: "tbs_skytf_idx"

备注：如果这个表的表空间为当前数据库的默认表空间，那么上面则不会显示 Tablespace 信息，

相反，则会显示这张有的表空间，例如上面的表 test_2 的表空间为 tbs_skytf_idx，而

表空间 "tbs_skytf_idx" 不是数据库 skytf 的默认表空间， 那么如何查询数据库的默认

表空间呢，可以通过以下命令查询。

--1.1 查询数据库的默认表空间

skytf=> select datname,dattablespace from pg_database where datname='skytf';

datname | dattablespace

---------+---------------

skytf | 14203070

(1 row)

skytf=> select oid,spcname from pg_tablespace where oid=142#define Anum_pg_database_datallowconn 703070;

oid | spcname

----------+-----------

14203070 | tbs_skytf

(1 row)

备注：通过以上查出数据库 skytf 的默认表空间为 tbs_skytf。

二 批量查询数据库表和索引的表空间

--2.1 查询表和索引所在的表空间

select relname, relkind, relpages,pg_size_pretty(pg_relation_size(a.oid)), tb.spcname

from pg_class a, pg_tablespace tb

where a.reltablespace = tb.oid

and a.relkind in ('r', 'i')

order by a.relpages desc;

备注：上面只取了部分结果，这个查询能够查询表和索引所处的表空间，但是有一点需要注意，这个查询

仅显示表空间不是数据库默认表空间的数据库对像，而我们通常需要查出位于数据库默认表空间的

对像，显然上面的查询不是我们想要的，接下来看另一个查询。

--2.2 查询位于默认数据库表空间的对像

select relname, relkind, relpages,pg_size_pretty(pg_relation_size(a.oid)),reltablespace,relowner

from pg_class a

where a.relkind in ('r', 'i')

and reltablespace='0'

order by a.relpages desc;

备注：这个查询加入限制条件 reltablespace='0'，即可查找出位于当前数据库默认表空间的

数据库表和索引。 通常这才是我们想要的结果，接下来可以把部分表转移到其它表空间上去，转移

的方法可以用 "ALTER TABLE move tablespace "或者重建索引移表空间等方法，这里不详细介绍。

select relname, relkind, relpages,pg_size_pretty(pg_relation_size(a.oid)),reltablespace,relowner

from pg_class a, pg_tablespace tb

where a.relkind in ('r', 'i')

and a.reltablespace=tb.oid

and tb.spcname='tablespace_name'

order by a.relpages desc;

--2.4 手册上对于 pgclass 视图的 reltablespace 字段解释

The tablespace in which this relation is stored. If zero, the database is default tablespace is

implied. (Not meaningful if the relation has no on-disk file.)

如何用 syench 并行装载 PostgreSQL 测试数据

syench原来自带的lua数据装载脚本是使用以下方式串行装载的，速度比较慢(比单条insert快，但是比COPY慢)。

insert into table1 values (),(),()....

insert into table2 values (),(),()....

...

insert into tablen values (),(),()....

使用prepare导入数据的用法举例

./syench_pg --test=lua/oltp.lua --db-driver=pgsql --pgsql-host=127.0.0.1 --pgsql-port=1921 --pgsql-user=postgres --pgsql-password=postgres --pgsql-db=postgres --oltp-tables-count=64 --oltp-table-size=1000000 --num-threads=64 prepare

prepare 表示装载数据，但是它串行的。

在syench中自定义的lua脚本中要求实现以下几个函数：

function thread_init(thread_id): 此函数在线程创建后只被执行一次

function event(thread_id): 每执行一次就会被调用一次。

由上可以知道，本次造数据的脚本我们只需要实现thread_init()函数就可以了。

生成mysql=# select oid from pg_class where relname like 'helloworld%';测试数据的脚本沿用老唐提供的代码：

#include

#include

#include

#include

#include

uint64_t my_rand(struct random_data r1, struct random_data r2)

{uint64_t rand_max = 100000000000LL;

uint64_t result;

random_r(r1, &u1);

random_r(r2, &u2);

result = (int64_t)u1 (int64_t)u2;

result = result % rand_max;

return result;

}int main(int argc, char argv[])

{struct timeval tpstart;

struct random_data r1, r2;

int i;

int r;

int max_value;

char rand_state1[128];

char rand_state2[128];

if (argc !=2)

{printf("Usage: %s \n", argv[0]);

return 1;

}max_value = atoi(argv[1]);

gettimeofday(&tpstart,NULL);

initstate_r(tpstart.tv_usec,rand_state1,sizeof(rand_state1),&r1);

srandom_r(tpstart.tv_usec, &r1);

gettimeofday(&tpstart,NULL);

initstate_r(tpstart.tv_usec,rand_state2,sizeof(rand_state1),&r2);

srandom_r(tpstart.tv_usec, &r2);

{r = my_rand(&r1, &r2) % max_value;

printf("%d,%d,%011llu-%011llu-%011llu-%011llu-%011llu-%011llu-%011llu-%011llu-%011llu-%011llu,%011llu-%011llu-%011llu-%011llu-%011llu\n",

r,

my_rand(&r1, &r2)

);

}return 0;

gcc gen件事是使用\password命令，为postgres用户设置一个密码。data.c -o gendata

新建一个copy.lua的脚本，内容如下

调用 common.lua中的 set_vars() 继承来自 common.lua 的全局变量。

函数 copydata(table_id) ： 创建表，创建管道，将管道数据传输到psql -c "copy ..."客户端的方式导入数据。

函数 create_index(table_id) ： 创建索引，调整SEQUENCE next val。

注意咯, oltp_tables_count 必须是 num_threads 的倍数，在 thread_init 中， 以num_threads 为步调，以thread_id+1为起始值，设置i的值，并调用copydata(table_id)和create_index(table_id)。

$ vi lua/copy.lua

pathtest = string.match(test, "(./)") or ""

dofile(pathtest .. "common.lua")

function copydata(table_id)

local query

query = [[

CREATE UNLOGGED TABLE test]] .. table_id .. [[ (

id SERIAL NOT NULL,

k INTEGER,

c CHAR(120) DEFAULT '' NOT NULL,

pad CHAR(60) DEFAULT '' NOT NULL,

PRIMARY KEY (id)

) ]]

db_query(query)

os.execute ('export PGPASSWORD=' .. pgsql_password)

os.execute ('rm -f test' .. table_id .. '.dat')

os.execute ('mknod test' .. table_id .. '.dat p')

os.execute ('./gendata ' .. oltp_table_size .. ' >> test'..table_id ..'.dat &')

os.execute ('cat test' .. table_id .. '.dat | psql -h ' .. pgsql_host .. ' -p ' .. pgsql_port .. ' -U ' .. pgsql_user .. ' -d ' .. pgsql_db .. ' -c "copy test' .. table_id .. ' from stdin with csv"')

os.execute ('rm -f test' .. table_id .. '.dat')

end

function create_index(table_id)

db_query("select setval('test" .. table_id .. "_id_seq', " .. (oltp_table_size+1) .. ")" )

db_query("CREATE INDEX k_" .. table_id .. " on test" .. table_id .. "(k)")

end

function thread_init(thread_id)

set_vars()

print("thread prepare"..thread_id)

for i=thread_id+1, oltp_tables_count, num_threads do

copydata(i)

create_index(i)

end

end

function event(thread_id)

os.exit()

end

用法，必须把psql放到路径中，因为lua中需要用到psql命令

export PATH=/home/digoal/pgsql9.5/bin:$PATH

./syench_pg --test=lua/copy.lua \

--db-driver=pgsql \

--pgsql-host=127.0.0.1 \

--pgsql-port=1921 \

--pgsql-user=postgres \

--pgsql-password=postgres \

--pgsql-db=postgres \

--oltp-tables-count=64 \

--oltp-table-size=1000000 \

--num-threads=64 \

run

清除数据, drop table

./syench_pg --test=lua/copy.lua \

--db-driver=pgsql \

--pgsql-host=127.0.0.1 \

--pgsql-port=1921 \

--pgsql-user=postgres \

--pgsql-password=postgres \

--pgsql-db=postgres \

--oltp-tables-count=64 \

--oltp-table-size=1000000 \

--num-threads=64 \

cleanup

lua全局变量代码：

syench/scripting/lua/src/lua.h:#define lua_register(L,n,f) (lua_pushcfunction(L, (f)), lua_setglobal(L, (n)))

syench/scripting/lua/src/lua.h:#define lua_setglobal(L,s) lua_setfield(L, LUA_GLOBALSINDEX, (s))

syench/scripting/lua/src/lbaselib.c: lua_setglobal(L, "_G");

syench/scripting/lua/src/lbaselib.c: lua_setglobal(L, "_VERSION"); / set global _VERSION /

syench/scripting/lua/src/lbaselib.c: lua_setglobal(L, "newproxy"); / set global `newproxy' /

syench/scripting/script_lua.c: lua_setglobal(state, opt->name);

syench/scripting/script_lua.c: lua_setglobal(state, "_rand");

syench/scripting/script_lua.c: lua_setglobal(state, "_rand_uniq");

syench/scripting/script_lua.c: lua_setglobal(state, "_rnd");

syench/scripting/script_lua.c: lua_setglobal(state, "_rand_str");

syench/scripting/script_lua.c: lua_setglobal(state, "_rand_uniform");

syench/scripting/script_lua.c: lua_setglobal(state, "_rand_special");

syench/scripting/script_lua.c: lua_setglobal(state, "db_connect");

syench/scripting/script_lua.c: lua_setglobal(state, "db_disconnect");

syench/scripting/script_lua.c: lua_setglobal(state, "db_query");

syench/scripting/script_lua.c: lua_setglobal(state, "db_bulk_insert_init");

syench/scripting/script_lua.c: lua_setglobal(state, "db_bulk_insert_next");

syench/scripting/script_lua.c: lua_setglobal(state, "db_bulk_insert_done");

syench/scripting/script_lua.c: lua_setglobal(state, "db_prepare");

syench/scripting/script_lua.c: lua_setglobal(state, "db_bind_param");

syench/scripting/script_lua.c: lua_setglobal(state, "db_bind_result");

syench/scripting/script_lua.c: lua_setglobal(state, "db_execute");

syench/scripting/script_lua.c: lua_setglobal(state, "db_close");

syench/scripting/script_lua.c: lua_setglobal(state, "db_store_results");

syench/scripting/script_lua.c: lua_setglobal(state, "db_free_results");

syench/scripting/script_lua.c: lua_setglobal(state, "DB_ERROR_NONE");

syench/scripting/script_lua.c: lua_setglobal(state, "DB_ERROR_DEADLOCK");

syench/scripting/script_lua.c: lua_setglobal(state, "DB_ERROR_FAILED");

syench/scripting/script_lua.c: lua_setglobal(L, "db_driver");

传入参数，可以把syench_pg的参数-替换成_在lua脚本中使用这些变量，例子

--pgsql-host=127.0.0.1 -> 对应lua中的变量名 pgsql_host

--pgsql-port=1921 -> 对应lua中的变量名 pgsql_port

--pgsql-user=postgres -> 对应lua中的变量名 pgsql_user

--pgsql-password=postgres -> 对应lua中的变量名 pgsql_password

--pgsql-db=postgres -> 对应lua中的变量名 pgsql_db

--oltp-tables-count=64 -> 对应lua中的变量名 oltp_tables_count

--oltp-table-size=1000000 -> 对应lua中的变量名 oltp_table_size

--num-threads=64 -> 对应lua中的变量名 num_threads

求postgreSQL 的数据库的表数据，导入导出的sql语句，谢谢！

1， 导出esa数据库所有对象到esa.backup文件

pg_dump.exe --host localhost --port 5432 --username 1，确实没有创建这个表postgres --format custom --blobs --verbose --file "E:\Dl\esa.backup" esa

2， 导入esa.backup文件中esa数据库所有对象到esa数据库中

p● Unique Unique：保证一个表中没有两行在键列有相同的值。g_restore.exe --host localhost --port 5432 --username postgres --dbname esa --verbose "E:\Dl\esa.backup"

如何快速掌握 Nicat for PostgreSQL表索引

在“索引”选项卡，只需简单地点击索引栏位就可以编辑，使用索引工具栏，可以方便地创建新的、编辑或删除选定的索引栏位。

● 添加索引：添加一个索引到表。1(1 row)

● 删除索引：删除已选择的索引。

使用“名”编辑框来设置索引名。

索引包含栏位：简单地双击“栏位”或点击栏位按钮，就可以打开编辑器来编辑。

索引类型：定义表索引的类型。

● Non-unique Non-unique：不施加（unique）索引的限制到列值。

● Bitmap：在 bitmap 索引，每键值用 bitmap 后边字段序号的定义也是很重要的，必须按顺序修改，也要记得属性数相应修改：代替 rowids 列表。

与度数并行：当用户有大量数据及多个处理器，并行索引可以提高索引的表现，输入决定分割索引进程数的度数。

表空间：创建索引的表空间。索引可以创建在与它索引的表相同或不同的表空间。

模式：创建索引的模式。

温馨提示：要在自己的模式创建索引，至少必须满足以下一项条件：

● 要创建索引的表或簇是在自己的模式。

● 有 INDEX 权限在要创建索引的表。

● 有 CREATE ANY INDEX 系统权限。

要在其他模式创建索引，必须满足以下全部条件：

● 有 CREATE ANY INDEX 系统权限。

● 其他模式的拥有者有配额给表空间包含索引或索引分割区，或 UNLIMITED TABLESPACE 系统权限。