0

I have a table in postgres that I need to update:

\d+ teachers;
                                                         Table "public.teachers"
   Column   |         Type          | Collation | Nullable |               Default                | Storage  | Stats target | Description
------------+-----------------------+-----------+----------+--------------------------------------+----------+--------------+-------------
 id         | bigint                |           | not null | nextval('teachers_id_seq'::regclass) | plain    |              |
 first_name | character varying(25) |           |          |                                      | extended |              |
 last_name  | character varying(50) |           |          |                                      | extended |              |
 school     | character varying(50) |           |          |                                      | extended |              |
 hire_date  | date                  |           |          |                                      | plain    |              |
 salary     | numeric               |           |          |                                      | main     |              |
Access method: heap

I have 2 update statements that I want to make in postgres, but in the future there will be a lot more:

UPDATE teachers SET id = 11 WHERE id = 23;
UPDATE teachers SET id = 12 WHERE id = 24;

How do I combine these update statements into one statement?

bluethundr
  • 1,005
  • 17
  • 68
  • 141
  • 1
    Check this https://stackoverflow.com/questions/18797608/update-multiple-rows-in-same-query-using-postgresql. But depending on case you might want to think about: set id = id - 12 in your case. Or you may want simply put all of those updates into transaction – dfens Apr 11 '21 at 15:34
  • 1
    Why would you change these id values? It looks like the id column is supposed to be your primary key - although according to your `\d+` output you did not declare it as such. Also, you have a serial default and this will most likely lead to gaps in your id column anyway. – clamp Apr 11 '21 at 15:44
  • I deleted some records and when I added new ones there were gaps in the the ID column. For example it would go 8,9,10,22,23,24 when I added new records. I wanted them to be consecutive. This is a test table, but I wanted to get an idea of how to update multiple records at once. – bluethundr Apr 11 '21 at 16:13
  • 1
    There are no gaps in your id's. An identifier is just a meaningless number. 473645843 or 378287648, just 2 random unique meaningless numbers that identify both a single record. And no, there is no gab between these 2 id's. You don't have a problem, so don't try to fix it. – Frank Heikens Apr 12 '21 at 08:47

0 Answers0