postgresql语句学习

 

SELECT 'CREATE NODE GROUP \"' || pgxc_group.group_name || '\" WITH(' || string_agg('\"' || pgxc_node.node_name || '\"',',') || ') ' ||
(CASE WHEN pgxc_group.group_kind = 'v' THEN 'VCGROUP' ELSE '' END) || ';' AS group_query,
(CASE WHEN pgxc_group.is_installation = 'TRUE' THEN 'UPDATE pg_catalog.pgxc_group SET is_installation = TRUE, group_kind = ''i'' WHERE group_name = '''||pgxc_group.group_name||''';'
WHEN pgxc_group.in_redistribution = 'y' THEN 'UPDATE pg_catalog.pgxc_group SET in_redistribution = ''y'' WHERE group_name = '''||pgxc_group.group_name||''';' ELSE '' END)
AS update_query
FROM pg_catalog.pgxc_node, pg_catalog.pgxc_group WHERE pgxc_group.group_name IN
(SELECT group_name FROM pgxc_group WHERE group_kind != 'e' OR group_kind IS NULL)
AND pgxc_node.oid = ANY (pgxc_group.group_members) GROUP BY pgxc_group.group_kind, pgxc_group.group_name, pgxc_group.is_installation,
pgxc_group.in_redistribution ORDER BY pgxc_group.is_installation desc;

 

知识点

 

||的使用--字符串拼接

select name || remark as newname from mytable where name like '%test%';

 

https://my.oschina.net/hippora/blog/379923

https://www.cnblogs.com/alianbog/p/5656722.html

 

select * from a,b; //笛卡乘积

select * from a,b where a.id=b.id; //内连接

https://blog.csdn.net/qingmengwuhen1/article/details/73611537

 

case when

https://www.cnblogs.com/accumulater/p/6560522.html

https://www.cnblogs.com/jijm123/p/10594442.html

 

group by

https://blog.csdn.net/weixin_40743261/article/details/89857565

 

IN 和 ANY

https://blog.csdn.net/weixin_36335903/article/details/113413211

 

  

• 复制表

1. INSERT INTO FROM 语句

insert into tablebackup select * from table;
insert into tablebackup select field1,field2,field3 from table where ...;

前提：tablebackup 必须存在
2. SELECT INTO FROM 语句

select * into tablebackup from table;
select field1,field2,field3 into tablebackup from table;

原文链接：https://blog.csdn.net/xbj24215/article/details/99304980

 

两种方式得到某张表的oid，这里以表 foo 为例：

第一种：传统方式
postgres=# select oid from pg_class where relname='foo';
oid
-------
49542
(1 row)
第二种：利用pg oid别名特性
postgres=# select 'foo'::regclass::oid;
oid
-------
49542
(1 row)