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

On Mon, Jan 23, 2012 at 11:19 AM, panam <panam@gmx.net> wrote:
> Hi,
>
> If I'd like to have primary keys generated ("numeric" style, no UUIDs) that
> are unique across schemas is the best option to allocate a fixed sequence
> range (min,max) to the sequences of all schemas?

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.)

Chris Angelico