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

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

CodePudding user response：

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.