This is a known problem with 7.3.X. It doesn't create an oid column as
part of SELECT INTO ....
We have a TODO item:
* Fix SELECT ... INTO and CREATE TABLE AS to have appopriate OID column
so this will be fixed, hopefully in 7.4. We couldn't fix it in 7.3.X
without causing more serious problems. If you specify the oid column as
part of the SELECT INTO, it will take the oid from the original table
and put it in your new table. Does that help you?
---------------------------------------------------------------------------
pgsql-bugs@postgresql.org wrote:
> jmm (j6m@cvni.net) reports a bug with a severity of 2
> The lower the number the more severe it is.
>
> Short Description
> [7.3] Attribute oid not found in a temporary table creted by a SELECT
>
> Long Description
> I ran on a problem today while running a SQL script that used
> to perfectly run from 7.0.2 to 7.2.3.
>
> (I dont know if it is exactly a bug or an intended design change as
> searchable online 7.3 docs are still not yet available. I browsed
> through the locally installed without success).
>
> In this SQL script, I create a temporary table with two columns from
> another table where I poured all data collected from various sources.
> My aim is to elmiminate doublons (id is a unique identifier in the
> finally resulting table). Therefore I take the max oid for each
> distinct id from the original table and do deletion for all rows
> with same id and oids lower than max oid. I do this on a temporary
> table processing it by chunks of 1000 ids for processing time reasons.
>
> It is quite simplistic way of doing it, but it used to work and does
> not any longer with 7.3. Temporary tables do have select table oids
> when explicitely created insted of using SELECT INTO.
>
> Sample Code
> toto=# create table toto (f1 integer, f2 integer) ;
> CREATE TABLE
> toto=# insert into toto values (1,2);
> INSERT 69204710 1
> toto=# insert into toto values (3,4);
> INSERT 69204711 1
> toto=# select * into temp table titi from toto ;
> SELECT
> toto=# select oid from titi ;
> ERROR: Attribute "oid" not found
>
> toto=# create temp table titi (f1 integer, f2 integer) ;
> ERROR: Relation 'titi' already exists
> toto=# create temp table tutu (f1 integer, f2 integer) ;
> CREATE TABLE
> toto=# insert into tutu select * from toto ;
> INSERT 0 2
> toto=# select oid from tutu ;
> oid
> ----------
> 69204716
> 69204717
> (2 rows)
>
>
> No file was uploaded with this report
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073