Prepared statements: Using unnamed and unnumbered question mark style positional placeholders

Question

In SQL systems other than Postgres, such as MySQL for instance, prepared statements can use question marks ? as a placeholder for data in prepared statements.

INSERT INTO foo (id, name) VALUES (?, ?), (?, ?);

However, in Postgres the only available placeholders seem to be the numbered placeholders, so the above INSERT statement for Postgres would look like:

INSERT INTO foo (id, name) VALUES ($1, $2), ($3, $4);

The problem with this syntax arises when I try to insert a large number of rows in one statement, for a 10,000 rows and 10 columns, I'd have to have placeholders ranging from $1 to $100,000 which leads to 3-4 fold increase in query size.

Does Postgres support any kinds of unnumbered and unnamed placeholders for prepared statement?

EDIT :

I am using prepared statements as they allow for defense against SQL Injections. So my objective is to keep my bulk inserts as fast as possible while keeping the insert process safe.

Create a prepared statement just once, using $1 and $2, and execute it many times. — Frank Heikens, Jun 22 '15 at 09:33
@FrankHeikens executing a prepared statement multiple times does not seem to have the advantage of faster bulk insertion that an INSERT statement with multiple rows offers. — Peeyush Kushwaha, Jun 22 '15 at 12:41
@FrankHeikens I'm using prepared statements to prevent against SQL Injections. Explicit EXECUTE statements seem to be useful only in the command line shell, making a query with `EXECUTE` will be somewhat pointless because then I'd have to prepare the `EXECUTE` statement itself to protect from any harmful values in the `EXECUTE` statement. — Peeyush Kushwaha, Jun 22 '15 at 12:50
Prepared statements are made for better performance, unless you commit after every statement. Start a transactions, create a prepared statement, execute many times, commit. That's it. Safe and fast. — Frank Heikens, Jun 22 '15 at 13:01
@JakubKania though I'm unable to find a citation for this, it has been my anecdotal experience that larger queries are somewhat slower. This is further reinforced for me by the performant accepted answer — Peeyush Kushwaha, Jun 22 '15 at 17:46
@FrankHeikens transactions did make multiple executions quite fast — Peeyush Kushwaha, Jun 22 '15 at 17:49
@PeeyushKushwaha I'd rather say it's the number of parameters not the size of the query that's a problem. — Jakub Kania, Jun 22 '15 at 22:53

klin · Accepted Answer · 2015-06-23T00:28:16.900

3

Prepared statements are used to speed up the repeated execution of the same query with different arguments. If your aim is to insert many rows at once it is better to execute regular insert query, which will be faster than the prepared insert. However, if you insisted on this solution, you could use arrays as arguments of prepared statement. Example:

create table foo(id int, val text);

prepare insert_into_foo (int[], text[]) as 
    insert into foo 
    select unnest(a), unnest(b)
    from (values ($1, $2)) v(a, b);

execute insert_into_foo (array[1,2,3], array['a','b','c']);
execute insert_into_foo (array[4,5,6,7], array['d','e','f','g']);

deallocate insert_into_foo;

The main disadvantage of this trick is that you need to be very careful to put the same number of arguments in the arrays. Any mistake could be painful. Therefore I would suggest to use it with a fuse:

prepare insert_into_foo (int[], text[]) as 
    insert into foo 
    select unnest(a), unnest(b)
    from (values ($1, $2)) v(a, b)
    where array_length(a, 1) = array_length(b, 1); -- fuse

execute insert_into_foo (array[1,2], array['a','b','c']); -- does nothing

edited Jun 23 '15 at 00:28

answered Jun 22 '15 at 12:30

klin

112,967
15
204
232

Aren't prepared statements also useful against SQL Injections? That is why I am using them for inserts. Edit: http://stackoverflow.com/questions/8263371/how-prepared-statements-can-protect-from-sql-injection-attacks – Peeyush Kushwaha Jun 22 '15 at 12:36
Yes, they are. There are many effective methods of defense against SQL injections, not necessarily at the expense of performance. I have edited the answer to be less assertive ;) – klin Jun 22 '15 at 12:44
This solution is extremely efficient. More efficient than many, many other solutions that I tested. This is probably due to the fact that it's only one query, query that's small in size, and rest that application is sending to the Postgres server is just row data. The other solutions I'm comparing it with involved multiple numerous queries (batched multiple inserts) or large-in-size queries (large and few multiple inserts), but this solution just simply shines out. – Peeyush Kushwaha Jun 22 '15 at 17:20

Prepared statements: Using unnamed and unnumbered question mark style positional placeholders

1 Answers1