12

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?

| improve this question | |
  • 2
    "I have read" ... could you provide a link? – Kirk Kuykendall Feb 23 '11 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 '11 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. – Peter Eisentraut Feb 23 '11 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 '17 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 '19 at 1:41
5

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

| improve this answer | |
  • 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 '11 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. – Peter Eisentraut Feb 24 '11 at 11:49
  • Thanks for the help Peter, I appreciate the clarification! I will go down that route. Cheers – Ando Feb 24 '11 at 20:44
9

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:

CREATE 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

| improve this answer | |
4

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.

| improve this answer | |
0

Hallo

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.

/Nicklas

| improve this answer | |
  • 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 '11 at 20:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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