Ana Roizen wrote:
>
> The motivation of this question is the following:
>
> It began by wanting to drop a column from a table. As i understood
> there is no DROP COLUMN option in Postrgres. So someone suggested me to
> make a SELECT INTO a new table , including the columns I didn't want to
> drop. But doing this would generate new oids for the tuples in my old
> table. So any table containing foreign references to tuples of my table
> would be lost.
> Am I correct?
>
Ana -
I tend to try to stay away from using the oid, for this very reason.
However, the copy comand (and pg_dump) have options for dealing with
preserving the OIDs. Take a look at the pg_dump man pages. I ran a quick
test dump of a small table, and it looks like it just tacks the oid on
as the first field, so you could SELECT INTO a new table, with something
like:
SELECT oid as old_oid, field1, field2, field6 into tmp_table from
oldtable
then COPY temp_table TO file
drop oldtable
create oldtable (new schema)
COPY oldtable WITH OIDS from file
Hmm, looks like you can generate rows wqith identical OIDs this way! I
just tried it, expecting an error, but it seems to let me. Hmm...
> What to use instead of postgres'oid to identify tuples?
> I rather not use primary keys as in standard relational model.
Why not? I use the 'serial' type, and set it as primary key. This is
just a shortcut for creating a sequence, using it as the default for an
int, and creating a unique index on it.
> I thought of creating my own column "identifier of tuple" in each
> table, forcing the values to be unique.
Ross
--
Ross J. Reedstrom, Ph.D., <[EMAIL PROTECTED]>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005
