526互联

postgresql 分区 partition by

发布时间 2023-09-06 17:15:28作者: CrossPython

按结构复制

CREATE TABLE testdb
(like testdb_old)
partition by range(vdate)

　　

 

增加附属表

CREATE TABLE cbd_cbdmodeldetails_1_2 PARTITION OF cbd_cbdmodeldetails FOR VALUES FROM (1) TO (3);
CREATE TABLE cbd_cbdmodeldetails_3_6 PARTITION OF cbd_cbdmodeldetails FOR VALUES FROM (3) TO (7);

--顾头不顾尾

　　

 

改变主键属性

1. 不存在列

-- auto-increment constraint for a new column
 ALTER TABLE public.products
    ADD COLUMN id SERIAL PRIMARY KEY;

　　

2. 已存在列但是没数据

-- create sequence
CREATE SEQUENCE public_products_id_seq OWNED BY public.products.id;

-- use sequence for the target column
ALTER TABLE public.products ALTER COLUMN id SET DEFAULT nextval('public_products_id_seq');

　　

3. 已经存在列并且已经有数据

-- create sequence
CREATE SEQUENCE public_products_id_seq OWNED BY public.products.id;

-- set the current value of the sequence to the max value from that column
-- (id column in this scenario)
SELECT SETVAL('public_products_id_seq', (select max(id) from public.products), false)

-- use sequence for the target column
ALTER TABLE public.products ALTER COLUMN id SET DEFAULT nextval('public_products_id_seq');