PostgreSQL常用操作

1. 创建分区表

1）创建主表

CREATE TABLE z_test (
    uuid bpchar(36) NOT NULL,
    cr_date date NOT NULL DEFAULT CURRENT_DATE,
    cr_time timestamptz NOT NULL DEFAULT now(),
    name varchar(6) NOT NULL,
    update_time timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT z_test_sap_sd_so_staging_pk PRIMARY KEY (cr_date, uuid)
)
PARTITION BY RANGE (cr_date);

2.1）创建分区表

CREATE table if not exists  z_test_202107
PARTITION OF z_test
FOR VALUES FROM ('2021-07-01') TO ('2021-08-01');

CREATE table if not exists  z_test_202108
PARTITION OF z_test
FOR VALUES FROM ('2021-08-01') TO ('2021-09-01');

-- 或者
CREATE table if not exists  z_test_202107
PARTITION OF z_test
FOR VALUES FROM ('2020-06-01') TO ('2020-07-01');

2.2)创建default分区

create table z_test_def
partition of z_test default

3）新增测试数据

INSERT INTO stagingavatar.z_test_sap_sd_so_staging
(uuid, cr_date, cr_time, name, update_time)
VALUES('1', CURRENT_DATE, now(), 'a', now());

INSERT INTO stagingavatar.z_test_sap_sd_so_staging
(uuid, cr_date, cr_time, name, update_time)
VALUES('1', CURRENT_DATE + integer '30', now(), 'a', now());

4） 查询分区表信息

SELECT
    nmsp_parent.nspname AS parent_schema ,
    parent.relname AS parent ,
    nmsp_child.nspname AS child ,
    child.relname AS child_schema
FROM
    pg_inherits JOIN pg_class parent
        ON pg_inherits.inhparent = parent.oid JOIN pg_class child
        ON pg_inherits.inhrelid = child.oid JOIN pg_namespace nmsp_parent
        ON nmsp_parent.oid = parent.relnamespace JOIN pg_namespace nmsp_child
        ON nmsp_child.oid = child.relnamespace
WHERE
    parent.relname = 'z_test';

5）查询分区表数据分布情况

SELECT p.relname,c.tableoid,c.*
FROM z_test c, pg_class p
WHERE c.tableoid = p.oid

6）删除分区表
 

DROP TABLE z_test_202107;

7）根据主表信息查分区表表名

select
    c.relname
from
    pg_class c
    join pg_inherits i on i.inhrelid = c. oid
    join pg_class d on d.oid = i.inhparent
where
    d.relname = 'z_test ';

8）新增分区表列

不影响rule的前提下直接alter

如：

ALTER TABLE z_test ADD test_column1 varchar(20) NULL;

9.1）新增约束

ALTER TABLE z_test ADD CONSTRAINT z_test_pkey PRIMARY KEY(id);

9.2）修改约束

ALTER INDEX z_test RENAME TO whatever_new_name

2.列出所有表名和数据库名

1）列出表名

SELECT   tablename   FROM   pg_tables  
WHERE   tablename   NOT   LIKE   'pg%'  
AND tablename NOT LIKE 'sql_%'
ORDER   BY   tablename;

2）列出数据库名
\l
或

SELECT datname FROM pg_database;

切换数据库
\c 数据库名

查看表结构：

\d table

---

\l 

\d table

\?

3.PostgreSQL查询表名称、表结构、主键（多个）（借鉴）

https://www.cnblogs.com/xy-ouyang/p/13303359.html