CODEWITHSUNDEEP
postgresql
user558122
user558122

Reputation: 861

PostgreSQL Record Reordering Issue

I need to perform record re-ordering using the timestamp, but I get a NOT-NULL constraint violation when the query runs with no matches.

Below is the query - 

UPDATE ORDR
SET    QUE_NUM = ORDR2.SORT_ORDER
FROM
   (
    SELECT ORDR_ID, ROW_NUMBER() OVER (ORDER BY CRTD_TS) AS SORT_ORDER
    FROM   ORDR
    WHERE  STUS_CD IN ('01','02','03','04','05','06')
   ) ORDR2
WHERE ORDR2.ORDR_ID = ORDR.ORDR_ID

I get the exception below - ERROR: null value in column "que_num" violates not-null constraint

Upvotes: 0

Views: 162

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658707

The query works as it is. I just tested. Consider this demo:

CREATE TEMP TABLE ordr (
   ordr_id int
  ,que_num int NOT NULL
  ,stus_cd text
  ,crtd_ts int
  );
INSERT INTO ORDR VALUES
 (1, 1, '01', 6)
,(2, 2, '02', 5)
,(3, 3, '03', 4)
,(4, 4, '04', 3)
,(5, 5, '05', 1)
,(6, 6, '06', 2);

UPDATE ordr
SET    que_num = ordr2.sort_order
FROM  (
    SELECT ordr_id, row_number() OVER (ORDER BY crtd_ts) AS sort_order
    FROM   ordr
    WHERE  stus_cd IN ('07','08') -- no match
   ) ordr2
WHERE ordr2.ordr_id = ordr.ordr_id

Returns:

Query returned successfully: 0 rows affected, 31 ms execution time.

Upvotes: 1

Related Questions