Is there an easy way to replace null with a default value?

Question

I would like to copy data from one table into another, but the source table may have nulls in columns where the destination table does not allow nulls but has default values:

drop table if exists table_with_nulls;
create table table_with_nulls
(
    value1 integer,
    value2 integer
);
insert into table_with_nulls values (1, null);
drop table if exists table_with_defaults;
create table table_with_defaults
(
    value1 integer not null default 10,
    value2 integer not null default 20
);
insert into table_with_defaults (value1, value2)
select value1, value2 from table_with_nulls;

That throws an exception due to the null value from table_with_nulls.value2. Is there a reasonably easy way to get a value of 20 for table_with_defaults.value2?

If no better ideas, use an in-between table and change all nulls to the defaults for the destination. a >b, fix b, b > c. — Dave S
– Dave S, Commented Aug 13, 2018 at 20:58

klin · Accepted Answer · 2018-08-14 14:10:16Z

3

Example table:

create table table_with_defaults
(
    value1 integer not null default 10,
    value2 numeric not null default 0.0,
    value3 text not null default '-- nothing --',
    value4 date not null default current_date,
    value5 text
);

You can query the system catalogs pg_attribute and pg_attrdef to find default expressions for the table columns:

select attname, adsrc
from pg_attribute a
left join pg_attrdef d on adrelid = attrelid and adnum = attnum
where attnum > 0
and attrelid = 'table_with_defaults'::regclass;

 attname |         adsrc         
---------+-----------------------
 value1  | 10
 value2  | 0.0
 value3  | '-- nothing --'::text
 value4  | ('now'::text)::date
 value5  | 
(5 rows)

Use the query in a plpgsql function to build and execute an appropriate statement:

create or replace function copy_table_with_defaults(table_from regclass, table_to regclass)
returns void language plpgsql as $$
declare
    column_list text;
begin

    select string_agg(
        case when adsrc is null then attname 
        else format('coalesce(%I, %s)', attname, adsrc) 
        end, ',')
    from pg_attribute a
    left join pg_attrdef d on adrelid = attrelid and adnum = attnum
    where attnum > 0
    and attrelid = table_to
    into column_list;

    execute format($ex$
        insert into %I
        select %s
        from %I
        $ex$, table_to, column_list, table_from);
end $$;

Use the function:

select copy_table_with_defaults('table_with_nulls'::regclass, 'table_with_defaults'::regclass);

Working example in rextester.

edited Aug 14, 2018 at 14:10

answered Aug 13, 2018 at 21:51

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ROBERT RICHARDSON Over a year ago

Thank you, but I don't want to assume I know the default values. Some columns might have constant defaults, others might use nextval() on a sequence, some might be integers, others might be text, ...

klin Over a year ago

There are no builtin generic means to do that. coalesce() works on any type. Anyway, I think you cannot create a table with unknown constraints.

ROBERT RICHARDSON Over a year ago

I'm not creating a table with unknown constraints. I'm inserting data into a table, and I'm hoping I don't have to look up the default value of every field and write it into the insert query as you did above. I've got about 50 tables to do this for. But the more I think about this, the more I conclude that what I want to do here is not possible.

ROBERT RICHARDSON · Accepted Answer · 2018-08-14 17:00:33Z

0

Thanks very much, @klin! I had to tweak it a bit because I need to be able to specify the schemas of the two tables. Here is is what I came up with:

create or replace function conversion.copy_table_with_defaults_ex(schema_from text, table_from text, schema_to text, table_to text)
returns void language plpgsql as $$
declare
    column_list text;
begin
    select string_agg(
        case when adsrc is null then attname 
        else format('coalesce(%I, %s)', attname, adsrc) 
        end, ',')
    from pg_attribute a
    left join pg_attrdef d on adrelid = attrelid and adnum = attnum
    left join pg_class c on c.oid = a.attrelid
    left join pg_namespace ns on c.relnamespace = ns.oid
    where attnum > 0
    and c.relname = table_to
    and ns.nspname = schema_to
    into column_list;

    execute format($ex$
        insert into %I.%I
        select %s
        from %I.%I
        $ex$, schema_to, table_to, column_list, schema_from, table_from);
end $$;

answered Aug 14, 2018 at 17:00

ROBERT RICHARDSON

2,4315 gold badges31 silver badges64 bronze badges

2 Comments

klin Over a year ago

That's ok but unnecessary. The original function uses regclass arguments, so you can pass a schema-qualified table name, e.g. select copy_table_with_defaults('public.table_with_nulls'::regclass, 'public.table_with_defaults'::regclass); Rextester

ROBERT RICHARDSON Over a year ago

Thank you. I've never worked with regclass arguments before.

Collectives™ on Stack Overflow

Is there an easy way to replace null with a default value?

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related