Business
Jobs
  • About Us
  • Solutions
    • Job Postings
      Post your job and receive qualified candidates in 48h.
    • Candidate Assessments
      500+ technical and psychological tests, plus anti-fraud.
    • Headhunting
      Tailor-made executive search from start to finish.
    • Payroll + EOR
      Payroll dispersal and EOR across 15+ LATAM countries.
  • Pricing
  • Jobs

0

238
Views
List Partitioning in Postgres 12
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

over 4 years ago · Santiago Trujillo
2 answers
Answer question

0

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);
over 4 years ago · Santiago Trujillo Report

0

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:

  • When you just allow 4 explicit partitions via list (as you tried) what happens when value 5 comes along?
  • The documentation at postgresql 12 on ddl partition ing suggests to consider hash partitioning instead of list and choose the number of partitions instead of relying on your column values which might expose a very unbalanced abundance.
over 4 years ago · Santiago Trujillo Report
Answer question
Find remote jobs

Discover the new way to find a job!

Top jobs
Top job categories
Business
Post vacancy Pricing Sales
Legal
Terms and conditions Privacy policy
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Show me some job opportunities
There's an error!