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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
