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

On Tue, Jan 24, 2012 at 5:23 AM, panam <panam(at)gmx(dot)net> wrote:
> 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 :)

Another quirky way to do it is with domains;

create sequence global_seq;
create domain gid bigint default nextval('global_seq');
create table foo (gid gid, f1 text);
create table bar (gid gid, f2 int);
etc.

This looks very appealing on the surface but domains have some quirks
that should give pause. In particular, you can't make arrays of them,
although you can make arrays of rowtypes that have a domain in them.

Barring domains, you can just manually apply the default instead of
using a serial type:

create table foo (gid bigint default nextval('global_seq'));

merlin