[PostgreSQL] ERROR: adding primary key to a partitioned table is not yet implemented

[sanyamsinghal]

Jira Link: DB-13486 If we have a Partitioned Table in which PRIMARY KEY is added later on using ALTER TABLE then it fails with the following error:

ERROR: adding primary key to a partitioned table is not yet implemented (SQLSTATE XX000)

Workaround: Add PRIMARY KEY in the CREATE TABLE statement and remove corresponding ALTER TABLE statement.

Sample Exported Schema:

CREATE TABLE public.sales_region (
    id integer NOT NULL,
    amount integer,
    branch text,
    region text NOT NULL
)
PARTITION BY LIST (region);

ALTER TABLE ONLY public.sales_region ADD CONSTRAINT sales_region_pkey PRIMARY KEY (id, region);

Suggested Changes:

CREATE TABLE public.sales_region (
    id integer NOT NULL,
    amount integer,
    branch text,
    region text NOT NULL,
    PRIMARY KEY(id, region)
)
PARTITION BY LIST (region);

[mrajcevic01]

I had initially created a new GH issue for this, not knowing one already existed - https://github.com/yugabyte/yb-voyager/issues/1285

There have been additional users who have run into issues with the way we export schema into two different DDLs (CREATE TABLE & ALTER TABLE...ADD CONSTRAINT...PRIMARY KEY) . I have added the examples in the issue I created above but will re-iterate here:

1. Creating the table with the primary key is faster than creating the table and then later changing the primary key, causing longer import times.
2. We get an error when trying to migrate a table that needs to be partitioned on the target. This is a process specific to taking an existing application and making changes for it be geo-partitioned. (meaning it was not partitioned on the source but instead we edit the SQL file after the export so that it will be partitioned on the target). The error below:

x_restore_multi_region=# ALTER TABLE ONLY public.account_values ADD CONSTRAINT account_values_pkey PRIMARY KEY (id); ERROR: insufficient columns in PRIMARY KEY constraint definition DETAIL: PRIMARY KEY constraint on table "account_values" lacks column "geo_location" which is part of the partition key. x_restore_multi_region=# ALTER TABLE ONLY public.account_values ADD CONSTRAINT account_values_pkey PRIMARY KEY (id,geo_location); ERROR: changing primary key of a partitioned table is not yet implemented HINT: See https://github.com/yugabyte/yugabyte-db/issues/16980. React with thumbs up to raise its priority

We can avoid both of these by creating the PRIMARY KEY as part of the CREATE TABLE syntax as shown in our examples.