Upon insert, how can I programmatically convert a null value to the column's default value?

Question

I have a table in for which I have provided default values for some of its columns. I want to create a function with arguments corresponding to the columns that will insert a record in the table after modifying any null value to the column's default value.I dont want to programmatically construct the query based on which arguments are null. Essentially I would like something like

INSERT into Table (c1, c2, c3, c4)
Values (coalesce(somevar, DEFAULT(c1)), ...)

Is this possible? I ve seen that mysql can do this. Does postgres offer anything similar? I am using version 9.1

UPDATE: This question provides some interesting solution but unfortunately the results are always text. I would like to get the default value as its true datatype so that I can use it for inserting it. I have tried to find a solution that will cast the default value from text to its datatype (which is provided as text) but I can't find a way:

SELECT column_name, column_default, data_type
FROM information_schema.columns
WHERE (table_schema, table_name) = ('public', 'mytable')
AND column_name = 'mycolumn'
ORDER BY ordinal_position;

The above returns the column_default and data_type as text so how can I cast the column_default to the value of data_type? If I could do this, then my problem would be solved.

Can you explain? My columns are declared as NOT NULL DEFAULT xxx — Paralife, Jan 08 '13 at 11:19
Also the keyword DEFAULT cannot be used inside a CASE or coalesce. It throws an error: insert into table (c1, c2) values (some_var, coalesce(some_other_var, DEFAULT)) --> throws a syntax error — Paralife, Jan 08 '13 at 11:28

score 1 · Accepted Answer · answered Jan 08 '13 at 23:38

If the table definition accepts an INSERT with the default values for all columns, the two-steps method below may work:

CREATE FUNCTION insert_func(c1 typename, c2 typename, ...)
RETURNS VOID AS $$
DECLARE
 r record;
BEGIN
 INSERT into the_table default values  returning *,ctid INTO r;
 UPDATE the_table set
  col1=coalesce(c1,r.col1),
  col2=coalesce(c2,r.col2),
  ...
 WHERE the_table.ctid=r.ctid;
END;
$$ language plpgsql;

The trick is to get all the default values into the r record variable while inserting and use them subsequently in an UPDATE to replace any non-default value. ctid is a pseudo-column that designates the internal unique ID of the row that has just been inserted.

Caveat: this method won't work if some columns have a default null value AND a non-null check (or any check that implies that the default value is not accepted), since the first INSERT would fail.

Paralife · Answer 2 · 2013-01-09T10:14:41.747

I ve worked around my problem with a solution similar to Daniel's by creating a temp table with LIKE and INCLUDING DEFAULTS clauses in order to match my rowtype, then i use

INSERT INTO temp_table (c1, c2, ...) VALUES(x1, DEFAULT, ..)

using the default keyword for whatever column i am interested in. Then I insert to the real table by selecting from the temporary and using

VALUES( x1, coalesce(x2, temp_table.c2), ...).

I dont like it, but it works ok: I can select which on which columns I would like to do this "null-replace-with-default" check and it could work for many rows with one pass if I overload my function to accept a record array.

Upon insert, how can I programmatically convert a null value to the column's default value?

2 Answers2