[postgis-users] To OID or not to OID?

[Joe Healy]

You can specify the columns you want to copy into. This means that the 
serial value will work.

e.g. copy table (col1, col2, col3) from stdin

In the docs:

http://www.postgresql.org/docs/current/static/sql-copy.html



Bruce Rindahl wrote:
> Ok now for the follow-up question :)
> I need to load this table from a massive flat file (444,000 + records).  The
> psql \copy command works great if I create the table with oid's but if I
> define a sequential gid then I can't use the \copy command.  I have to
> specify a value for the gid since it won't take a null, '*' or DEFAULT.
> However I have no way of knowing what the value should be.  I will also need
> to use \copy for appending new data.  Does anyone know a workaround for
> this??
>
> Thanks
> Bruce
>
> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Bruce
> Rindahl
> Sent: Thursday, December 14, 2006 2:01 PM
> To: 'PostGIS Users Discussion'
> Subject: RE: [postgis-users] To OID or not to OID?
>
> WFM.  I will go with the latter.
> Thanks!
> Bruce
>
> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Mike
> Leahy
> Sent: Thursday, December 14, 2006 1:46 PM
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] To OID or not to OID?
>
> Hello Bruce,
>
>  From my perspective as a frequent user of PostgreSQL/PostGIS (though 
> not an expert), I would recommend the latter of the two options you 
> mention.  I've found that not using OIDs makes the database/tables more 
> portable using pg_dump.  With OIDs, which are no longer default in 
> PostgreSQL, you'll always need to remember to include the option to dump 
> OIDs when you export/backup your data.  Using a field like gid 
> explicitly declared in the table makes things simpler down the road.  I 
> can't say for sure whether there are performance benefits one way or the 
> other, but I would guess that since the gid field gets indexed when it 
> is declared as the primary key, the performance would be essentially the 
> same.
>
> Mike
>
> Bruce Rindahl wrote:
>   
>> I am creating and populating a table with X and Y values as attributes.
>> After the table is created I will add a geometry column and populate it
>>     
> with
>   
>> points from the X and Y attributes.  My question is what is the better
>> approach?  Something like this?
>>
>> CREATE TABLE wells
>> (
>>   receipt varchar(8),
>>   utm_x float8,
>>   utm_y float8
>> ) 
>> WITH OIDS;
>> SELECT AddGeometryColumn('','wells','the_geom','26913','POINT',2);
>>
>> Or the method used by shp2pgsql?
>>
>> CREATE TABLE wells
>>  (
>>    gid serial PRIMARY KEY,
>>    receipt" varchar(8),
>>    utm_x" numeric,
>>    utm_y" numeric
>> );
>> SELECT AddGeometryColumn('','wells','the_geom','26913','POINT',2);
>>
>> In the first method the oid field becomes the primary key where in the
>> second method the gid field becomes the primary key.
>>
>> Is there a performance difference?
>>
>> Thanks
>> Bruce Rindahl
>>
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>>     
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>   


-- 
________________________________________________________
Joe Healy | Engineer

OMC-International | 6 Paterson St | Abbotsford, VIC 3067
Melbourne | Australia
Phone +61 (3) 9412 6500 | Fax +61 (3) 9415 9105
http://www.omc-international.com.au/

Smarter Ports | Safer Shipping.

CONFIDENTIAL COMMUNICATIONS. The information contained in this e-mail
is confidential and may be subject to legal professional privilege. It
is intended solely for the addressee. If you received this
correspondence by mistake, please promptly inform us by reply e-mail
or by telephoning +61 3 9412 6500 and then delete the e-mail and
destroy any printed copy. You must not disclose, copy or rely on any
part of this correspondence if you are not the intended recipient.