0

I have table in Postgres

create table foo(bar int);

I know I can insert sequential data like this

insert into public.foo (select i from generate_series(1, 10) as i);

Now I want to update rows

update public.foo set bar = sq.t from (select t from generate_series(100, 10000) as t) as sq;

but this will update column to all the same values. I know I need to use where somehow, but how can I use it without primary keys from both sides?

EDIT: I will add more real life detail. I have complex table with around 20 columns. Around 40k rows. I am interested in two columns here, pk (or id, integer, with id_seq) and created_date. I populated this table with duplicating initial 10 rows, so created_date are repeating (like 123123123). I want to pick big range of dates from generate_series with 1 min interval and put them in created_date column to have sequential data there. And ideally regenerate ids from 1. How can I do it?

IgorNikolaev
  • 1,053
  • 1
  • 12
  • 28
  • Does this answer your question? [Postgresql update each row with a random value](https://stackoverflow.com/questions/36463205/postgresql-update-each-row-with-a-random-value) – Thirumal Jul 28 '20 at 02:11
  • @Thirumal no, it does not – IgorNikolaev Jul 28 '20 at 02:13
  • Does this answer your question? [Using window functions in an update statement](https://stackoverflow.com/questions/4358613/using-window-functions-in-an-update-statement) or [Postgres UPDATE using rank window function](https://stackoverflow.com/q/40296265/1048572) – Bergi Jul 28 '20 at 02:52
  • @Bergi, no, it does not – IgorNikolaev Jul 28 '20 at 09:59
  • 1
    @IgorNikolaev Why not? It's exactly what you want to do. (Or do you really want to `generate_series()` again, and only update the first N rows or so?) – Bergi Jul 28 '20 at 11:23
  • @Bergi because these solutions require subquery column to have pk for where clause and I don't have pk in` generate_series` subquery – IgorNikolaev Jul 28 '20 at 11:26
  • @IgorNikolaev You do the subquery on the same table, and use its primary key ([or `ctid`](https://stackoverflow.com/a/40296406/1048572)). Don't use `generate_series()`, use a window function such as `row_number()`. Or why do you insist on using `generate_series()`? – Bergi Jul 28 '20 at 12:10
  • @Bergi how is subquery on the same table? I need new sequence of values with arbitrary start and end – IgorNikolaev Jul 28 '20 at 12:55
  • @IgorNikolaev And *which* values in the table do you want to update with the range between a start and an end value? What if there is a different number of rows than your range has entries? – Bergi Jul 28 '20 at 13:16
  • @Bergi values of first and only column, if less, than put NULLs, if more, then cut redundant – IgorNikolaev Jul 28 '20 at 13:20
  • @Bergi I really can't understand why it's so complicated, I have column with n values, I want Postgres to iterate over rows and clean values of this column and put there new values from previously evaluated `generate_series`. Is there something I don't get about relational databases? – IgorNikolaev Jul 28 '20 at 13:22
  • @IgorNikolaev One important thing about relational databases is that rows don't have an order and not really an identity. If your table really has only a single column, you can achieve an equivalent result by just deleting all rows and then inserting your range (and, if necessary, the respective number of `NULL`s) – Bergi Jul 28 '20 at 13:27
  • @Bergi I've edited the question with more real life details – IgorNikolaev Jul 28 '20 at 13:33
  • @IgorNikolaev Do you care about the values in the other 18 columns, i.e. should the re-generated ids and dates have the same order as the original ones? – Bergi Jul 28 '20 at 21:43

1 Answers1

0

You're looking for

UPDATE foo
SET bar = CASE WHEN new_value < N THEN new_value + start END
FROM (
  SELECT
    ctid,
    row_number() OVER () as new_value
  FROM foo
) AS new_foo
WHERE foo.ctid = new_foo.ctid;
TABLE foo;

(online demo)

Bergi
  • 630,263
  • 148
  • 957
  • 1,375