oids vs. serial question
Hi, I'm new to PostreSQL, and I fear this might be a FAQ.
I am considering using OIDs for my application. I like the fact that
they are just automagic. However, the magic seems to disappear when it
comes to dumping and restoring data. Is there a way to get references to
oids across tables get restored correctly? I see that pg_dump has --oids
and pg_restore has --oid-order. But when I do a restore using
--oid-order, my oid references are all off by a constant number. For
example, if I have a oid column that is a references between these two
tables:
CREATE TABLE park (
region_oid oid references region,
primary key (oid),
...
);
CREATE TABLE region (
PRIMARY KEY (oid),
...
) ;
Then I do
pg_dump -Fc --oids > my.db
[drop tables]
pg_restore -Fc --oid-order my.db
Then the region_oid column values do not match the current oids in
region.oid. It can be fixed with a query, but someone (me) is sure to
forget to do this cleanup.
update park set region_oid = region_oid + DELTA
where DELTA is the difference of min(region.oid) and min(park.region_oid)
Am I missing something with the use of dump and restore, or should I
just forget it and use SERIAL columns?
Alex Rice, Software Developer
Architectural Research Consultants, Inc.
alex_rice@arc.to
alrice@swcp.com
On Fri, Mar 01, 2002 at 05:10:47PM -0700, Alex Rice wrote:
...
Am I missing something with the use of dump and restore, or should I
just forget it and use SERIAL columns?
Personnally, serial sounds cleaner to me, so if it's an option, I'd go for it!
Patrick
Alex Rice schrieb:
I am considering using OIDs for my application. I like the fact that
they are just automagic. However, the magic seems to disappear when it
Hi,
as far as I understood the thing, take OIDs as 'internal' (they are
unique all over all databases) and do not use them to organize your
data.
CREATE TABLE park (
region_oid oid references region,
primary key (oid),
...
);CREATE TABLE region (
PRIMARY KEY (oid),
...
) ;
Something like
CREATE TABLE park (
id serial primary key,
region int4 references region,
...
);
CREATE TABLE region (
id serial primary key,
...
) ;
should work.
Is SERIAL still INT4 in v7.2?
Bye,
Knut S�bert
as far as I understood the thing, take OIDs as 'internal' (they are
unique all over all databases) and do not use them to organize your
data.
I think that sums it up nicely.
Is SERIAL still INT4 in v7.2?
Yes, but there's now a BIGSERIAL (or SERIAL8 if you prefer).
Greg
as far as I understood the thing, take OIDs as 'internal' (they are
unique all over all databases) and do not use them to organize your
data.I think that sums it up nicely.
Is SERIAL still INT4 in v7.2?
Yes, but there's now a BIGSERIAL (or SERIAL8 if you prefer).
OH COOL!!!! Very useful! Is that in the ever famous, "7.2 docs" that
are still pending release? ::hint hint:: -sc
--
Sean Chittenden
Sean Chittenden <sean@chittenden.org> writes:
OH COOL!!!! Very useful! Is that in the ever famous, "7.2 docs" that
are still pending release? ::hint hint:: -sc
What "pending" release?
http://www.ca.postgresql.org/users-lounge/docs/#7.2
http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/index.html
regards, tom lane
OH COOL!!!! Very useful! Is that in the ever famous, "7.2 docs" that
are still pending release? ::hint hint:: -scWhat "pending" release?
http://www.ca.postgresql.org/users-lounge/docs/#7.2
http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/index.html
Sorry, I meant the interactive documentation:
http://www.postgresql.org/idocs/index.php
-sc
--
Sean Chittenden
On Mon, Mar 04, 2002 at 09:20:43PM -0800, Sean Chittenden wrote:
as far as I understood the thing, take OIDs as 'internal' (they are
unique all over all databases) and do not use them to organize your
data.I think that sums it up nicely.
Is SERIAL still INT4 in v7.2?
Yes, but there's now a BIGSERIAL (or SERIAL8 if you prefer).
OH COOL!!!! Very useful! Is that in the ever famous, "7.2 docs" that
are still pending release? ::hint hint:: -sc
http://www.postgresql.org/users-lounge/docs/7.2/postgres/datatype.html#DATATYPE-SERIAL
- Andrew