UPDATE with INSERT RETURNING value PGSQL

Question

Trying to update certain column with newly created id. Example:

╔═══════════════════════════╗
║ TABLE_LIST_BINDER         ║
╠════╦══════════════════════╣
║ id ║ tbl_id_lists         ║
╠════╬══════════════════════╣
║ 1  ║ 1,2,3,4,5,6,7,8,9,10 ║
╚════╩══════════════════════╝

╔═════════════════════════════╗
║ TABLE_LIST_ITSELF           ║
╠════╦═════════════╦══════════╣
║ id ║ text_or_sm  ║ some_val ║
╠════╬═════════════╬══════════╣
║ 11 ║ ok lets see ║ ikd123   ║
╚════╩═════════════╩══════════╝

Alright so this is a rough example of what I have.

Text explanation: I'm trying to update tbl_list_binder's tbl_id_lists, by adding just freshly inserted row to TABLE_LIST_ITSLEF using postgresql. Is it doable in one query?

My current state of SQL code:

UPDATE TABLE_LIST_BINDER 
    SET tbl_id_lists=concat(tbl_id_lists,','(
        INSERT INTO TABLE_LIST_ITSELF (text_or_sm, some_val) 
        VALUES ('letz danse','mkei') 
        RETURNING id)) 
    WHERE id=1

My approach was: Using concat to combine, existing text of the value, then a comma and finally the returning ID value..

I think you're looking for trigger https://www.postgresql.org/docs/9.1/sql-createtrigger.html — executable, Dec 07 '18 at 09:03
oh? but triggers are like listeners, aren't they? I don't really need so it would update every time, I'm running it from PHP code, so its one time query. — Thomas J., Dec 07 '18 at 09:06
`TABLE_LIST_BINDER.tbl_id_lists` is a text column? not an array of ints? — Slava Lenskyy, Dec 07 '18 at 09:13
yeh its a text column, its an old table, dont know why someone thought it would be fun to make it text — Thomas J., Dec 07 '18 at 09:14

score 3 · Accepted Answer · answered Dec 07 '18 at 09:16

3

with _insert as 
(
INSERT INTO TABLE_LIST_ITSELF (text_or_sm, some_val) 
        VALUES ('letz danse','mkei') 
        RETURNING id
)
UPDATE TABLE_LIST_BINDER 
    SET tbl_id_lists=concat(tbl_id_lists,',' || (select id from _insert))
where id = 1;

answered Dec 07 '18 at 09:16

Slava Lenskyy

426
2
10

Thanks a lot, could you explain a bit how *with* works, I assume it makes a temporary holder so I could use it after it's inserted (My query was wrong since I was trying to update with not yet inserted id) right? – Thomas J. Dec 07 '18 at 09:30
1

@Comirdc you are right, you have to separate two operations. The link above is helpfull. Read 7.8.2. I suggest to read the whole page as this method have some restrictions. For example you can't update the same row twice in different statemens. – Slava Lenskyy Dec 07 '18 at 11:15

UPDATE with INSERT RETURNING value PGSQL

1 Answers1