how to insert DEFAULT into a prepared statement in PostgreSQL

Question

I'm trying to insert values into a database using prepared statements, but sometimes I need to insert for a certain value the literal 'DEFAULT', how do I do this?

CREATE TABLE test (id int, firstname text default 'john', lastname text default 'doe');

This is what I want to do, but then using a prepared statement:

insert into test (id, firstname, lastname) VALUES ('1', DEFAULT, DEFAULT);

But this is resulting in an error (for obvious reasons):

PREPARE testprep (integer, text, text) AS INSERT INTO test (id, firstname, lastname) VALUES ($1, $2, $3);
EXECUTE testprep('1',DEFAULT,DEFAULT);

The Error:

ERROR: syntax error at or near "DEFAULT"

Both examples I created using SQL-Fiddle:

http://sqlfiddle.com/#!15/243ae/1/0

http://sqlfiddle.com/#!15/243ae/3/0

Answer 1

There is no way to do that with a prepared statement.

The only escape would be a BEFORE INSERT trigger on the table that replaces certain data values (e.g. NULL) with the default value. But this is not a nice solution and will cost performance.

The other escape route is to use several prepared statements, one for each combination of values you want set to default.

Answer 2

You may try omitting the default columns from the insert statement:

PREPARE testprep (integer) AS
INSERT INTO test (id) VALUES ($1);
EXECUTE testprep('1');

Postgres should rely on the default values in the table definition for the firstname and lastname columns. From the Postgres documentation:

When a new row is created and no values are specified for some of the columns, those columns will be filled with their respective default values.