CODEWITHSUNDEEP
postgresql
NL3
NL3

Reputation: 127

PostgreSQL - Update rows in table with generate_series()

I have the following table:

create table test(
    id              serial primary key,
    firstname       varchar(32),
    lastname        varchar(64),
    id_desc         char(8)
);

I need to insert 100 rows of data. Getting the names is no problem - I have two tables one containing ten rows of first names and the other containing ten last names. By doing a insert - select query with a cross join I am able to get 100 rows of data (10x10 cross join).

id_desc contains of eight characters (fixed size is mandatory). It always starts with the same pattern (e.g. abcde) followed by 001, 002 etc. up to 999. I have tried to achieve this with the following statement:

update test set id_desc = 'abcde' || num.id
    from (select * from generate_series(1, 100) as id) as num
        where num.id = (select id from test where id = num.id);

The statement executes but affects zero rows. I know that the where-clause probably does not make much sense; I have been trying to finally get this to work and just started trying a couple of things. Didn't want to omit it though when posting here because I know it is definitely required.

Upvotes: 4

Views: 2966

Answers (2)

Chris Travers
Chris Travers

Reputation: 26464

Laurenz's suggestion fits this specific case very well. I recommend using it.

The rest of this is for the more general case where that simplification is not appropriate.

In my tests this doesn't work in this way.

I think you are better off using a WITH clause and a window function.

WITH ranked_ids (id, rank) AS (
      select id, row_number() OVER (rows unbounded preceding)
        FROM test
)
update test set id_desc = 'abcde' || ranked_ids.rank
from ranked_ids WHERE test.id = ranked_ids.id;

Upvotes: 3

Laurenz Albe
Laurenz Albe

Reputation: 247545

It should be as simple as

UPDATE test SET id_desc = 'abcde' || to_char(id, 'FM099');

Upvotes: 1

Related Questions