Updating a table with row number in postgresql

Question

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

The string concatenation operator in SQL is || - the + is for adding numbers. There also is no replicate() function in Postgres. And you can't update the result of a CTE in Postgres — user330315
– user330315, Commented May 23, 2021 at 21:10

Gordon Linoff · Accepted Answer · 2021-05-23 20:51:22Z

2

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.

answered May 23, 2021 at 20:51

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

Harsh Vaibhav Over a year ago

Thank you so much....... for showing a new approach for the query.

astentx · Accepted Answer · 2021-05-23 21:14:11Z

1

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

edited May 23, 2021 at 21:14

answered May 23, 2021 at 21:08

astentx

6,7812 gold badges19 silver badges26 bronze badges

1 Comment

Harsh Vaibhav Over a year ago

Thank you, the sample query worked well for me..... the update ran as expected. :)

Collectives™ on Stack Overflow

Updating a table with row number in postgresql

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related