Does INSERT has a REJECTED DATA AS TABLE clause?

Answers

• Options

  Jim_Knicely - Select Field - Administrator

  September 2021 edited September 2021

  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)
  

  0