How to make one identifier for 2 tables?

Question

PostgreSQL

The task is to make one identifier (auto increment) for 2 tables.
Actually, there is data and demo data, they should fly into 2 different tables, but at the same time, for correct access at the front, they should not have IDs intersect.

That is, if 3 demo records fly into one table, then a non-demo record will be flooded into the next one with identifier 4.

As I understand the option - partitions, but it's not entirely clear how to create such a rule.

Answer 1

The idea is stupid, to be honest, but you can do:

create sequence shared_sequence;
create table production_data(id bigint default nextval('shared_sequence'), value text);
create table demo_data(id bigint default nextval('shared_sequence'), value text);

Answer 2

Postgresql does not have auto-increment, postgresql does have sequences.
Actually take the value (nextval) from the same sequence when inserting a record into both tables - get what you need.

Answer 3

You are in any case to maintain the logic of this key outside of the base.
You can make non-intersecting increments (one from 1 to 100, the second from 101 to 200, like that) you can make an external table, you can refuse the numeric key and use uuid.

Answer 4

And I would switch from id to key. Then not only two tables but ten nodes will be able to process and save the same thing in parallel. Those. Key describes the record, not some randomly generated id.