pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
keeping OID's when copying table
6+ messages / 3 participants
[nested] [flat]

* keeping OID's when copying table
@ 1999-02-03 00:42  Michael Olivier <molivier@yahoo.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Michael Olivier @ 1999-02-03 00:42 UTC (permalink / raw)
  To: pgsql-sql

Hi folks,

Using 6.3.2 in Linux and trying to clean up some tables by getting rid
of dead columns, but need to preserve the OID's... The FAQ in "3.17)
What is an oid? What is a tid?" describes this process to copy a
table, keeping the OID's:

        CREATE TABLE new_table (mycol int);
        INSERT INTO new_table SELECT oid, mycol FROM old_table;

But I tried this and it failed:

  dmlists=> create table test (acctname text not null);
  CREATE
  dmlists=> create table test2 (acctname text not null);
  CREATE
  dmlists=> insert into test2 select oid, acctname from test;
  ERROR:  Type of oid does not match target column acctname

How can I do this?

thanks,
Michael

_________________________________________________________
DO YOU YAHOO!?
Get your free @yahoo.com address at http://mail.yahoo.com




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: [SQL] keeping OID's when copying table
@ 1999-02-03 03:55  Bruce Momjian <maillist@candle.pha.pa.us>
  parent: Michael Olivier <molivier@yahoo.com>
  0 siblings, 0 replies; 6+ messages in thread

From: Bruce Momjian @ 1999-02-03 03:55 UTC (permalink / raw)
  To: molivier@yahoo.com; +Cc: pgsql-sql

> Hi folks,
> 
> Using 6.3.2 in Linux and trying to clean up some tables by getting rid
> of dead columns, but need to preserve the OID's... The FAQ in "3.17)
> What is an oid? What is a tid?" describes this process to copy a
> table, keeping the OID's:
> 
>         CREATE TABLE new_table (mycol int);
>         INSERT INTO new_table SELECT oid, mycol FROM old_table;
> 
> But I tried this and it failed:
> 
>   dmlists=> create table test (acctname text not null);
>   CREATE
>   dmlists=> create table test2 (acctname text not null);
>   CREATE
>   dmlists=> insert into test2 select oid, acctname from test;
>   ERROR:  Type of oid does not match target column acctname
> 
> How can I do this?

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.

-- 
  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



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: [SQL] keeping OID's when copying table
@ 1999-02-04 21:34  Michael Olivier <molivier@yahoo.com>
  0 siblings, 2 replies; 6+ messages in thread

From: Michael Olivier @ 1999-02-04 21:34 UTC (permalink / raw)
  To: Bruce Momjian <maillist@candle.pha.pa.us>; +Cc: pgsql-sql

---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




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: [SQL] keeping OID's when copying table
@ 1999-02-04 21:51  Bruce Momjian <maillist@candle.pha.pa.us>
  parent: Michael Olivier <molivier@yahoo.com>
  1 sibling, 0 replies; 6+ messages in thread

From: Bruce Momjian @ 1999-02-04 21:51 UTC (permalink / raw)
  To: molivier@yahoo.com; +Cc: pgsql-sql

> ---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



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: [SQL] keeping OID's when copying table
@ 1999-05-09 12:02  Bruce Momjian <maillist@candle.pha.pa.us>
  parent: Michael Olivier <molivier@yahoo.com>
  1 sibling, 1 reply; 6+ messages in thread

From: Bruce Momjian @ 1999-05-09 12:02 UTC (permalink / raw)
  To: Michael Olivier <molivier@yahoo.com>; +Cc: pgsql-sql


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, Pennsylvania 19026



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: [SQL] keeping OID's when copying table
@ 1999-05-09 17:11  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Bruce Momjian <maillist@candle.pha.pa.us>
  0 siblings, 0 replies; 6+ messages in thread

From: Tom Lane @ 1999-05-09 17:11 UTC (permalink / raw)
  To: Bruce Momjian <maillist@candle.pha.pa.us>; +Cc: Michael Olivier <molivier@yahoo.com>; pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~1999-05-09 17:11 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-02-03 00:42 keeping OID's when copying table Michael Olivier <molivier@yahoo.com>
1999-02-03 03:55 ` Bruce Momjian <maillist@candle.pha.pa.us>
1999-02-04 21:34 Re: [SQL] keeping OID's when copying table Michael Olivier <molivier@yahoo.com>
1999-02-04 21:51 ` Bruce Momjian <maillist@candle.pha.pa.us>
1999-05-09 12:02 ` Bruce Momjian <maillist@candle.pha.pa.us>
1999-05-09 17:11   ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox