Friday, March 8, 2019

How to create a RANGE partitioned table in PostgreSQL


Create the table:
create table album
(
album_id integer generated always as identity,
album_name varchar(40),
genre varchar(20),
label varchar(40),
release_year date
)
partition by range( extract (year from release_year) );

Create a couple of partitions, including a default partition:
create table albums_1974
partition of album
for values from ( '1974' ) to ('1975'); 

create table albums_1979
partition of album
for values from ( '1979' ) to ('1980'); 

create table albums_default 
partition of album DEFAULT;

Another example using a timestamp column as a partition key:
create table shared_docs (
 share_id character      varying(100),
 docid character varying(100),
 identificator character  varying(100),
 package character varying(100),
 fromd timestamp without time zone,
 tod timestamp without time zone,
 rev_year character     varying(100),
 creationtime timestamp without time zone,
 agreedparty character varying(300)
) partition by range (creationtime);
Create the partitions:
create table docs_jan2022 partition of shared_docs for values from    ('2022-01-01 00:00:00') to ('2022-02-01 00:00:00');
create table docs_feb2022 partition of shared_docs for values from    ('2022-02-01 00:00:00') to ('2022-03-01 00:00:00');
create table docs_march2022 partition of shared_docs for values from   ('2022-03-01 00:00:00') to ('2022-04-01 00:00:00');


Yet another example using an integer column:
CREATE TABLE mytable
(
    key text COLLATE pg_catalog."default" NOT NULL,
    outdata jsonb,
    partno integer,
    koffset bigint,
    handled_at timestamp with time zone,
    inserted_at timestamp with time zone NOT NULL DEFAULT now(),
    kyear integer,
    nk boolean NOT NULL,
    CONSTRAINT mytable_pkey PRIMARY KEY (id,kyear),
    CONSTRAINT key_unique_int UNIQUE (key,kyear)
)
partition by range (kyear);
Create the partitions:
create table mytable_2021
partition of mytable
for values from (2021) to (2022);

create table mytable_2022
partition of mytable
for values from (2022) to (2023);

No comments:

Post a Comment