Combine rows in the table. Postgresql

Question

I have a table:

CREATE TABLE sale_tender_t0
(
  sten_bill bigint NOT NULL,
  sten_paymentform smallint NOT NULL,
  sten_tenderamount integer,
  sten_changeamount integer,
  sten_isfiscal character(1),
  sten_accid integer,
  sten_id bigserial NOT NULL,
  sten_map_user integer,
  sten_storeid integer NOT NULL

and some data, like:

11246276310734;0;5000;2748;"0";;1913;;1
11246277847093;0;25000;2083;"0";;1914;;1
11246280935156;0;2254;0;"0";;1915;;1
11246281048531;0;2240;100;"0";;1916;;1
11246281345250;0;10000;1360;"0";;1917;;1

sten_bill - should have unique data, but sometimes happens the data sten_bill is duplicated. using this script i can find duplicated data:

select sten_bill from sale_tender_t0 GROUP by sten_bill
HAVING count(*)>1;

But i need to fix the table, for example:

11246276310734;0;5000;2748;"0";;1913;;1
11246276310734;1;5000;2748;"0";;1913;;1

should fix like:

11246276310734;1;10000;5496;"0";;1913;;1

Delete first column, and change second column data column2.sten_tenderamount + column1.sten_tenderamount and column2.sten_changeamount + column2.sten_changeamount

You need some kind of a unique identifier A.K.A. primary key in order to distinguish somehow the rows. — Kouber Saparev
– Kouber Saparev, Commented Oct 17, 2014 at 12:27
Unrelated, but: it's cleaner to use a real boolean for a flag like sten_isfiscal — user330315
– user330315, Commented Oct 17, 2014 at 12:38

Kouber Saparev · Accepted Answer · 2014-10-17 13:40:12Z

2

WITH max_id_and_summed_amounts AS (
  SELECT
    sten_bill,
    SUM(sten_tenderamount) AS sten_tenderamount,
    SUM(sten_changeamount) AS sten_changeamount,
    MAX(sten_id) AS sten_id
  FROM
    sale_tender_t0
  GROUP BY
    sten_bill
  HAVING
    COUNT(*)>1
),
newest_line AS (
  SELECT
    miasa.sten_bill,
    stt.sten_paymentform,
    miasa.sten_tenderamount,
    miasa.sten_changeamount,
    stt.sten_isfiscal,
    stt.sten_accid,
    miasa.sten_id,
    stt.sten_map_user,
    stt.sten_storeid
  FROM
    max_id_and_summed_amounts miasa INNER JOIN sale_tender_t0 stt
  ON
    miasa.sten_id=stt.sten_id
),
deleted_rows AS (
  DELETE FROM
    sale_tender_t0
  WHERE
    sten_bill IN (
      SELECT
        sten_bill
      FROM
        max_id_and_summed_amounts
    )
)
INSERT INTO
  sale_tender_t0
SELECT
  *
FROM
  newest_line;

Edit: PostgreSQL versions 8.4 and older do not support Common Table Expressions, so you need to use temporary tables instead.

CREATE TEMPORARY TABLE max_id_and_summed_amounts
  AS
SELECT
  sten_bill,
  SUM(sten_tenderamount) AS sten_tenderamount,
  SUM(sten_changeamount) AS sten_changeamount,
  MAX(sten_id) AS sten_id
FROM
  sale_tender_t0
GROUP BY
  sten_bill
HAVING
  COUNT(*)>1;

CREATE TEMPORARY TABLE newest_line
  AS
SELECT
  miasa.sten_bill,
  stt.sten_paymentform,
  miasa.sten_tenderamount,
  miasa.sten_changeamount,
  stt.sten_isfiscal,
  stt.sten_accid,
  miasa.sten_id,
  stt.sten_map_user,
  stt.sten_storeid
FROM
  max_id_and_summed_amounts miasa INNER JOIN sale_tender_t0 stt
ON
  miasa.sten_id=stt.sten_id;

DELETE FROM
  sale_tender_t0
WHERE
  sten_bill IN (
    SELECT
      sten_bill
    FROM
      max_id_and_summed_amounts
  );

INSERT INTO
  sale_tender_t0
SELECT
  *
FROM
  newest_line;

DROP TABLE max_id_and_summed_amounts;
DROP TABLE newest_line;

edited Oct 17, 2014 at 13:40

answered Oct 17, 2014 at 12:34

Kouber Saparev

8,2552 gold badges32 silver badges28 bronze badges

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

12 Comments

user330315 Over a year ago

You could add a returning * to the final select in order to see the rows that were aggregated.

Sanyo4ever Over a year ago

ERROR: syntax error at or near "DELETE" LINE 20: DELETE FROM

Kouber Saparev Over a year ago

Just discovered the same issue, thanks horse, I edited the answer accordingly. Otherwise it was complaining for a missing FROM-clause entry for table "duplicate_rows"

Kouber Saparev Over a year ago

Perhaps you are using a version of PostgreSQL that does not support Common Table Expressions, such as 8.4 or older?

Sanyo4ever Over a year ago

Is it possible to alter on Postgres 8.4 ?

|

Collectives™ on Stack Overflow

Combine rows in the table. Postgresql

1 Answer 1

12 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

12 Comments

Your Answer

Sign up or log in

Post as a guest

Related