[SOLVED] Generate column value automatically from other columns values and be used as PRIMARY KEY

Generate column value automatically from other columns values and be used as PRIMARY KEY

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.

Solution

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;