PostgreSQL FAQ贴【转】

发布时间：2020-12-13 17:33:22 所属栏目：百科来源：网络整理

导读：QQ群里一些网友问到的问题，收集如下 : 目录 : 1. PostgreSQL存储过程中自定义异常怎么弄? 2. PostgreSQL9.1的同步事务在某些情况下用户主动cancel等待sync replication standby 的acknowledge,实际本地已提交. 3. PostgreSQL如何满足已经存在则更新,不存在

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

另外,通过PostgreSQL的客户端psql定义的变量也可以实现 :

设置变量和它的值

# set a 2

postgres=# set result 5-:a

输出变量的值

# select :result;

?column?

----------

(1 row)

输出变量的值

# echo :a

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 ;

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 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());

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

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]+()','11234256783','g');

regexp_replace

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

{"x":1234,"y":5678,"spatialReference":{"wkid":4326}

(1 row)

11234256783 中

详细介绍参考

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"

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

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?

----------

(1 row)

digoal=# select relname is of (name) from pg_class limit 1;

?column?

----------

(1 row)

PostgreSQL7.3添加到特性.

参考:src/backend/parser/gram.y

本文仅供自己研究学习使用，文章来源：http://blog.163.com/digoal@126/blog/static/1638770402022222274336235/，如有版权纠纷请联系我删除，email：adeng1943@126.com

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

PostgreSQL FAQ贴 【转】

PostgreSQL FAQ贴【转】