postgresql 分区 partition by

按结构复制

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');

　　

 

python:

from sqlalchemy import create_engine

odoo_conn = create_engine('postgresql://user:pswd@10.10.10.10:5432/dbname')


def createPartitionByName(tabname: str, interval: int):
    tmptab = tabname + '_2'
    renamesql = '''alter table %s rename to %s''' % (tabname, tmptab)
    createsql = '''create table %s (like %s) partition by range (id)''' % (tabname, tmptab)
    seqname = tabname + '_id_seq_1'
    ownid = tabname + '.id'
    createseq = '''CREATE SEQUENCE %s OWNED BY %s;''' % (seqname, ownid)
    altersql = '''ALTER TABLE %s ALTER COLUMN id SET DEFAULT nextval('%s');''' % (tabname, seqname)
    dropsql = '''drop table %s''' % tmptab
    for sql in [renamesql, createsql, createseq, altersql]:
        print('执行sql: ', sql)
        odoo_conn.execute(sql)
        print('执行完成')

    endnum = 0
    for i in range(20):
        startnum = endnum + 1
        endnum = startnum + interval
        createPartionTable(tabname, startnum, endnum)
    print('drop tbale')
    odoo_conn.execute(dropsql)


def createPartionTable(tabname, startnum, endnum):
    print(tabname, startnum, endnum)
    print('values from', startnum, 'to', endnum)
    sql = '''CREATE TABLE %s_%s_%s PARTITION OF %s FOR VALUES FROM (%s) TO (%s);''' % (tabname, startnum, endnum, tabname, startnum, endnum+1)
    odoo_conn.execute(sql)
    print('创建完成')

createPartitionByName('cbd_huilv', interval=1000000)