Re: Best way to create unique primary keys across schemas?

OK, thanks for replys. To sum up, this is what I now consider best practice:

CREATE schema schema1;
CREATE schema schema2;
CREATE SEQUENCE global_seq; --in public schema
CREATE TABLE tbl (ID bigint default nextval('global_seq') primary key,foo
varchar,bar varchar); --in public schema
CREATE TABLE schema1.tbl (LIKE public.tbl INCLUDING ALL); --draws ids from
sequence in public schema
CREATE TABLE schema2.tbl (LIKE public.tbl INCLUDING ALL); --draws ids from
sequence in public schema
INSERT INTO schema1.tbl (foo,bar) VALUES ('asdf','qwer');
INSERT INTO schema2.tbl (foo,bar) VALUES ('hello','world');
INSERT INTO schema1.tbl (foo,bar) VALUES ('asdf','qwer');
INSERT INTO schema2.tbl (foo,bar) VALUES ('hello','world');

P.S.: This is to figure out good strategies to make pg work with Hibernate
4's multi tenancy feature and I personally dislike handling large UUIDs,
especially during debugging. Plus I see no particular reason to prefer them
over bigint sequences in the general case (but this is a different topic).

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Best-way-to-create-unique-primary-keys-across-schemas-tp5165043p5430441.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.