# How to merge rows based on how much data they have?

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.

-
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