Assume the following table definitions for this example:
CREATE TABLE tbl_source ( col1 INTEGER, col2 INTEGER DEFAULT 10, col3 INTEGER DEFAULT 20, col4 CHARACTER(60)); CREATE TABLE tbl_destination ( col1 INTEGER, col2 INTEGER DEFAULT 10, col3 INTEGER DEFAULT 20, col4 CHARACTER(60));
The following example shows correct use of the DEFAULT function within an INSERT … SELECT request:
INSERT INTO tbl_destination (col1) SELECT COALESCE(col3, DEFAULT(col3)) END FROM tbl_source WHERE col3 <> DEFAULT;
In this example, the DEFAULT function evaluates to a constant value, which is the default value of col3 of tbl_source.