DBLINK_B...QL_INSERT(3)

🎀 NAME 🎀

okay so we're doing dblink_build_sql_insert today bestie, and this is page DBLINK_B...QL_INSERT(3) straight out of the PostgreSQL 18.6 Documentation 💖

what it does, no cap: it builds an INSERT statement using a local tuple, replacing the primary key field values with alternative supplied values. that's the whole vibe. she's a builder. she's an icon. 💅

---

🌸 SYNOPSIS 🌸

dblink_build_sql_insert(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

like... five arguments and a text return. she said "I don't do minimalism" and honestly? respect. 🫶

---

💅 DESCRIPTION 💅

okay so dblink_build_sql_insert is lowkey perfect when you're doing selective replication of a local table over to a remote database ✨

here's the tea: it selects a row from the local table based on primary key, and then it builds an SQL INSERT command that will duplicate that row — except the primary key values get replaced by the values in the last argument. that's the switcheroo. that's the plot twist. 🦋

and if you actually want an exact copy of the row? just specify the same values for the last two arguments. same energy in, same energy out. 🎀

---

🦋 ARGUMENTS 🦋

• relname — name of a local relation, for example foo or myschema.mytab. include double quotes if the name is mixed-case or contains special characters, for example "FooBar" — because without quotes, the string will be folded to lower case and that is not the moment you want to be having. 🙈
• primary_key_attnums — attribute numbers (1-based) of the primary key fields, for example 1 2. yes bb, we count from 1 here, that's just how she rolls.
• num_primary_key_atts — the number of primary key fields. that's it. that's the whole job. 💖
• src_pk_att_vals_array — values of the primary key fields to be used to look up the local tuple. each field is represented in text form. and bestie, listen: an error is thrown if there is no local row with these primary key values 😭 so make sure that row actually exists, fr fr.
• tgt_pk_att_vals_array — values of the primary key fields to be placed in the resulting INSERT command. each field is represented in text form. this is the glow-up version of your primary key ✨

---

⭐ RETURN VALUE ⭐

returns the requested SQL statement as text. that's it. she gives you the statement, you go do something cute with it. 💅

---

👑 NOTES 👑

okay this part is important, so vibe check time 👑

as of PostgreSQL 9.0, the attribute numbers in primary_key_attnums are interpreted as logical column numbers, corresponding to the column's position in SELECT * FROM relname. previous versions interpreted the numbers as physical column positions. 💀

and there's a difference if any column(s) to the left of the indicated column have been dropped during the lifetime of the table. yes. dropped columns still lurking in the physical layout. haunted house behaviour, honestly. 🧚 so if your table has been through some things, double-check those attnums before you commit to anything.

---

🔥 EXAMPLES 🔥

here she is in action, serving exactly what you'd expect 🔥

SELECT dblink_build_sql_insert('foo', '1 2', 2, '{"1", "a"}', '{"1", "b''a"}');
             dblink_build_sql_insert
--------------------------------------------------
 INSERT INTO foo(f1,f2,f3) VALUES('1','b''a','1')
(1 row)

look at her. '1' and 'a' went in as the source primary key, '1' and 'b''a' came out the other side, and the row got rebuilt with that escaped quote doing its little dance. ate and left no crumbs 👑

---

aaaand that's the page!! 💖 written against PostgreSQL 18.6, dated 2026, page DBLINK_B...QL_INSERT(3) — go build some INSERTs bestie, and remember: quoted relation names, 1-based attnums, and no dropped columns sneaking up on you 🎀✨🦋💅