In Postgres, how would I retrieve the default value of a column, preferably inline in an insert statement?

Question

Here's my example table:

CREATE TABLE IF NOT EXISTS public.cars
(
    id serial PRIMARY KEY,  
    make varchar(32) not null,
    model varchar(32),
    has_automatic_transmission boolean not null default false,
    created_on_date timestamptz not null DEFAULT NOW()
);  

I have a function that allows my data service to insert a car into the database. It looks like this:

drop function if exists cars_insert; 
 
create function cars_insert
(
    in make_in text,
    in model_in text,
    in has_automatic_transmission_in boolean,
    in created_on_date_in timestamptz 
)  
returns public.carsas 
$$
    declare result_set public.cars;
    begin   
        insert into cars
        (
            make,
            model,
            has_automatic_transmission,
            created_on_date
        ) 
        values 
        (
            make_in,
            model_in,
            has_automatic_transmission_in,
            created_on_date_in
        ) 
        returning * into result_set;  

        return result_set;
    end;
$$

language 'plpgsql';

This works really well until the service wants to insert a car with no value for has_automatic_transmission or created_on_date. In that case they'd send null for those parameters and would expect the database to use a default value. But instead the database rejects that null for obvious reasons (NOT NULL!).

What I want to do is have the insert routine do a coalesce to DEFAULT, but that doesn't work. Here's the logic I want for the insert:

insert into cars
        (
            make,
            model,
            has_automatic_transmission,
            created_on_date
        ) 
        values 
        (
            make,
            model,
            COALESCE(has_automatic_transmission_in, DEFAULT),
            COALESCE(created_on_date_in, DEFAULT)
        ) 

How can I effectively achieve that? Ideally it'd be some method I can apply inline to every column so that we don't need special knowledge of which columns do or don't have defaults, but I'll take anything at this point...

Except I'd like to avoid Dynamic SQL if possible.

Answer 1

You need two Insert Statements; one where the Nullable columns are filled and another one which omits these columns as the default is only used if you do not reference the columns for insert.

Answer 2

Building on the suggestions made by previous commentators, I would write a function that generates, in a dynamic fashion, an insert function for each table.

The advantage of such approach is that the resulting insert function will not use dynamic SQL at all.

Function generating function:

CREATE OR REPLACE FUNCTION f_generate_insert_function(tableid regclass) RETURNS VOID LANGUAGE PLPGSQL AS
$$
DECLARE
    tablename text := tableid::text;
    funcname text := tablename || '_insert';

    ddl text := $ddl$
        CREATE OR REPLACE FUNCTION %s (%s) RETURNS %s LANGUAGE PLPGSQL AS $func$
        DECLARE
            result_set %s;
        BEGIN
            INSERT INTO %s
            (
                %s
            )
            VALUES
            (
                %s
            )
            RETURNING * INTO result_set;

            RETURN result_set;
        END;
        $func$
    $ddl$;

    argument_list text := '';
    column_list   text := '';
    value_list    text := '';

    r record;
BEGIN

    FOR r IN
        SELECT attname nam, pg_catalog.format_type(atttypid, atttypmod) typ, pg_catalog.pg_get_expr(adbin, adrelid) def
        FROM pg_catalog.pg_attribute
        JOIN pg_catalog.pg_type t
        ON t.oid = atttypid
        LEFT JOIN pg_catalog.pg_attrdef
            ON adrelid = attrelid AND adnum = attnum AND atthasdef
        WHERE attrelid = tableid
          AND attnum > 0
    LOOP
        IF r.def LIKE 'nextval%' THEN
            CONTINUE;
        END IF;
        argument_list := argument_list || r.nam || '_in ' || r.typ || ',';
        column_list   := column_list   || r.nam || ',';
        IF r.def IS NULL THEN
            value_list   := value_list || r.nam || '_in,';
        ELSE
            value_list   := value_list || 'coalesce(' || r.nam || '_in,' || r.def || '),';
        END IF;
    END LOOP;

    argument_list := rtrim(argument_list, ',');
    column_list   := rtrim(column_list,   ',');
    value_list    := rtrim(value_list,    ',');

    EXECUTE format(ddl, funcname, argument_list, tablename, tablename, tablename, column_list, value_list);
