CODEWITHSUNDEEP
sqlpostgresqlcommon-table-expressionrow-number
Harsh Vaibhav
Harsh Vaibhav

Reputation: 23

Updating a table with row number in postgresql

Trying to update a table with a unique iD column as there is no unique key column in the same.

When using the below CTE, I am getting an error of relation does not exist for the CTE name in the update statement. but the same is working fine when select statement is executed.

Query used:

With updateid As
(
SELECT 
ID,
ROW_NUMBER() OVER (ORDER BY Model DESC) AS RN
FROM aud
)UPDATE updateid SET ID='AUD'||repeat('0',5-length(cast(RN as varchar)))||cast(RN as varchar)

Error encountered:

ERROR:  relation "updateid" does not exist
LINE 7: )UPDATE updateid SET ID='AUD'+replicate('0',5-len(cast(RN as...
                ^
SQL state: 42P01
Character: 95

The select statement that worked well:

With updateid As
(
SELECT 
ID,
ROW_NUMBER() OVER (ORDER BY Model DESC) AS RN
FROM aud
)Select * from updateid

Upvotes: 1

Views: 2410

Answers (2)

astentx
astentx

Reputation: 6751

If you still want to update the table from some source result set, you can use update ... from ... with join condition specified in where clause:

create table t
as
select q, null::int as w
from unnest(array[1,2,3]) as q

with s as (
  select row_number() over(order by q desc) as rn
    , ctid as row_id
  from t
)
update t
set w = rn
from s
where t.ctid = s.row_id

select *
from t

 q |  w
-: | -:
 3 |  1
 2 |  2
 1 |  3

db<>fiddle here

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270411

You can use a sequence for this:

create sequence temp_sequence_x;

update t
    set id = nextval('temp_sequence_x');

drop sequence temp_sequence_x;

I don't recommend making the primary key a string, as your code suggests that you want to do. But you can, of course, put that logic into the set clause if needed.

Here is a db<>fiddle.

Note: If there are a group of keys that are unique, then there are alternative methods. However, your question does not provide that information. And, the sequence approach is pretty simple.

Upvotes: 2

Related Questions