A cursed insert in postgres

Posted on February 9, 2025
Tags: git

Inserting data into a postgresql database is straightforward:

CREATE TABLE users (
    name VARCHAR(100),
    email VARCHAR(255),
    age INTEGER
);

insert into users (name, email, age) VALUES ('bob', 'bob@bob.com', 21), ('alice', 'alice@alice.com', 21);

THe issue is that the maximum amount of parameters that you can bind is 65,535 that is because the number of parameters is encoded as ain int16 in the PG wire protocol.

One way to workaround it is to use unnest:

INSERT INTO users (name, email, age)
SELECT name, email, age
FROM UNNEST(
    ARRAY['bob', 'alice'],
    ARRAY['bob@bob.com', 'alice@alice.com'],
    ARRAY[21, 21]
) AS t(name, email, age);

If you are passing the parameters your query will look similar to this:

INSERT INTO users (name, email, age)
	select * from unnest(
			$1::varchar[], $2::varchar[], $3::integer[]
	);

This works well, is super fast but there is a small issue. What if we have an array column? You may think we can just pass in the array and do:


ALTER TABLE users ADD COLUMN tags VARCHAR[] DEFAULT NULL;

INSERT INTO users (name, email, age, tags)
	select * from unnest(
			$1::varchar[], $2::varchar[], $3::integer[], $4::varchar[][],
	);

But unnest flattens the array, let me show you:

 select UNNEST(ARRAY[Array['bob'], Array['alice']]);
 unnest
--------
 bob
 alice

So what is the workaround? One workaround is maybe “cursed”, maybe not. If you enocode your whole insert payload as a jsonb array you can do:

insert into users
  select record_to_insert.* 
  from jsonb_array_elements($1::jsonb) json_element,
  jsonb_populate_record(null::users, json_element) as record_to_insert;

Wow, what is happening here? Let me break it down:

  1. jsonb_array_elements($1::jsonb) - This function takes a JSONB array as input (passed as a parameter $1) and returns each element of the array as a separate row
  2. jsonb_populate_record(null::users, json_element) - This function: takes each JSON object (json_element) from the array then creates a record matching the users table structure and finally maps the JSON fields to the corresponding table columns.

PREPARE insert_users(jsonb) AS
   INSERT INTO users 
   SELECT record_to_insert.* 
   FROM jsonb_array_elements($1::jsonb) json_element,
   jsonb_populate_record(null::users, json_element) as record_to_insert;

EXECUTE insert_users('[
   {"name": "John Doe", "email": "john@example.com", "age": 30, "tags": ["developer", "nodejs"]},
   {"name": "Jane Smith", "email": "jane@example.com", "age": 25, "tags": ["designer", "ui"]},
   {"name": "Bob Wilson", "email": "bob@example.com", "age": 35, "tags": ["manager", "agile"]}
]');