I have read that using OIDs as a primary key in a postgreSQL/PostGIS db is poor practice because there are instances where these can be reset. Sounds logical, but then what is a suitable alternative? I believe there is an option to use a "Universal Unique Identifer" UUID, but the large text and number value that spits out is horrible.

Just a bit more background to my situation. I have all of my spatial tables created with a field called "gid" which is the primary key for that table and unique only to that table. I have an issue now because I want to relate my spatial tables (all with a "gid" field starting at 1 and incrementing) to one large table with the related information. Obviously for my relationship to work all of my spatial features need a unique identifier which differentiates them from one another.

EDITED Added this image as per Peters' comment. Peter this is the idea I have in my head, it may not be the best way to go about it or it might not even be good db design. I am interested in what you think.

Conceptual diagram

Any tips?

  • 2
    "I have read" ... could you provide a link? Feb 23, 2011 at 4:19
  • 1
    Here is one of many postgresql.org/docs/8.4/static/ddl-system-columns.html towards the bottom of the page it mentions that it is bad practice to assume they are unique. Also this next link bytes.com/topic/postgresql/answers/423281-oid-not-oid a reply to the original post mentions that OIDs are deprecated for user tables.
    – Ando
    Feb 23, 2011 at 4:36
  • 1
    Could you add a few more concrete details of what kind of schema you are trying to create. It's not clear to me that you necessarily need a globally unique ID if you change the foreign key relationships a bit, for example. Feb 23, 2011 at 8:19
  • 1
    I believe there is an option to use a "Universal Unique Identifer" UUID, but the large text and number value that spits out is horrible. Why does it matter what the unique ID looks like?
    – nmtoken
    Nov 2, 2017 at 7:55
  • "...but the large text and number value that spits out is horrible." No, it's not. It's just long, as is required of any globally unique ID number.
    – jpmc26
    Jan 25, 2019 at 1:41

4 Answers 4


Two Solutions:

1)Create a single sequence and make all the tables use that sequence, can be done from the beginning or you may create a ID column and update your tables now.

To Create the sequence:

CREATE SEQUENCE universal_sequence;

Then a table:

colname integer NOT NULL DEFAULT nextval('universal_sequence'));

To Update an existing table id field with new IDs (do it for all the tables that you want to follow the same sequence):

UPDATE table1
SET id=nextval('universal_sequence'));

2)The other solution: Create a temporary sequence and them run the query creating a new ID column.

More here: http://www.postgresql.org/docs/8.4/static/sql-createsequence.html


I would create separate intermediary tables buildings_attach, parcels_attach, etc. Then you don't need a global identifier.

  • Hi Peter, Thanks for the response. I finally managed to get in touch with our DBA (she is based in another office), she suggested the same solution as you did. I am happy to go that route as I am definitely not a DB person (could be obvious from my schema drawing?!?), but is that really the best solution? What happens if there was an attachment that was relevant to both a Parcel feature and a building feature? In my above diagram I would only need to enter the details for the attachment once, where as the solution the DBA suggested I would need to do it twice in two different tables.
    – Ando
    Feb 23, 2011 at 23:55
  • 1
    Yes, but they are two separate pieces of information, so it's OK to enter them in two separate places. It's just the way a relational database design works out. Feb 24, 2011 at 11:49
  • Thanks for the help Peter, I appreciate the clarification! I will go down that route. Cheers
    – Ando
    Feb 24, 2011 at 20:44

The best option is the UUID or GUID. They are built for this reason, globally unique no matter what table. Ugly? Yes but they are the best for this situation.

See https://stackoverflow.com/questions/294933/generate-unique-id-to-share-with-multiple-tables-sql-2008

I have seen methods where people use data from the table to make IDs eg col1 + somestring + col2, I would really really adivse against this (see here). Intelligent IDs are a really bad idea.



Why don't you take the id from the big table and put in the spatial tables instead?

If one row in one of the spatial tables relates to multiple rows in the big table I see the problem, otherwise the big table id should be enough, or am I missing something.


  • Hi Nicklas, I can't do it that way because one of my spatial features may relate to 1 or more records in the larger table
    – Ando
    Feb 23, 2011 at 20:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.