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

Mike Leahy mgleahy at alumni.uwaterloo.ca
Thu Dec 14 12:45:33 PST 2006

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 


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?
> (
>   receipt varchar(8),
>   utm_x float8,
>   utm_y float8
> ) 
> SELECT AddGeometryColumn('','wells','the_geom','26913','POINT',2);
> Or the method used by shp2pgsql?
>  (
>    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

More information about the postgis-users mailing list