CREATE TABLE countrymeasurements
(
countrycode int NOT NULL,
countryname character varying(30) NOT NULL,
languagename character varying (30) NOT NULL,
daysofoperation character varying(30) NOT NULL,
salesparts bigint,
replaceparts bigint
)
PARTITION BY LIST(countrycode)
(
partition india values(1),
partition japan values(2),
partition china values(3),
partition malaysia values(4)
);
I am getting ERROR: syntax error at or near "(". What i am missing here. I am using postgres12
I don't know where you found that syntax, obviously not in the manual. As you can see there partitions are created using create table .. as partition of in Postgres:
Define the table:
CREATE TABLE countrymeasurements
(
countrycode int NOT NULL,
countryname character varying(30) NOT NULL,
languagename character varying (30) NOT NULL,
daysofoperation character varying(30) NOT NULL,
salesparts bigint,
replaceparts bigint
)
PARTITION BY LIST(countrycode);
Define the partitions:
create table india
partition of countrymeasurements
for values in (1);
create table japan
partition of countrymeasurements
for values in (2);
create table china
partition of countrymeasurements
for values in (3);
create table malaysia
partition of countrymeasurements
for values in (4);
Welcome to stackoverflow! Please note, that asking questions here without showing prior research may turn away people that otherwise might love to help.
In this case I checked and found no official example for list partitioning. But, if you just shorten your statement it will create a table using the values in countrycode column to partition:
CREATE TABLE countrymeasurements
(
countrycode int NOT NULL,
countryname character varying(30) NOT NULL,
languagename character varying (30) NOT NULL,
daysofoperation character varying(30) NOT NULL,
salesparts bigint,
replaceparts bigint
)
PARTITION BY LIST(countrycode)
;
The psql describe table command shows the partitioning is as requested:
psql=# \d countrymeasurements
Table "public.countrymeasurements"
Column | Type | Collation | Nullable | Default
-----------------+-----------------------+-----------+----------+---------
countrycode | integer | | not null |
countryname | character varying(30) | | not null |
languagename | character varying(30) | | not null |
daysofoperation | character varying(30) | | not null |
salesparts | bigint | | |
replaceparts | bigint | | |
Partition key: LIST (countrycode)
Then you can define the partitions like in the answer from @a_horse_with_no_name. But, some notes on using such a strategy may be in order.
Notes: