2 tables that use the same sequence to generate primary key in postgres

All we need is an easy explanation of the problem, so here it is.

Could I use the same sequence to generate the primary keys on two separate tables for instance like this?

CREATE TABLE IF NOT EXISTS public."user_Registered"
(
    id integer NOT NULL DEFAULT nextval('user_id_seq'::regclass),
    email character varying COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT "PK_1" PRIMARY KEY (id),
    CONSTRAINT "UQ_1" UNIQUE (email)
)
CREATE TABLE IF NOT EXISTS public."user_Applicant"
(
    id integer NOT NULL DEFAULT nextval('user_id_seq'::regclass),
    email character varying COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT "PK_2" PRIMARY KEY (id),
    CONSTRAINT "UQ_2" UNIQUE (email)
)

In my example the registered users have a lot more data than just the email and I can’t store this in the same table and populate when they register. So my plan is to save them in the applicant table and then move them to the registered table but I want to preserve the ID.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Yes, you can do this and it works.

Imagine it works something like this: you have a virtual, 3rd, parent table (we could it call it User) and both the Registered and Applicant tables have an FK that references User (id). So you get almost all the functionality of that without having to maintain the User table.

If later you want to add a 3rd (or 4th) table with similar design and possibly different extra columns, it’s pretty straightforward as well. You just add the nextval('user_id_seq'::regclass) in their primary key definition.

One disadvantage is that if you need the IDs from both tables for some query, you’ll have to do a UNION of Applicant and Registered.

Another disadvantage is that some ORMs might be problematic to handle this design.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply