Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am cleaning data and I formed a table something like this:

    file   |  product  |  color  | product_id
---------------------------------------------
  one.csv      pipe        blue       null
  one.csv      null        null       null
  one.csv      null        red        786
  one.csv      pipe        red        null
  two.csv      plate       null       null
  two.csv      pipe        null       null
  two.csv      plate       null       3333

In this table, null is a logical null, not a string 'null'. From this table, I need to merge rows so that one row contain data from the other merged rows.

Here is some kind of algorithm:

  • Let's call the defined attribute for a single row, an attribute that has a value (is not null)

  • Let's call the common attributes for two rows those attributes that have the same value for each row, keeping in mind that null=null is null (they are not equal).

So, for the first two rows from the table, the common attribute is 'file'. For the first and the fourth row are 'file' and 'product'

  • Two rows will be merged if one row have only common attributes, and the other one have more defined attributes (not null).

Result for merging the first and the second row is
('one.csv','pipe','blue',null).
Result for merging the fifth and the last row is
('two.csv','plate',null,'3333').
The third and the fourth row can not be merged, because they have other defined attributes beside the common attributes.

Final result is table:

    file   |  product  |  color  | product_id
---------------------------------------------
  one.csv      pipe        blue       null
  one.csv      null        red        786
  one.csv      pipe        red        null
  two.csv      pipe        null       null
  two.csv      plate       null       3333

Is there a query, a function or something else in PostgreSQL that I can use for my problem?

I know that there is a operator in database theory for that kind of operation, but I don't know how to search it because I am not native English speaker.

share|improve this question
    
Are you evaluating columns left-to-right or is the order of operations dictated by the total number of matching columns? –  Erwin Brandstetter Dec 6 '13 at 7:33

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.