Example: Using the DEFAULT Function With INSERT … SELECT
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.