END;
$$;

In your case, the resulting insert function will be:

CREATE OR REPLACE FUNCTION public.cars_insert(make_in character varying, model_in character varying, has_automatic_transmission_in boolean, created_on_date_in timestamp with time zone)
 RETURNS cars
 LANGUAGE plpgsql
AS $function$
        DECLARE
            result_set cars;
        BEGIN
            INSERT INTO cars
            (
                make,model,has_automatic_transmission,created_on_date
            )
            VALUES
            (
                make_in,model_in,coalesce(has_automatic_transmission_in,false),coalesce(created_on_date_in,now())
            )
            RETURNING * INTO result_set;

            RETURN result_set;
        END;
        $function$

Answer 3

I like Erwin's solution from the playfulness point of view, but it is quite expensive to have these subqueries in every INSERT. For practical purposes, I would recommend one of the following:

• Have four INSERT statements in the function, one for each combination of default/non-default arguments, and use IF statements to pick the right one.

• Don't use DEFAULT, but write a BEFORE INSERT trigger that replaces NULLs with the appropriate value.

Of course this will add overhead too. You should benchmark the different options.

Answer 4

While you need to pass values to a function, and want to insert default values instead of NULL dynamically, you could look them up like this (but see disclaimer below!):

CREATE OR REPLACE FUNCTION cars_insert (make_in text
                                      , model_in text
                                      , has_automatic_transmission_in boolean
                                      , created_on_date_in timestamptz)  
  RETURNS public.cars AS
$func$
INSERT INTO cars(make, model, has_automatic_transmission, created_on_date) 
VALUES (make_in
      , model_in
      , COALESCE(has_automatic_transmission_in
               , (SELECT pg_get_expr(d.adbin, d.adrelid)::bool -- default_value
                  FROM   pg_catalog.pg_attribute a
                  JOIN   pg_catalog.pg_attrdef   d ON (d.adrelid, d.adnum) = (a.attrelid, a.attnum) 
                  WHERE  a.attrelid = 'public.cars'::regclass
                  AND    a.attname  = 'has_automatic_transmission'))
      , COALESCE(created_on_date_in
               , (SELECT pg_get_expr(d.adbin, d.adrelid)::timestamptz -- default_value
                  FROM   pg_catalog.pg_attribute a
                  JOIN   pg_catalog.pg_attrdef   d ON (d.adrelid, d.adnum) = (a.attrelid, a.attnum) 
                  WHERE  a.attrelid = 'public.cars'::regclass
                  AND    a.attname  = 'created_on_date'))
       )
RETURNING *;
$func$
LANGUAGE sql;

db<>fiddle here

You also have to know the column type to cast the text returned from pg_get_expr().

I simplified to an SQL function, as nothing here requires PL/pgSQL.

See:

• Get the default values of table columns in Postgres?

However, this only works for constants and types where a cast from text is defined. Other expressions (incl. functions) are not evaluated without dynamic SQL. now() in the example only happens to work by coincidence, as 'now' (ignoring parentheses) is a special input string for timestamptz that evaluates to the the same as the function now(). Misleading coincidence. See:

• Difference between now() and current_timestamp

To make it work for expressions that have to be evaluated, dynamic SQL is required - which you ruled out. But if dynamic SQL is allowed, it's much more efficient to build the target list of the INSERT dynamically and omit columns that are supposed get default values. Or keep the target list constant and switch NULL values for the DEFAULT keyword. See:

• Function to INSERT dynamic list of columns in multiple tables
• Test for null in function with varying parameters
• Generate DEFAULT values in a CTE UPSERT using PostgreSQL 9.3