All tables will use bigint as primary key, and all (indirectly) inherit from the same base table
I am not sure I like this "all tables inherit from the base table", but given that, it sounds doable with Postgres:
To generate the primary key for all tables, create one sequence:
create sequence one_for_all as bigint;
Create the base table using that sequence to generate the values:
create table base (id integer primary key default nextval('one_for_all'));
Note that the primary key will not be enforced for the child tables!
Then create the child tables:
create table t1 (t1_data integer, primary key (id)) inherits (base);
create table t2 (t2_data integer, primary key (id)) inherits (base);
If you now insert into the child tables, the sequence will be used to generate the ID:
insert into t1 (t1_data) values (100);
insert into t2 (t2_data) values (200);
To find in which table a row is located, select from the base table and include the tableoid
column, that identifies the actual table in which the row is located:
select b.*, tableoid::regclass as actual_table
from base b
where id = 42;
You will still need another query to return the full row of the child table.
Online example: https://dbfiddle.uk/?rdbms=postgres_11&fiddle=4305e86b996e7a94c24faed733257232
Another approach that does not need inheritance is to generate ID values that encode the table name (shudder).
Something along the lines:
create sequence one_for_all as bigint;
create table lookup (table_name text primary key, code serial unique);
insert into lookup (table_name) values ('base'), ('t1'), ('t2');
create function get_id(p_tablename text)
returns bigint
as
$$
select nextval('one_for_all') * 1000 + code
from lookup
where table_name = p_tablename;
$$
language sql;
create function get_tablename(p_id bigint)
returns text
as
$$
select table_name
from lookup
where code = p_id % 1000;
$$
language sql;
By multiplying the value from the sequence with 1000 we essentially have the lower 3 digits available to encode a unique number for each table. In order to be able to lookup those numbers, we need that additional table.
Note that this will fail miserably if the lookup table does not contain all tables!
Then instead of using nextval()
use, the get_id()
function:
create table base (id integer primary key default get_id('base'));
create table t1 (id integer primary key default get_id('t1'), some_value integer);
create table t2 (id integer primary key default get_id('t2'), some_data text);
Then you can do:
insert into t1 (some_value) values (42);
insert into t2 (some_data) values ('foo');
insert into t1 (some_value) values (117);
insert into t2 (some_data) values ('bar');
So the rows in t2 now go the IDs 2003 and 4003. The function get_tablename()
can be used to retrieve the table name to which an ID belongs:
select get_tablename(2003);
get_tablename
-------------
t2
This is certainly faster in terms of looking up the table name and does not carry the baggage of a huge inheritance tree with it. So performance wise it's probably faster. It will however become a maintenance nightmare.
Populating of the lookup table could perhaps be done through event triggers whenever a table is created or dropped.
If you can change your application to support varchar primary keys, instead of bigint (and you can live with the slightly higher storage requirements), you could also generate the ID as a string that simply contains the tablename:
create function get_id(p_tablename text)
returns text
as
$$
select concat(nextval('one_for_all'), '_', p_tablename)
$$
language sql;
Things like sorting and range queries will be complicated though.