Does INSERT has a REJECTED DATA AS TABLE clause?

markus_zhang

Hi experts,
When I INSERT from SELECT clause, sometimes the data failed to be inserted (for example Non NULL meets NULL, VARCHAR(20) meets VARCHAR(36), etc.). Is there a way to continue INSERT but put the rejected data into a secondary table as COPY?

Thanks!

Jim_Knicely

Why not just add a WHERE clause to exclude the rows that will fail to INSERT?

verticademos=> SELECT * FROM some_data_table;
 c1 | c2
----+----
  1 | A
  2 |
  3 | C
(3 rows)

verticademos=> SELECT * FROM some_data_table2;
 c1 | c2
----+----
(0 rows)

verticademos=> \d some_data_table2;
                                         List of Fields by Tables
 Schema |      Table       | Column |    Type     | Size | Default | Not Null | Primary Key | Foreign Key
--------+------------------+--------+-------------+------+---------+----------+-------------+-------------
 public | some_data_table2 | c1     | int         |    8 |         | f        | f           |
 public | some_data_table2 | c2     | varchar(80) |   80 |         | t        | f           |
(2 rows)

verticademos=> INSERT INTO some_data_table2 SELECT * FROM some_data_table;
ERROR 2501:  Cannot set a NOT NULL column (c2) to a NULL value in INSERT/UPDATE statement

verticademos=> INSERT INTO some_data_table2 SELECT * FROM some_data_table WHERE c2 IS NOT NULL;
 OUTPUT
--------
      2
(1 row)

verticademos=> SELECT * FROM some_data_table2;
 c1 | c2
----+----
  1 | A
  3 | C
(2 rows)

Or, you could pipe the data into a COPY command and use the REJECTED DATA TABLE feature to capture the rejections....

verticademos=> TRUNCATE TABLE some_data_table2;
TRUNCATE TABLE

verticademos=> \! vsql -Atc "SELECT * FROM some_data_table;" | vsql -c "COPY some_data_table2 FROM STDIN REJECTED DATA TABLE some_data_table2_bad;"

verticademos=> SELECT * FROM some_data_table2;
 c1 | c2
----+----
  1 | A
  3 | C
(2 rows)

verticademos=> SELECT row_number, rejected_data, rejected_reason FROM some_data_table2_bad;
 row_number | rejected_data |            rejected_reason
------------+---------------+---------------------------------------
          2 | 2|            | Null value for NOT NULL column 2 (c2)

(1 row)