Re: Creating composite keys from csv

Eli Murray wrote
> Basically, what we'd like to do is create a serial primary key but instead
> of having it increment every row, it needs to check the name and total
> salary columns and only increment if that person doesn't already exist.

So you already have a PK, (Name, Total Salary), but the source data doesn't
provide a usable surrogate key to operate with.

I would create Person and Person-Position tables and after importing the CSV
data to a Staging area write a query to insert any unknown (Name, Total
Salary) records into Person with a serial PK field.

You can then join Person back onto Staging using (Name, Total Salary) but
now include the PK and select just the PK, Position, and Salary fields which
you can then add to the Person-Role table.

You now have a Person table with (PK, Name, Total Salary) and
Person-Position with (PK, Position, Role Salary) and you can discard the
imported CSV data.

This solves the explicit problem given the assumption that (Name, Total
Salary) is indeed a uniquely identifying constraint.

David J.

--
View this message in context: http://postgresql.nabble.com/Creating-composite-keys-from-csv-tp5841038p5841043.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.