Re: Creating composite keys from csv

On 3/8/2015 11:49 PM, Eli Murray wrote:
> Hi all,
>
> I'm a student journalist working on a project for our student paper
> which lists salaries and positions for every staff member at the
> university. We received the data from an FOI request but the
> university is refusing to give us primary keys for the data.
>
> The issue we've run into is that if there are two staff members with
> the same name (and there are) our current web app adds their salaries
> together and considers them one person. Now, luckily, we can create a
> composite key if we combine their name column with their salary
> column. Unfortunately, the format of the data we have makes it more
> difficult than that (of course!) because some employees can hold
> multiple paying positions.

Take a look at the windowing functions:

http://www.postgresql.org/docs/9.4/static/functions-window.html

Roxanne
>
> Here's some example data:
>
> Name, Position, Salary,Total Salary, ...
> Jane Doe, Dean, 100.000, 148.000, ...
> John Locke, Custodian, 30.000, 30.000, ...
> Jane Doe, Academic Adviser, 48.000, 148.000, ...
> Jane Doe, Trainer, 46.000, 46.000, ...
>
> 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. If they do exist, it should just assign the previously
> created number to the column. However, our team is small and between
> us we have very little experience working with databases and we
> haven't found a way to accomplish this goal yet. In fact, we may be
> trying to solve this in the wrong way entirely.
>
> So, to put it succinctly, how would you approach this problem? What
> are our options? Do we need to write a script to clean the data into
> separate csv tables before we import it to postgres, or is this
> something we can do in postgres? We'd really appreciate any help you
> all may be able to offer.
>
> Best!
> Eli Murray
>
>

--
[At other schools] I think the most common fault in general is to teach students how to pass exams instead of teaching them the science.
Donald Knuth