PostgreSQL FAQ贴

概述QQ群里一些网友问到的问题，收集如下 :  目录 :  1. PostgreSQL存储过程中自定义异常怎么弄? 2. PostgreSQL9.1的同步事务在某些情况下用户主动cancel等待sync replication standby 的acknowledge,实际本地已提交. 3.  PostgreSQL如何满足已经存在则更新, 不存在则插入的需求. 4.  copy和insert哪个效率高 QQ群里一些网友问到的问题，收集如下 : 目录 : 1. Postgresql存储过程中自定义异常怎么弄? 2. Postgresql9.1的同步事务在某些情况下用户主动cancel等待sync replication standby 的ackNowledge,实际本地已提交. 3. Postgresql如何满足已经存在则更新,不存在则插入的需求. 4. copy和insert哪个效率高? 5. Postgresql能不能限制数据库的大小? 6. 怎样给一个用户授予只读角色? 7. 不想让数据插入到某个表应该怎么做? 8. Postgresql 中有没有rownum这样的，显示结果集的序号? 9. Postgresql 函数中如何使用savepoint? 10.请问,pg脚本有宏替换,计算字符串公式的能力? 类似 a=2 ; evaluate('5-a') ; 如果将这个值赋值给这个变量呢? zresult = evaluate('5-a') ; 11.UPDATE A表 FROM B表 ? 12. hex转decimal 13. Postgresql 时区. 14. Postgresql 不同的版本,只读事务中是否允许 nextval() 取序列值; 15.修改数据库记录的时候,如何才能做到指修改日期,而不修改时间? 例如 2012-06-16 08:20:12 改为 2012-07-01 08:20:12 ? 16. Postgresql 什么情况下模糊查询可以使用索引扫描? 17. INT类型如何显示前导0,例如01,001. 18.postgresql能不能把在某个数据库上执行过的所有sql都记录下来. 19.在postgresql写SQL语句，更新字段里的值，该字段的值是这样的形式{"x":114.310134,"y":30.522772,"spatialReference":{"wkID":4326}，需要更新字符串里X Y的值，值是从其它变表查出来的，该如何写SQL语句呢？ 20. Postgresql 列类型匹配判断. IS OF,IS NOT OF表达式.
内容 : 1. Q : postgresql存储过程中自定义异常怎么弄? A : http://www.postgresql.org/docs/9.1/static/plpgsql-errors-and-messages.html
2. Q : Postgresql9.1的同步事务在某些情况下用户主动cancel等待sync replication standby 的ackNowledge,实际本地已提交.返回如下: canceling the wait for synchronous replication and terminating connection due to administrator command. The transaction has already committed locally,but might not have been replicated to the standby. 或者, canceling wait for synchronous replication due to user request The transaction has already committed locally,but might not have been replicated to the standby. A : 原因是, * If a wait for synchronous replication is pending,we can neither * ackNowledge the commit nor raise ERROR or FATAL. The latter would * lead the clIEnt to belIEve that that the transaction aborted,which * is not true: it's already committed locally. The former is no good * either: the clIEnt has requested synchronous replication,and is * enTitled to assume that an ackNowledged commit is also replicated, * which might not be true. So in this case we issue a WARNING (which * some clIEnts may be able to interpret) and shut off further output. * We do NOT reset ProcDIEPending,so that the process will dIE after * the commit is cleaned up. 或者 * It's unclear what to do if a query cancel interrupt arrives. We * can't actually abort at this point,but ignoring the interrupt * altogether is not helpful,so we just terminate the wait with a * suitable warning. 详细参考,src/backend/replication/syncrep.c
3. Q : Postgresql如何满足已经存在则更新,不存在则插入的需求.

               digoal=> create table exists_test (ID int primary key,info text);               NOTICE: CREATE table / PRIMARY KEY will create implicit index "exists_test_pkey" for table "exists_test"               CREATE table                       digoal create or replace function insert_exists_testi_ID inti_info text) returns voIDas $BODY$               declare               begin               perform 1from exists_test where ID=i_ID;               ifnot found then               insert into exists_testIDinfo values i_info);               return;               else               update exists_test set infoi_info endifexception               when others then               raise exception 'Insert exists_test(ID,info) values(%,%) error.'end$BODY$ language plpgsql;                  select(1'digoal');              'adigoal' * exists_test ;                   ID | info                ----+---------                    adigoal    

4. Q : copy和insert哪个效率高? copY效率高. 例如 :

create table copy_insert_comparetable insert copy_insert_compare generate_serIEs1000000),0)">'digoal_test'; INSERT 01000000

