DBLINK_B...QL_UPDATE(3)

✨ DBLINK_B...QL_UPDATE ✨

okay bestie, so this is a section 3 man page (dblink_build_sql_update) from the PostgreSQL 18.6 docs, and it's literally the tool you want when you're hand-building UPDATE statements from a local row. let's get into it 💅

---

🌸 NAME 🌸

dblink_build_sql_update — builds an UPDATE statement using a local tuple, replacing the primary key field values with alternative supplied values ✨

---

🎀 SYNOPSIS 🎀

dblink_build_sql_update(text relname,
                        int2vector primary_key_attnums,
                        integer num_primary_key_atts,
                        text[] src_pk_att_vals_array,
                        text[] tgt_pk_att_vals_array) returns text

look at her. five args, returns text. iconic. 👑

---

🦋 DESCRIPTION 🦋

so dblink_build_sql_update is your girl when you're doing selective replication of a local table over to a remote database. 🫶

what she does: she picks a row out of your local table based on the primary key, then builds an SQL UPDATE command that will duplicate that row — except the primary key values get swapped out for the values you passed in the last argument. (and if you want an exact copy of the row, just pass the same values for the last two arguments, easy peasy 🍓)

the UPDATE command always assigns all fields of the row — that's a full send, no half measures. and the main difference between this and dblink_build_sql_insert is that here it's assumed the target row already exists in the remote table. iykyk. 💜

---

💖 ARGUMENTS 💖

• relname — the name of a local relation, like foo or myschema.mytab. if the name is mixed-case or has special characters in it, wrap it in double quotes, like "FooBar" — because without quotes, the string gets folded to lower case. no cap, that's just how it is. 🙈
• primary_key_attnums — the attribute numbers (1-based) of the primary key fields, like 1 2. ✨
• num_primary_key_atts — the number of primary key fields. that's it, that's the whole job. 💅
• src_pk_att_vals_array — the values of the primary key fields used to look up the local tuple. each field is represented in text form. heads up though: an error gets thrown if there's no local row with these primary key values. 😭
• tgt_pk_att_vals_array — the values of the primary key fields that will be placed in the resulting UPDATE command. each field is represented in text form. 🌸

---

🌈 RETURN VALUE 🌈

returns the requested SQL statement as text. simple. clean. slay. ⭐

---

👑 NOTES 👑

okay this part matters, bestie, listen up:

as of PostgreSQL 9.0, the attribute numbers in primary_key_attnums are interpreted as logical column numbers — meaning they correspond to the column's position in SELECT * FROM relname. previous versions interpreted those numbers as physical column positions. 🧚

and yes, there IS a difference if any column(s) to the left of the indicated column have been dropped during the lifetime of the table. so if your table has been through some things, be careful. 💀

---

🎀 EXAMPLES 🎀

SELECT dblink_build_sql_update('foo', '1 2', 2, '{"1", "a"}', '{"1", "b"}');
                   dblink_build_sql_update
-------------------------------------------------------------
 UPDATE foo SET f1='1',f2='b',f3='1' WHERE f1='1' AND f2='b'
(1 row)

look at that output. she ate and left no crumbs. 🔥

---

and that's the whole page, bestie!! this one's from PostgreSQL 18.6, dated 2026, and it's the DBLINK_B...QL_UPDATE(3) page — go forth and replicate selectively, you absolute main character. 💖✨🦋💅🫶