0

I am using Postgres 10 and Need to create the two table like below.

CREATE TABLE puplic.test1(
    id character varying(8) NOT NULL,
    name character varying(36) NOT NULL,
    status character varying(36) NOT NULL,
    CONSTRAINT pk_test1 PRIMARY KEY (id),
);
CREATE TABLE puplic.test2(
    id character varying(8) NOT NULL,
    name character varying(36) NOT NULL,
    status character varying(36) NOT NULL,
    CONSTRAINT pk_test2 PRIMARY KEY (id),
);

I want to move particular status records say "EXPIRED" from table test1 to test2.

And maintain the primary key unique data across the two table. Means my INSERT query should consider both the table primary key data before inserting data on to any one of the table.

Please help me to create tables structure with above requirements.

sadanand Gowda
  • 1
  • 1
  • Why do you have two tables with exactly the same structure. Fix your data model and make it just one table. –  May 13 '20 at 13:38
  • @a_horse_with_no_name . . . One reason would be to have foreign key constraints that only work on "expired" or "non-expired" ids. – Gordon Linoff May 13 '20 at 13:43
  • Want to move huge record set of "EXPIRED" status to table test2 and Keep minimal records in table test1 which is used for select queries. This is just a sample table I provided to make it simple. – sadanand Gowda May 13 '20 at 13:45
  • You may need to do this using triggers . . . or a scheduled process that moves such records periodically. The methods that I readily think of -- table partitioning, inheritance, and clustered indexes -- either are not supported in Postgres or do not fully support what you want to do. – Gordon Linoff May 13 '20 at 13:57

0 Answers0