How to select rows after using row_number() in postgresql

Question

I have to select the null values one by one from a column with some null values in it. I have to select them one by one because I want to update all of them with different values.

I chose to do it with row_number() after running out of possible options in my mind, So here is the query that I executed

select cid, ROW_NUMBER () OVER (ORDER BY random()) as row from aa_dev.calls where cid is null;

How can I pick each row without storing it in any temp table and update each row? This column has 100 values 96 are populated with integers with only 4 nulls.

I need to populate row_number as follows. for example there are total 10 values in this data and 3 of them are null values.

cid	row
1	0
54	0
null	1
26	0
86	0
45	0
null	2
56	0
null	3
5	0

So, you just want to fill all NULL records with some random data? Please show us some sample data and the expected output. — S-Man
– S-Man, Commented Jan 11, 2021 at 10:18
You could take a look at the system-column ctid: SELECT ctid FROM aa_dev.calls WHERE cid IS NULL; It will point at the physical location for this record — Frank Heikens
– Frank Heikens, Commented Jan 11, 2021 at 10:43
@S-Man I have added a table in the question. That's my expected output — Chloe
– Chloe, Commented Jan 11, 2021 at 10:46
@FrankHeikens but that's going to select all null values, I want to select it one by one. — Chloe
– Chloe, Commented Jan 11, 2021 at 10:48
@S-Man Also yes I want to fill all null records with some random data, but I don't want to update all of the null records at a time. I want to do it iteratively. So that I have control over what I have to insert in each null record. — Chloe
– Chloe, Commented Jan 11, 2021 at 10:54

S-Man · Accepted Answer · 2021-01-11 10:56:33Z

1

Two possible ways came to mind:

demos:db<>fiddle

Using the row_number() over partitions which are (cid = NULL) and (cid != NULL). And this just execute for those records that are NULL, all others are set to 0:

SELECT 
    *,
    CASE WHEN cid IS NULL THEN
       row_number() OVER (PARTITION BY cid IS NULL)
    ELSE 0 END
FROM
    mytable

Second way is simply using a UNION construct over (cid = NULL) and (cid != NULL):

SELECT
    cid,
    row_number() OVER ()
FROM mytable
WHERE cid IS NULL

UNION

SELECT
   cid,
   0
FROM mytable
WHERE cid IS NOT NULL

answered Jan 11, 2021 at 10:56

S-Man

24k9 gold badges51 silver badges78 bronze badges

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

1 Comment

Chloe Over a year ago

Solved my problem.

Collectives™ on Stack Overflow

How to select rows after using row_number() in postgresql

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