PostgreSQL-实用功能性语句

（2017-06-01 银河统计）

  本篇文章对工作中使用的Postgres实用语句进行总结，方便查找和复用。

目录概览

 1)pg数据库语言支持情况查询

 2)PostgreSQL查询删除表中重复数据

 3)PostgreSQL通配符

 4)postgreSQL清除所有表或所有表中数据

 5)psql查询表大小

 6)PostgreSQL中Raise Notice用来输出消息文本的语法格式

 7)语句7

 8)语句8

 9)语句9

 10)语句10

 11)语句11

 12)语句12

 13)语句13

 14)语句14

 15)语句15

 16)语句16

 17)语句17

 18)语句18

---

1)pg数据库语言支持情况查询

SELECT * FROM pg_language;

SELECT lanpltrusted FROM pg_language WHERE lanname LIKE 'c';

SELECT * FROM pg_language WHERE lanname LIKE 'plr';

UPDATE pg_language SET lanpltrusted = true WHERE lanname LIKE 'plr';

2)PostgreSQL查询删除表中重复数据

1)查询表中重复数据

SELECT * FROM abdata
WHERE "Id"
NOT IN (
SELECT max("Id")
FROM abdata
GROUP BY "Name", "Des", "Price"
);

2)删除表中重复数据

DELETE * FROM abdata
WHERE "Id"
NOT IN (
SELECT max("Id")
FROM abdata
GROUP BY "Name", "Des", "Price"
);

3)PostgreSQL通配符

~~ 等效于 LIKE

~~* 对应 ILIKE

!~~ NOT LIKE

!~~* NOT ILIKE

匹配正则表达式，大小写相关 'thomas' ~ '.thomas.'
~* 匹配正则表达式，大小写无关 'thomas' ~* '.Thomas.'
!~ 不匹配正则表达式，大小写相关 'thomas' !~ '.Thomas.'
!~* 不匹配正则表达式，大小写无关 'thomas' !~* '.vadim.'

4)postgreSQL清除所有表或所有表中数据

CREATE or replace FUNCTION drop_table() RETURNS void AS $$
DECLARE
tmp VARCHAR(512);
DECLARE names CURSOR FOR
select tablename from pg_tables where schemaname='public';
BEGIN
FOR stmt IN names LOOP
tmp := 'DROP TABLE '|| quote_ident(stmt.tablename) || ' CASCADE;';
RAISE NOTICE 'notice: %', tmp;
-- EXECUTE 'DROP TABLE '|| quote_ident(stmt.tablename) || ' CASCADE;';
EXECUTE 'DROP TABLE '|| quote_ident(stmt.tablename) || ';';
END LOOP;
RAISE NOTICE 'finished .....';
END;
$$ LANGUAGE plpgsql;

select drop_table();

-- 然后对于单独的表，可以采用TRUNCATE TABLE name这样的方法。

5)psql查询表大小

select schemaname,tablename,pg_relation_size(schemaname||'.'||tablename) as tabsize from pg_tables order by 3 desc;

select schemaname,tablename,pg_table_size(schemaname||'.'||tablename) as tabsize from pg_tables order by 3 desc;

6)PostgreSQL中Raise Notice用来输出消息文本的语法格式

do language plpgsql
$$
declare
v_msg character varying := 'Hello World!';
begin
raise notice '%', v_msg;
end
$$;

7)语句7

8)语句8

9)语句9

10)语句10

11)语句11

12)语句12

13)语句13

14)语句14

15)语句15

16)语句16

17)语句17

18)语句18