copy到文件

=# copy digoal.copy_insert_compare to '/home/postgres/copy_insert_compare';

导出为insert语句 pg_dump -f ./copy_insert_compare.sql F p E UTF8 t digoalcopy_insert_compare --inserts h 127.0.0.1U digoal digoal rwr postgres postgres 19M Dec1312:07 copy_insert_compare 61M09 copy_insert_comparesql

首先文件大小就相差几倍. copY导入时间， # copy digoal.copy_insert_compare from '/home/postgres/copy_insert_compare'; copY 1000000 Time: 1456.939 ms

insert导入时间， postgres@db-172163150-> date;nohup psql digoal digoal >/dev/null2>&date; Tue1448 CST 2011 562011128秒

单一事务insert导入时间,(开始部分加入begin;结束部分加入commit;) ; 17232011 1928125秒

insert 慢了将近100倍。
5. Q : Postgresql能不能限制数据库的大小? A : 目前Postgresql对数据库,表空间的大小都没有限制. 如果要限制表空间的大小,可以考虑利用文件系统的配合来实现,如linux的quota.
6. Q : 怎样给一个用户授予只读角色? Postgresql没有只读角色这个权限，但是可以通过修改用户的默认参数来达到只读的目的，如下 : # select usename,useconfig from pg_user where usename='digoal'; usename | useconfig ---------+----------- digoal | (1 row) digoal=# alter role digoal set default_transaction_read_only=true; ALTER RolE digoal=# select usename,0)">usename | useconfig ---------+-------------------------------------- digoal | {default_transaction_read_only=true} (1 row)

连接到digoal用户进行验证, # \c digoal digoal You are Now connected to database "digoal" as user "digoal". digoal=> insert into test values (1); ERROR: cannot execute INSERT in a read-only transaction digoal=> delete from test ; ERROR: cannot execute DELETE in a read-only transaction digoal=> drop table test ; ERROR: cannot execute DROP table in a read-only transaction

重置用户参数 : \c postgres postgres You are Now connected to database "postgres" user "postgres". postgres# alter role digoal reset default_transaction_read_only; ALTER RolE postgres=# select usename,0)">(1 row)

7. Q : 不想让数据插入到某个表应该怎么做? 创建do instead nothing规则,例如 create rule r_insert on insert to test do instead nothingRulE test values 0 test ID ---- rows)

8. Q : Postgresql 中有没有rownum这样的，显示结果集的序号? A : 在Oracle里面rownum可以用来标示行号,如 :

sql> rownumtable_name dba_tables <10; ROWNUM table_name ---------------------------------------- ICol$ 2 CON$ 3 UNDO$ 4 PROXY_RolE_DATA$ 5 file$ 6 UET$ 7 IND$ 8 SEG$ 9 Col$ rows selected.

Postgresql使用窗口函数row_number()可以满足同样的需求

              # select * from (select row_number() over() as rownum,tablename from pg_tables) t where rownum<10;                    rownum |  tablename                      --------+-----------------                       1 | pg_statistic                       2 | pg_type                       3 | pg_attribute                       4 | pg_authID                       5 | pg_proc                       6 | pg_class                       7 | pg_database                       8 | pg_user_mapPing                       9 | pg_constraint                    (9 rows)      

9. Q : Postgresql 函数中如何使用savepoint? A : http://blog.163.com/digoal@126/blog/static/1638770402011112635938640/
10. Q : 请问,计算字符串公式的能力? 类似 a=2 ; evaluate('5-a') ; 如果将这个值赋值给这个变量呢? result = evaluate('5-a') ; A : Postgresql支持DO来执行匿名块,实现类似上述的功能如下 :

              # do $$                    postgres$# declare                    postgres$# a int;                    postgres$# result int;                    postgres$# begin                    postgres$# a := 2;                    postgres$# result := 5-a;                    postgres$# raise notice '%',result;                    postgres$# end                    postgres$# $$                    postgres-# ;                    NOTICE: 3                    DO      

另外,通过Postgresql的客户端psql定义的变量也可以实现 : 设置变量和它的值

                  # \set a 2                          postgres=# \set result 5-:a        

输出变量的值

                  # select :result;                          ?column?                          ----------                              3                          (1 row)        

输出变量的值 # \echo :a 2 postgres=# \echo :result 5-:a

