Обсуждение: Re: [SQL] keeping OID's when copying table
---Bruce Momjian <maillist@candle.pha.pa.us> wrote: > > Thank you for pointing out my error. It should be: > > CREATE TABLE new_table (mycol int); > INSERT INTO new_table (oid, mycol) SELECT oid, mycol FROM old_table; > > I have updated the FAQ. Hmm... now I don't get an error, but the oid isn't preserved. This is running 6.3.2 on RH Linux: dmdemo=> CREATE TABLE new_table (mycol int); CREATE dmdemo=> CREATE TABLE old_table (mycol int); CREATE dmdemo=> insert into old_table values (33); INSERT 837643 1 dmdemo=> select oid from old_table; oid ------ 837643 (1 row) dmdemo=> INSERT INTO new_table (oid, mycol) SELECT oid, mycol FROM dmdemo-> old_table; INSERT 837644 1 dmdemo=> select oid from new_table; oid ------ 837644 (1 row) Further suggestions? _________________________________________________________ DO YOU YAHOO!? Get your free @yahoo.com address at http://mail.yahoo.com
> ---Bruce Momjian <maillist@candle.pha.pa.us> wrote: > > > > Thank you for pointing out my error. It should be: > > > > CREATE TABLE new_table (mycol int); > > INSERT INTO new_table (oid, mycol) SELECT oid, mycol FROM > old_table; > > > > I have updated the FAQ. > > Hmm... now I don't get an error, but the oid isn't preserved. This is > running 6.3.2 on RH Linux: > > dmdemo=> CREATE TABLE new_table (mycol int); > CREATE > dmdemo=> CREATE TABLE old_table (mycol int); > CREATE > dmdemo=> insert into old_table values (33); > INSERT 837643 1 > dmdemo=> select oid from old_table; > oid > ------ > 837643 > (1 row) > > dmdemo=> INSERT INTO new_table (oid, mycol) SELECT oid, mycol FROM > dmdemo-> old_table; > INSERT 837644 1 > dmdemo=> select oid from new_table; > oid > ------ > 837644 > (1 row) > > Further suggestions? I can confirm that here. Seems there is some change that was made that is now causing this to fail. I will try to fix it before 6.5. The only workaround I can suggest is COPY WITH OID's out and in, but it doesn't allow you to easily remove columns. Can someone suggest when it stopped working? -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
> ---Bruce Momjian <maillist@candle.pha.pa.us> wrote:
> >
> > Thank you for pointing out my error. It should be:
> >
> > CREATE TABLE new_table (mycol int);
> > INSERT INTO new_table (oid, mycol) SELECT oid, mycol FROM
> old_table;
> >
> > I have updated the FAQ.
>
> Hmm... now I don't get an error, but the oid isn't preserved. This is
> running 6.3.2 on RH Linux:
>
> dmdemo=> CREATE TABLE new_table (mycol int);
> CREATE
> dmdemo=> CREATE TABLE old_table (mycol int);
> CREATE
> dmdemo=> insert into old_table values (33);
> INSERT 837643 1
> dmdemo=> select oid from old_table;
> oid
> ------
> 837643
> (1 row)
>
> dmdemo=> INSERT INTO new_table (oid, mycol) SELECT oid, mycol FROM
> dmdemo-> old_table;
> INSERT 837644 1
> dmdemo=> select oid from new_table;
> oid
> ------
> 837644
> (1 row)
>
> Further suggestions?
OK, I have a fix for you.
CREATE TABLE new(old_oid oid, mycol int);
SELECT INTO new SELECT old_oid, mycol FROM old;
COPY new TO '/tmp/x';
DELETE FROM new;
COPY new WITH OIDS FROM '/tmp/x';
Does this work for your purposes? I have updated the FAQ.
Should we allow oid's to be transfered via INSERT? I think we should.
INSERT INTO new_table (oid, mycol) SELECT oid, mycol FROM old_table
--
Bruce Momjian | http://www.op.net/~candle
maillist@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
OK, I think Tom Lane fixed this by generating an error in 6.5 beta. > ---Bruce Momjian <maillist@candle.pha.pa.us> wrote: > > > > Thank you for pointing out my error. It should be: > > > > CREATE TABLE new_table (mycol int); > > INSERT INTO new_table (oid, mycol) SELECT oid, mycol FROM > old_table; > > > > I have updated the FAQ. > > Hmm... now I don't get an error, but the oid isn't preserved. This is > running 6.3.2 on RH Linux: > > dmdemo=> CREATE TABLE new_table (mycol int); > CREATE > dmdemo=> CREATE TABLE old_table (mycol int); > CREATE > dmdemo=> insert into old_table values (33); > INSERT 837643 1 > dmdemo=> select oid from old_table; > oid > ------ > 837643 > (1 row) > > dmdemo=> INSERT INTO new_table (oid, mycol) SELECT oid, mycol FROM > dmdemo-> old_table; > INSERT 837644 1 > dmdemo=> select oid from new_table; > oid > ------ > 837644 > (1 row) > > Further suggestions? > > _________________________________________________________ > DO YOU YAHOO!? > Get your free @yahoo.com address at http://mail.yahoo.com > > -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Bruce Momjian <maillist@candle.pha.pa.us> writes:
> OK, I think Tom Lane fixed this by generating an error in 6.5 beta.
>> dmdemo=> INSERT INTO new_table (oid, mycol) SELECT oid, mycol FROM
>> dmdemo-> old_table;
>> INSERT 837644 1
Hmm, what version did you do this with? It would fairly reliably crash
the backend in recent times, so a week or two ago I put in a temporary
check to generate an error instead. I would like to see INSERT or
UPDATE able to set the OID field; but we're too close to 6.5 release
to think about making that work for 6.5.
Right now the only way to preserve OIDs is to use COPY WITH OIDS.
regards, tom lane