CODEWITHSUNDEEP
postgresql
Chloe
Chloe

Reputation: 127

How to select rows after using row_number() in postgresql

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;

See result of above query

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

Upvotes: 0

Views: 528

Answers (1)

S-Man
S-Man

Reputation: 23746

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

Upvotes: 1

Related Questions