输出当前psql下的所有变量 # \set autoCOMMIT = 'on' PROMPT1 = '%/%r%# ' PROMPT2 = '%/%r%# ' PROMPT3 = '>> ' VERBOSITY = 'default' VERSION = 'Postgresql 9.1.2 on x86_64-unkNown-linux-gnu,compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-51),64-bit' DBname = 'postgres' USER = 'postgres' HOST = '127.0.0.1' PORT = '1931' ENCoding = 'UTF8' LASTOID = '0' a = '2' result = '5-:a'

删除变量 \unset a \unset result

可以参考man psql
11. Q : UPDATE A表 FROM B表 ? A : 首先A表和B表需要有关联的列,关联之后A表和B表应该是多对一或者一对一的关系, 如果是一对多会怎么样呢? 测试如下 : sar create table a info text); "a_pkey""a" table sar create table b a ; 10 b 'Digoal''DIGOAL'= info ----+-------- digoal row) Digoal DIGOAL )

执行如下更新之后,a.ID 会等于什么呢? 是Digoal,还是DIGOAL. update a binfo aandUPDATE 1 Digoal 看起来是第一次匹配到的B表的那条记录的info值. 所以在做多表关联的更新 *** 作时,需要注意这一点.
12. 基本的用法是 SELECT x'FF'::integer; 一般写成函数. 可参看, http://postgresql.1045698.n5.nabble.com/Hex-to-Dec-Conversion-td3218223.html
13. 时区参看 timezone参数 http://www.postgresql.org/docs/9.1/static/runtime-config-client.html#RUNTIME-CONFIG-CLIENT-FORMAT pg_timezone_names表 http://www.postgresql.org/docs/9.1/static/view-pg-timezone-names.html 或 src/backend/utils/adt/timestamp.c
14. Postgresql 不同的版本,只读事务中是否允许 nextval() 取序列值; 8.3 允许只读事务通过nextval()取序列值.

                    psql                   Welcome to psql 8.35 the Postgresql interactive terminal.                               # create sequence seq_test;                   CREATE SEQUENCE                   postgres=# begin transaction read only;                   BEGIN                   postgres=# select nextval('seq_test'::regclass);                   nextval                   ---------                      1                   (1 row)           

9.1 版本不允许. psql 9.1) Type "help" help. # create sequence seq_test; CREATE SEQUENCE postgres=# begin transaction read only; BEGIN postgres=# select nextval('seq_test'::regclass); ERROR: cannot execute nextval() in a read-only transaction

其他版本未测试 .
15. 修改数据库记录的时候,而不修改时间? 例如 2012-06-16 08:20:12 改为 2012-07-01 08:20:12 ? column_name::time+changed_date for example create table test col1 timestamp0)); table postgresNow()); 1 test col1 --------------------- 20120702135338 ) update test col1 '2011-01-01'col10101)

16. Postgresql 什么情况下模糊查询可以使用索引扫描? 分为两种情况,
1. collate = C

 create table collate_c  info text collate "C");                          "collate_c_pkey""collate_c"                          table                                  collate_c 100000repeatrandom()::text20);                       100000              

索引使用默认的operator class,即可支持"后模糊"查询.

                          create index IDx_collate_c_1 on collate_c INDEX                                        explain analyze  info ~'^12';                                                    query PLAN                                                    ------------------------------------------------------------------------------------------------------------------------------                       IndexScanusing IDx_collate_c_1 on collate_c cost0.00..2.8110 wIDth348actual time0.0300.030 loops)                       Cond((info >='12' AND <'13'))                       FilterTotal runtime0.056 ms                       )                                        info like '12%'0.0260.026~~0.051)              

2. collate <> C 第二种情况是列的collate <> C. 这种使用默认的operator class不支持模糊查询.

                          create table collate_notc "en_US");                       "collate_notc_pkey""collate_notc"                       table                                collate_notc );                      100000                                       create index IDx_collate_notc_1 on collate_notc INDEX                                      ;                                               query PLAN                                               ----------------------------------------------------------------------------------------------------------------                      Seq on collate_notc 6012.00414.631414.631)                      RowsRemovedby100000                      414.661 ms                      )                                      157.486157.486157.508)                                       create index IDx_collate_notc_2 on collate_notc info varchar_pattern_ops);                      INDEX                      ;                                                    query PLAN                                                    ------------------------------------------------------------------------------------------------------------------------------------                       IDx_collate_notc_2 on collate_notc 0.4820.482)                      ~>=~~<~))                      0.506 ms                      )                                             0.0730.0730.097)                                                                          drop index IDx_collate_notc_2DROP INDEX                        info                          query PLAN                                                ----------------------------------------------------------------------------------------------------------------                       344204.449204.449100000                       204.471)                                                enable_seqscanoffSET              

