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

[Bruce Rindahl]

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