PostgreSQL - Common autoincrement with inherited tables

Question

I'm currently trying the inheritance system with PostgreSQL but I have a problem with the auto-increment index in my child tables.

I have three tables: "Currency", "Crypto" and "Stable"

CREATE TABLE IF NOT EXISTS public.currency
(
    id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name VARCHAR(30) UNIQUE NOT NULL,
    symbol VARCHAR(10) UNIQUE NOT NULL,
);

CREATE TABLE IF NOT EXISTS public.stable (id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY) INHERITS (public.currency);    
CREATE TABLE IF NOT EXISTS public.crypto (id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY) INHERITS (public.currency);

I insered my data like this:

INSERT INTO public.stable (name, symbol) VALUES ('Euro', '€'), ('Dollar', '$'), ('Tether', 'USDT');    
INSERT INTO public.crypto (name, symbol) VALUES ('Bitcoin', 'BTC'), ('Ethereum', 'ETH'), ('Litcoin', 'LTC');

But this is my problem: I would like to have a unique identifier that increments itself through my parent table "Currency". When I select, I have (take a look in my id: 1,2,3,1,2,3):

But, Is it possible to have something like this instead (1,2,3,4,5,6):

Is it a problem in my primary key?

Thank you

D-Shih · Accepted Answer · 2022-04-30 02:34:12Z

2

We can try to use create sequence to set row numbers for sharing between multiple tables.

define a new sequence generator

create sequence n_id;

Then we can use this sequence as below, sharing this sequence for those three tables.

create sequence n_id;

CREATE TABLE IF NOT EXISTS currency
(
    id INT default nextval('n_id') PRIMARY KEY,
    name VARCHAR(30) UNIQUE NOT NULL,
    symbol VARCHAR(10) UNIQUE NOT NULL
);

CREATE TABLE IF NOT EXISTS stable (id INT default nextval('n_id') PRIMARY KEY) INHERITS (currency);    
CREATE TABLE IF NOT EXISTS crypto (id INT default nextval('n_id') PRIMARY KEY) INHERITS (currency);

sqlfiddle

edited Apr 30, 2022 at 2:34

answered Apr 30, 2022 at 2:28

D-Shih

46.5k6 gold badges38 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Naografix Over a year ago

So cool thank you! But is it the only way? I mean, I have an other parent table with 4 childs. Do I need to create a sequence too? Or this is the only one way to "share" the auto increment

D-Shih Over a year ago

In my experience, create yourself sequence that shares the same increment id object for multiple tables

Collectives™ on Stack Overflow

PostgreSQL - Common autoincrement with inherited tables

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related