2

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

DeniseMeander
  • 806
  • 2
  • 9
  • 28
  • Possible duplicate of [Inserting a COALESCE(NULL,default)](https://stackoverflow.com/questions/39165583/inserting-a-coalescenull-default) – Bergi Nov 05 '19 at 01:53

2 Answers2

2

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
1

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.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • In my real situation I have 21 fields in my insert statement and I use the same prepared statement multiple times and different fields may need to be set 'DEFAULT', just leaving out one or two... I cannot imagine postgresql will know then which I left out. – DeniseMeander Nov 04 '19 at 13:56
  • Then you either need a flexible prepared statement, or more likely you just need to have a collection of separate statements. – Tim Biegeleisen Nov 04 '19 at 13:57
  • Lets say I have only a firstname or a last name, and I leave one or the other, how does postgresql know if it is the first name or the last name provided? – DeniseMeander Nov 04 '19 at 13:59
  • Postgres only "knows" the columns you are targeting in an insert because you specify them in the statement. – Tim Biegeleisen Nov 04 '19 at 14:02
  • Yes, my point exactly! There has to be a way to do this... I cannot imagine they didn't think of a way to do these kinds of things...Seems to my this should be basic functionality. – DeniseMeander Nov 04 '19 at 14:04
  • No, in general statements are _not_ one size fits all. You should ideally know exactly which columns you want to target. This is a result of statements not allowing things like SQL injection to happen. – Tim Biegeleisen Nov 04 '19 at 14:05
  • The whole idea of a prepared statement is to have a query which you will you multiple times with different sets of data. in some cases I do not have all the data and I rely on my DEFAULT field settings, things like a default state or an '{}' for a jsonb field. Previously I used the normal INSERT queries, just a string which I concatenate, now I'm rewriting this old code to more standard and safer methods... – DeniseMeander Nov 04 '19 at 14:10
  • 1
    Postgres (SQL) requires all columns and types to be defined before a statement is executed, as you obviously know. This is a feature, not a bug. A super annoying, off-putting feature that makes SQL code off-puttingly verbose and badly matched to situations where you have variable inputs or dynamic outputs. But in a good way ;-) SQL's declarative nature is wonderfulannoying. – Morris de Oryx Nov 04 '19 at 21:48