merging rows in postgres with matching fields

Question

I have a table format that appears in this type of format:

email               |   interest  |     major   | employed  |inserttime
[email protected]    |   soccer    |             | true      |  12:00
[email protected]    |             |     CS      | true      |  12:01

Essentially, this is a survey application and users sometimes hit the back button to add new fields. I later changed the INSERT logic to UPSERT so it just updated the row where email=currentUsersEmail , however for the data inserted prior to this code change there are many duplicate entries for single users. i have tried some group by's with no luck, as it continually says the

ID column must appear in the GROUP BY clause or be used in an aggregate function.

Certainly there will be edge cases where there may be clashing data, in this case maybe user entered true for the employed column and then in the second he/she could have enter false. For now I am not going to take this into account yet.

I simply want to merge or flatten these values into a single row, in this case it would look like:

email               |   interest  |     major   | employed  |inserttime
[email protected]    |   soccer    |     CS      | true      |  12:01

I am guessing I would take the most recent inserttime. I have been writing the web application in scala/play, but for this task i think probably using a language like python might be easier if i cannot do it directly through psql.

Lukasz Szozda · Accepted Answer · 2015-10-23 19:43:30Z

1

You can GROUP BY and flatten using MAX():

SELECT email, MAX(interest) AS interest,
       MAX(major) AS major,MAX(employed) AS employed,
       MAX(inserttime) AS inserttime
FROM your_table
GROUP BY email

answered Oct 23, 2015 at 19:43

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

kinghenry14 Over a year ago

wow thank you . i tried using MAX but wasn't sure how to use it exactly. will mark as correct in about 10 minutes when SO allows me to.

Lukasz Szozda Over a year ago

@kinghenry14 Great to hear I can help :)

Collectives™ on Stack Overflow

merging rows in postgres with matching fields

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related