CODEWITHSUNDEEP
postgresqlsequence
KSloan
KSloan

Reputation: 31

common sequence across multiple tables

I'm trying to create a sequence owned by multiple tables, to have a unique identifier over more tables. The only workaround i can think about right know is using a nextval(blabla_id_seq) when INSERTing, but obviously it wont work when using COPY (or some other situations which i can't think of right now).
So anybody know a solution or another workaround for this ? Purpose of question is mostly educational.

Cheers, Don

LE And can i implement a global primary key for two (or more) table , parent and child? Currently i tried 




DROP TABLE IF EXISTS child;
DROP TABLE IF EXISTS parent;

CREATE TABLE parent (
id serial PRIMARY KEY
, categ varchar(20) NOT NULL

);

CREATE TABLE child (
else varchar (30) NOT NULL
, id integer -- i have also tried with no id in child table, on;y when using   
--"id serial" does id become primary key
, CONSTRAINT PK__child PRIMARY KEY (id)

) INHERITS (parent);

COPY parent (categ)
FROM 'E:\\1\\_parent.csv'
WITH CSV;

COPY child(categ,altceva)
FROM  'E:\\1\\_child.csv'
WITH CSV;

INSERT INTO child (id,categ,altceva)
--VALUES(nextval('parent_id_seq')+3,'kid7','blabla');
VALUES(5,'kid7','blabla');

but i can insert duplicates

Upvotes: 3

Views: 4758

Answers (1)

Supr
Supr

Reputation: 19042

This question is old and all, but here's a simple answer.

This looks like some sort of inheritance model and the natural solution to that in my mind is to use a common base table. The base table consists at the least of a single serial column. Optionally it may contain fields for attributes that are common for all the objects/nodes in the system.

node(serial node_id, timestamp creation_time default now())

book(integer node_id [foreign key => node.node_id], string title, timestamp published_time)
user(integer node_id [foreign key => node.node_id], string name, timestamp joined_time, string display_name)
car (integer node_id [foreign key => node.node_id], string model, timestamp manufactured_time, real miles)

When you want to add a new object, or node as it were, then you first insert into node and then use the returned autogenerated serial id to insert further details into the subtyped tables.

This doesn't require any special considerations like transactions, checks, triggers and so on, nor will creation ever fail due to uniqueness errors.

As an extension, you can add a string type field to the node table and gain the ability to accept any node_id and straightforwardly find the corresponding object.

Upvotes: 6

Related Questions