How to use DEFAULT in postgres?

Question

I have table and function:

create table test_temporal (
  id SERIAL,
  name varchar(16)
);

create or replace function test_temporal_insert()
returns trigger as $$
begin
INSERT INTO test_temporal ( id, name )
values ( coalesce( new.id, DEFAULT ), new.name );
return NEW;
end;
$$ language plpgsql;

But when I try to insert I get error:

insert into test ( name ) values ( 'a' );

ERROR:  DEFAULT is not allowed in this context
LINE 2: values ( coalesce( new.id, DEFAULT ), new.name )
                                   ^
QUERY:  INSERT INTO test_temporal ( id, name )
values ( coalesce( new.id, DEFAULT ), new.name )
CONTEXT:  PL/pgSQL function test_temporal_insert() line 3 at SQL statement

In my trigger I need to generate next value from sequence (DEFAULT) if user does not provide value for id. If user provide value for id I should use it (new.id)

How to insert DEFAULT value when no new.id is supplied?

The dbfiddle

You need to add default value when creating a table. Reference : https://www.postgresql.org/docs/9.3/ddl-default.html — prabin badyakar, Jan 12 '19 at 09:45
@KaushikNayak: `select version(); PostgreSQL 10.4 (Debian 10.4-2.pgdg90+1)` — Eugen Konkov, Jan 12 '19 at 09:50
It's working in 10.6, see [dbfiddle](https://dbfiddle.uk/?rdbms=postgres_10&fiddle=62404e9a30ce1facb839ca97629d6c67) . If you remove the `coalesce`, it will compiles in older versions [9.6 demo](https://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=93dae34afe9f04e253ababd54fd4c530) — Kaushik Nayak, Jan 12 '19 at 09:52
@KaushikNayak: [it](https://dbfiddle.uk/?rdbms=postgres_10&fiddle=f97c234e3708dbb7d37a25bf9c411455) does not — Eugen Konkov, Jan 12 '19 at 09:54
@prabinbadyakar: I have default because column type is `SERIAL` — Eugen Konkov, Jan 12 '19 at 10:02

score 0 · Answer 1 · answered Jan 12 '19 at 10:10

0

I resolve that via condition:

create or replace function test_temporal_insert()
returns trigger as $$
begin
IF( new.id is null ) THEN
  INSERT INTO test_temporal ( name )
  values ( new.name );
ELSE
  INSERT INTO test_temporal ( id, name )
  values ( new.id, new.name );
END IF;
return NEW;
end;
$$ language plpgsql;

It would be much clear if Postgresql allow coalesce( new.id, DEFAULT ):

INSERT INTO test_temporal ( id, name )
values ( coalesce( new.id, DEFAULT ), new.name );

answered Jan 12 '19 at 10:10

Eugen Konkov

22,193
17
108
158

You can however explicitly specify default without a `coalesce` Like [this](https://dbfiddle.uk/?rdbms=postgres_11&fiddle=e18ec300ae4556a1400dd2556e4cd8a8) – Kaushik Nayak Jan 12 '19 at 10:14
@KaushikNayak: Yes, but there still `IF THEN ELSE` block =( – Eugen Konkov Jan 12 '19 at 10:21

How to use DEFAULT in postgres?

1 Answers1