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