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

Chris Angelico wrote
>
>
> You can "share" a sequence object between several tables. This can
> happen somewhat unexpectedly, as I found out to my surprise a while
> ago:
>
> CREATE TABLE tbl1 (ID serial primary key,foo varchar,bar varchar);
> INSERT INTO tbl1 (foo,bar) VALUES ('asdf','qwer');
> CREATE TABLE tbl2 LIKE tbl1 INCLUDING ALL;
> INSERT INTO tbl2 (foo,bar) VALUES ('hello','world');
>
> Both tables will be drawing IDs from the same sequence object, because
> "create table like" copies the default value, not the "serial"
> shorthand. (It makes perfect sense, it just surprised me that the IDs
> were looking a little odd.)
>
Wow, this is pretty useful. Just to fit it more to my original use case, I
used this:

CREATE schema schema1;
CREATE schema schema2;
CREATE TABLE tbl (ID serial 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');

Thanks, I now consider this my best practice. This way, I don't have to
allocate ranges any more a priori :)

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