PostgreSql : Merge two rows and add the difference to new column

Question

We have an app which displays a table like this :

this is what it looks like in database :

┌──────────┬──────────────┬─────────────┬────────────┬──────────┬──────────────────┐
│ BatchId  │ ProductCode  │ StageValue  │ StageUnit  │ StageId  │ StageLineNumber  │
├──────────┼──────────────┼─────────────┼────────────┼──────────┼──────────────────┤
│ 0B001    │ 150701       │ LEDI2B4015  │            │ 37222    │ 1                │
│ 0B001    │ 150701       │ 16.21       │ KG         │ 37222    │ 1                │
│ 0B001    │ 150701       │ 73.5        │            │ 37222    │ 2                │
│ 0B001    │ 150701       │ LEDI2B6002  │ KG         │ 37222    │ 2                │
└──────────┴──────────────┴─────────────┴────────────┴──────────┴──────────────────┘

I would like to query the database so that the output looks like this :

┌──────────┬──────────────┬────────────────────┬─────────────┬────────────┬──────────┬──────────────────┐
│ BatchId  │ ProductCode  │ LoadedProductCode  │ StageValue  │ StageUnit  │ StageId  │ StageLineNumber  │
├──────────┼──────────────┼────────────────────┼─────────────┼────────────┼──────────┼──────────────────┤
│ 0B001    │ 150701       │ LEDI2B4015         │ 16.21       │ KG         │ 37222    │ 1                │
│ 0B001    │ 150701       │ LEDI2B6002         │ 73.5        │ KG         │ 37222    │ 2                │
└──────────┴──────────────┴────────────────────┴─────────────┴────────────┴──────────┴──────────────────┘

Is that even possible ?

My PostgreSQL Server version is 14.X

I have looked for many threads with "merge two columns and add new one" but none of them seem to be what I want.

DB Fiddle link SQL Fiddle (in case) link

Please make your question reproducible with table definitions and DML commands. It is painful for others to recreate environment again. — Umut TEKİN
– Umut TEKİN, Commented Jul 21, 2022 at 11:49

Jeremy · Accepted Answer · 2022-07-21 12:42:05Z

2

It's possible to get your output, but it's going to be prone to errors. You should seriously rethink your data model, if at all possible. Storing floats as text and trying to parse them is going to lead to many problems.

That said, here's a query that will work, at least for your sample data:

SELECT batchid,
       productcode,
       max(stagevalue) FILTER (WHERE stagevalue ~ '^[a-zA-Z].*') as loadedproductcode,
       max(stagevalue::float) FILTER (WHERE stagevalue !~ '^[a-zA-Z].*') as stagevalue,
       max(stageunit),
       stageid,
       stagelinenumber
 FROM datas
 GROUP BY batchid, productcode, stageid, stagelinenumber;

Note that max is just used because you need an aggregate function to combine with the filter. You could replace it with min and get the same result, at least for these data.

answered Jul 21, 2022 at 12:42

Jeremy

6,77322 silver badges21 bronze badges

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

1 Comment

keytrap Over a year ago

The database wasn't created by me but by a company and we have a tool that sits on it. I wouldn't have done it this way either, but I have to deal with it. This has solved my problem thanks !

Collectives™ on Stack Overflow

PostgreSql : Merge two rows and add the difference to new column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related