CODEWITHSUNDEEP
sqlpostgresql
user2393256
user2393256

Reputation: 1150

Set default value for all columns of a table

I have multiple tables with a lot of columns. Some columns contain Null values. Now, i want to set them to a default value (empty string or 0).

I found two two pieces of SQL that probably cover what i need but i can't put them together.

select column_name,data_type 
from information_schema.columns 
where table_name = 'tablename';

I can use the above code to get all columns and their data type and the snippet below to update Null values in columns.

Update tablename Set col1 = ''
Where col1 Is Null;

How can i put these two together? Sorry, if this is a basic question but i couldn't find a solution for this...

Upvotes: 0

Views: 649

Answers (1)

Pavel Stehule
Pavel Stehule

Reputation: 45795

Surely, this is not basic question, and solution is little bit hard (not for experts). You have to generate SQL statement from system data stored in table pg_attribute and pg_attrdef.

You can run a following query. The result is text of UPDATE command, that does what you want:

select 'update foo set ' || 
       string_agg(attname || 
                  ' = coalesce(' || attname || ', '|| 
                               (select pg_catalog.pg_get_expr(d.adbin, d.adrelid, true) 
                                  from pg_catalog.pg_attrdef d 
                                 where d.adrelid = a.attrelid 
                                   and d.adnum = a.attnum 
                                   and a.atthasdef )  || ')', ', ') 
 from pg_attribute a 
where attrelid = 'foo'::regclass and attnum > 0;

Example:

create table foo(a int default 0, b varchar default '');

run query and you get:

┌───────────────────────────────────────────────────────────────────────────┐
│                                 ?column?                                  │
╞═══════════════════════════════════════════════════════════════════════════╡
│ update foo set a = coalesce(a, 0), b = coalesce(b, ''::character varying) │
└───────────────────────────────────────────────────────────────────────────┘
(1 row)

Now you can run:

update foo set a = coalesce(a, 0), b = coalesce(b, ''::character varying)

Upvotes: 1

Related Questions