PostgreSQL FAQ贴 【转】
前端之家收集整理的这篇文章主要介绍了
PostgreSQL FAQ贴 【转】，
前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。
QQ群里一些网友问到的问题，收集如下 :
目录 :
2. Postgresql9.1的同步事务在某些情况下用户主动cancel等待sync replication standby 的acknowledge,实际本地已提交.
3.
Postgresql如何满足已经存在则更新,不存在则插入的需求.
4.
copy和insert哪个效率高?
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
14. Postgresql 不同的版本,只读事务中是否允许 nextval() 取序列值;
15.修改数据库记录的时候,如何才能做到指
修改日期,而不
修改时间? 例如 2012-06-16 08:20:12 改为 2012-07-01 08:20:12 ?
16. Postgresql 什么情况下模糊查询可以使用索引扫描?
17. INT类型如何显示前导0,例如01,001.
19.在postgre
sql写
sql语句，更新字段里的值，该字段的值是这样的形式{"x":114.310134,"y":30.522772,"spatialReference":{"wkid":4326}，需要更新字符串里X Y的值，值是从其它变表查出来的，该如何写
sql语句呢？
20. Postgre
sql 列类型匹配判断. IS OF,IS NOT OF表达式.
1. Q :
A :
2. Q :
Postgre
sql9.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 :
Postgre
sql如何满足已经存在则更新,不存在则插入的需求.
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 ;
adigoal
4. Q :
copy和insert哪个效率高?
COPY效率高.
例如 :
create table copy_insert_compareTABLE
insert copy_insert_compare generate_series1000000),0)">'digoal_test';
INSERT 01000000
=# 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
单一事务insert导入时间,(开始部分加入begin;结束部分加入commit;)
;
17232011
1928125秒
insert 慢了将近100倍。
5. Q :
A :
目前Postgre
sql对
数据库,表空间的大小都没有限制. 如果要限制表空间的大小,可以考虑利用
文件系统的配合来实现,如linux的quota.
6. Q :
Postgre
sql没有只读角色这个权限，但是可以通过
修改用户的默认参数来达到只读的目的，如下 :
# 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)
# \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
8. Q :
Postgre
sql 中有没有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.
Postgre
sql使用窗口
函数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 :
10. Q :
请问,计算字符串公式的能力? 类似 a=2 ; evaluate('5-a') ; 如果将这个值赋值给这个变量呢? result = evaluate('5-a') ;
A :
# 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
另外,通过Postgre
sql的客户端p
sql定义的变量也可以实现 :
设置变量和它的值
# \set a 2
postgres=# \set result 5-:a
# select :result;
?column?
----------
3
(1 row)
# \echo :a
2
postgres=# \echo :result
5-:a
# \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
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; 一般写成函数.
可参看,
13.
时区参看
timezone参数
pg_timezone_names表
或
src/backend/utils/adt/timestamp.c
14. Postgre
sql 不同的版本,只读事务中是否允许 nextval() 取序列值;
8.3 允许只读事务通过nextval()取序列值.
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 版本不允许.
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
create index idx_collate_notc_1 on collate_notc INDEX
;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Seq on collate_notc 6012.00414.631414.631)
RowsRemovedby100000
414.661 ms
)
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)
digoal=> select * from pg_collation ;
参考 :
to_char123'0000'to_char
---------
0123
)
'00000'00123
'00000.0'to_char
----------
00123.0
)
# alter database digoal set log_min_duration_statement=0;
ALTER DATABASE
或者
digoal=# alter database digoal set log_statement='all';
ALTER DATABASE
语法 :
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)
\11234\25678\3 中
详细介绍参考
20. Q:
Postgre
sql 列类型匹配判断. 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)
参考:src/backend/parser/gram.y
本文仅供自己研究学习使用，文章来源：http://blog.163.com/digoal@126/blog/static/1638770402011111274336235/，如有版权纠纷请联系我删除，email：adeng1943@126.com
原文链接：https://www.f2er.com/postgresql/195955.html
