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

On Tue, Jan 24, 2012 at 10:23 PM, 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 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
>
> Thanks, I now consider this my best practice. This way, I don't have to
> allocate ranges any more a priori :)

I would recommend using an explicit sequence object rather than
relying on odd behavior like this; for instance, if you now drop
public.tbl, the sequence will be dropped too. However, what you have
there is going to be pretty close to the same result anyway.

As someone who's moved from DB2 to MySQL (hey, it's all open source!)
to Postgres (hey, it's all the features of DB2 and it's _still_ open
source!), I've been pretty pleased with Postgres sequences. Instead of
special-casing the primary key (as MySQL does with auto_increment),
Postgres allows you to have any sequences you like, going any
direction you like, and have multiple in the same table if you so
desire. Incidentally - I've yet to need it, but I think it's possible
to reset a sequence object to start producing lower numbers again,
while your table still has some higher numbers in it (of course being
careful not to get pkey collisions).

ChrisA