1

I have a table with a column named "source" and "id". This table is populated from open data DB. "id" can't be UNIQUE, since my data came from other db with their own id system. There is a real risk to have same id but really different data.

I want to create another column which combine source and id into a single value. 

"openDataA" + 123456789 -> "openDataA123456789"
"openDataB" + 123456789 -> "openDataB123456789"

I have seen example that use || and function to concatenate value. This is good, but I want to make this third column my PRIMARY KEY, to avoid duplicate, and create a really unique id that I can query without much computation and that I can use as a foreign key constraint for other table.

I think Composite Types is what I'm looking for, but instead of setting the value manually each time, I want to grab them automatically by setting only "source" and "id"

I'm fairly new to postgresql, so any help is welcome. Thank you.

Mureinik
  • 297,002
  • 52
  • 306
  • 350
Lionel
  • 90
  • 1
  • 9

1 Answers1

1

You could just have a composite key in your table:

CREATE TABLE mytable (
    source VARCHAR(10),
    id VARCHAR(10),
    PRIMARY KEY (source, id)
);

If you really want a joined column, you could create a view to display it:

CREATE VIEW myview AS 
SELECT *, source || id AS primary_key
FROM   mytable;
Mureinik
  • 297,002
  • 52
  • 306
  • 350
  • thank you very much ! How can I query a composite key ? I don't need to give it a name ? Also, I will look into VIEW in the documentation, I wasn't aware of this and it seem interesting. Thank you again ! – Lionel Oct 31 '15 at 00:12
  • @Lionel you could either query the view as you would query a table (e.g., `select * from myview where primary_key = 'openDataB123456789'`, or query the table's fields separately: `select * from my_table where source = 'openDataA' and id ='123456789'` – Mureinik Oct 31 '15 at 07:34
  • Thank you again, I'm going to bother you one last time, if that's OK for you. Which solution would be faster ? – Lionel Nov 02 '15 at 02:47
  • @Lionel querying the two fields separately, definitely. It saves you from redundantly concating strings. – Mureinik Nov 02 '15 at 07:28