但是,当查询的结果集只包含索引列字段时,可以选择Index Only Scan. 当然这个效率显然不如使用ops来的高.

;                                                           query PLAN                                                                                     ------------------------------------------------------------------------------------------------------------------------------------                          -------------                          Only IDx_collate_notc_1 on collate_notc 11870.79746.763746.763 row                          s)                          100000                          HeapFetches746.786 ms                          )                                                                                  query PLAN                                                        ---------------------------------------------------------------------------------------------------------------------------------                          10000000000.0010000006012.00175.617175.617175.646)        

查询支持的collate digoal=> select * from pg_collation ; 参考 : http://www.postgresql.org/docs/9.2/static/indexes-opclass.html to_char123'0000'to_char --------- 0123 ) '00000'00123 '00000.0'to_char ---------- 00123.0 )

18. postgresql能不能把在某个数据库上执行过的所有sql都记录下来. # alter database digoal set log_min_duration_statement=0; ALTER DATABASE 或者 digoal=# alter database digoal set log_statement='all'; ALTER DATABASE -- 只记录下digoal数据库的所有sql,其他数据库则按系统默认的配置. 语法 : ALTER DATABASE name SET configuration_parameter { TO | = } { value | DEFAulT } ALTER DATABASE name SET configuration_parameter FROM CURRENT ALTER DATABASE name reset configuration_parameter ALTER DATABASE name reset ALL Postgresql的权限分级较多,例如可以按角色配置权限,也可以按数据库配置权限,也可以按会话配置权限等等.

19. Q : 在postgresql写SQL语句，更新字段里的值，该字段的值是这样的形式{"x":114.310134,"spatialReference":{"wkID":4326}，需要更新字符串里X Y的值，值是从其它变表查出来的，该如何写SQL语句呢？ A : digoal=# select regexp_replace ('{"x":114.310134,"spatialReference":{"wkID":4326}','()[0-9]+\.[0-9]+(,"y":)[0-9]+\.[0-9]+()','\11234\25678\3','g'); regexp_replace ------------------------------------------------------- {"x":1234,"y":5678,"spatialReference":{"wkID":4326} (1 row) 234678 中 详细介绍参考 http://www.postgresql.org/docs/9.3/static/functions-matching.html#FUNCTIONS-POSIX-REGEXP
20. Q: Postgresql 列类型匹配判断. IS OF,u5b8bu4f53; Font-size:16px"> A: digoal=# \d+ pg_class table "pg_catalog.pg_class" Column | Type | ModifIErs | Storage | Stats target | Description ----------------+-----------+-----------+----------+--------------+------------- relname | name | not null | plain | | relnamespace | oID | not null | plain | | reltype | oID | not null | plain | | reloftype | oID | not null | plain | | relowner | oID | not null | plain | | relam | oID | not null | plain | | relfilenode | oID | not null | plain | | reltablespace | oID | not null | plain | | relpages | integer | not null | plain | | reltuples | real | not null | plain | | relallvisible | integer | not null | plain | | reltoastrelID | oID | not null | plain | | reltoastIDxID | oID | not null | plain | | relhasindex | boolean | not null | plain | | relisshared | boolean | not null | plain | | relpersistence | "char" | not null | plain | | relkind | "char" | not null | plain | | relnatts | smallint | not null | plain | | relchecks | smallint | not null | plain | | relhasoIDs | boolean | not null | plain | | relhaspkey | boolean | not null | plain | | relhasrules | boolean | not null | plain | | relhastriggers | boolean | not null | plain | | relhassubclass | boolean | not null | plain | | relispopulated | boolean | not null | plain | | relfroZenxID | xID | not null | plain | | relminmxID | xID | not null | plain | | relacl | aclitem[] | | extended | | reloptions | text[] | | extended | | Indexes: "pg_class_oID_index" UNIQUE,btree (oID) "pg_class_relname_nsp_index" UNIQUE,btree (relname,relnamespace) Has OIDs: yes
digoal=# select relname is of (text) from pg_class limit 1; ?column? ---------- f (1 row) digoal=# select relname is of (name) from pg_class limit 1; ?column? ---------- t (1 row) Postgresql7.3添加到特性. 参考:src/backend/parser/gram.y 本文仅供自己研究学习使用，文章来源：http://blog.163.com/digoal@126/blog/static/1638770402011111274336235/，如有版权纠纷请联系我删除，email：adeng1943@126.com 总结

以上是内存溢出为你收集整理的PostgreSQL FAQ贴 全部内容，希望文章能够帮你解决PostgreSQL FAQ贴 所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。