pgsql常用表方法和函数

pg_class: 保存着所有表

select * from pg_class

字段：

  relnamespace ：包含这个关系的名字空间(模式)的 OID。（引用 pg_namespace.oid）
  relname： 表、索引、视图等的名称。

select pg_class.relname	
from pg_class, pg_namespace
where pg_class.relnamespace = pg_namespace.oid

pg_namespace：存储命名空间

字段：

  oid: 主键
  nspname : 命名空间的名称

pg_inherits:记录有关表和索引继承层次结构的信息

字段：

  inhrelid ：子表或索引的 OID
  inhparent： 父表或索引的 OID

/*查询库中所有表继承关系*/
select concat('alter table ', child_table, ' no inherit ', parent_table, ';') from (
select
child_class.relname child_table,
parent_class.relname parent_table
from pg_class child_class inner join pg_inherits child_inherits 
  on child_inherits.inhrelid = child_class.oid
  inner join pg_class parent_class 
  on child_inherits.inhparent = parent_class.oid
  where child_class.relnamespace = 2200
   and child_class.relkind = 'r') a;


/**查询被继承得所有表*/
select 	concat('drop table ', dtable  , ';') from (
  select DISTINCT pg_class.relname dtable
  from pg_class inner join pg_inherits 
  on pg_inherits.inhparent = pg_class.oid 
  where pg_class.relnamespace = 2200
  and relkind = 'r') b;

pg_attribute:存储有关表列的信息

字段：

  attrelid：此列所属的表 参考资料pg_class.oid
  attname： 列名称
  attnum :  列的编号。普通列的编号从 1 开始

/*查询表中所有字段*/
select pg_attribute.attname  字段名
from pg_attribute  left join pg_class 
on pg_attribute.attrelid = pg_class.oid
where pg_attribute.attnum >= 1
  and pg_class.relnamespace = 2200
  and relkind = 'r'
  and pg_class.relname = 'tabletest'

/*查询一个表中有另一个表中没有的字段*/
select pg_attribute.attname 字段名
from pg_attribute left join pg_class 
on pg_attribute.attrelid = pg_class.oid
where pg_attribute.attnum >= 1
  and pg_class.relnamespace = 2200
  and relkind = 'r'
  and pg_class.relname = '表1'
  and pg_attribute.attname not in (
  select pg_attribute.attname 字段名
  from pg_attribute left join pg_class 
  on pg_attribute.attrelid = pg_class.oid
  where pg_attribute.attnum >= 1
       and pg_class.relnamespace = 2200
       and relkind = 'r'
       and pg_class.relname = '表2');

pg_aggregate ：存储有关聚合函数的信息

常用方法和函数

SQL字符串函数和运算符：

连接两个字符串： text || text    
连接所有参数的文本表示形式。NULL 参数将被忽略： concat('abcde', 2, NULL, 22)→abcde222
用分隔符连接除第一个参数之外的所有参数： concat_ws(',', 'abcde', 2, NULL, 22)→abcde,2,22
返回字符串中的字符数： char_length ( text)  
根据数据库区域设置的规则将字符串转换为全部小写： lower ( text)  
根据数据库区域设置的规则将字符串转换为全部大写： upper('tom')→TOM
替换字符串： overlay('Txxxxas' placing 'hom' from 2 for 4)→Thomas
将查询出来列的数据拼接一起：string_agg(distinct project.project_name, ',') project_name

数据类型格式化函数

根据给定格式将时间戳转换为字符串: to_char(timestamp '2002-04-20 17:31:12.66', 'HH24:MI:SS')→17:31:12
根据给定格式将间隔转换为字符串:  to_char(interval '15h 2m 12s', 'HH24:MI:SS')→15:02:12
根据给定格式将字符串转换为日期:  to_date('05 Dec 2000', 'DD Mon YYYY')→2000-12-05
根据给定格式将字符串转换为数字:   to_number('12,454.8-', '99G999D9S')→-12454.8
根据给定格式将字符串转换为时间戳: to_timestamp('05 Dec 2000', 'DD Mon YYYY')→2000-12-05 00:00:00-05

日期/时间运算符

向日期添加天数(date + integer):  date '2001-09-28' + 7→2001-10-05
向日期添加间隔(date + interval):  date '2001-09-28' + interval '1 hour'→2001-09-28 01:00:00
减去日期，得出经过的天数(date - date):  date '2001-10-01' - date '2001-09-28'→3
从日期中减去天数(date - integer): date '2001-10-01' - 7→2001-09-24

日期/时间函数

当前日期: current_date
一天中的当前时间: current_time
当前日期和时间: current_timestamp
函数date_part，获取日期相差天数:
   date_part('day', cast(now() as TIMESTAMP) - cast(last_task_plan_date as TIMESTAMP)) as day_num

EXTRACTdate_part

EXTRACT(field FROM source) : 该函数从日期/时间值中检索子字段，例如年或小时。

dow(星期日 到星期六,为星期几):  SELECT EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40');

doy(一年中的某一天 （1–365/366）): SELECT EXTRACT(DOY FROM TIMESTAMP '2001-02-16 20:38:40');

year(年份字段): SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40');

hour(小时字段 （0–23）): SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40');

minute(分钟字段 （0–59）): SELECT EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 20:38:40');
month(月份数 （1–12）): SELECT EXTRACT(MONTH FROM TIMESTAMP '2001-02-16 20:38:40');

条件表达式

/*-----CASE---- */
SELECT a,
       CASE WHEN a=1 THEN 'one'
            WHEN a=2 THEN 'two'
            ELSE 'other'
       END
    FROM test;

/*-----COALESCE
   该函数返回其第一个不为 null 的参数----*/
SELECT COALESCE(NULL, 'TESTHHH', '(none)')

/*-----NULLIF
   如果值 1 等于值 2，则该函数返回空值;否则返回值 1----*/
SELECT NULLIF('HHHH', 'HHHH')