COPY syntax??

Ok, this works, but I have no idea why.

copy finicity (w, x, y, a filler varchar(50), z )
from '/home/dbadmin/finicity/test.txt'
DELIMITER E'\001'
ENCLOSED BY '"'
rejected data as table finicity_rejects
DIRECT ;

Maybe it's a way of loading the 4th column into a field that actually accepts it - and then it does an auto-cast when it assigns it to z?

I might have to file this as a bug. It doesn't make a lot of sense to me.

Now I know why it's working - because it's completely ignoring the 4th column. Back to square one...

Ok, so I think I found a solution to this. 6 years I've worked for Vertica. How come I never knew about ::!? Am I alone in this?

https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/SQLReferenceManual/LanguageElements/Operators/CastFails.htm?Highlight=cast

That's an issue I've hit in the past!

dbadmin=> \! cat /home/dbadmin/curtis.txt
""
"1"

dbadmin=> \d test
                                List of Fields by Tables
 Schema | Table | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key
--------+-------+--------+------+------+---------+----------+-------------+-------------
 public | test  | c      | int  |    8 |         | f        | f           |
(1 row)

dbadmin=>  COPY test FROM '/home/dbadmin/curtis.txt' ENCLOSED BY '"' REJECTED DATA TABLE curtis_bad;
 Rows Loaded
-------------
           1
(1 row)

dbadmin=> SELECT rejected_data, rejected_reason FROM curtis_bad;
 rejected_data |              rejected_reason
---------------+--------------------------------------------
 ""            | Invalid integer format '' for column 1 (c)
(1 row)

But the load works fine if the table column is a VARCHAR:

dbadmin=> \d test2
                                   List of Fields by Tables
 Schema | Table | Column |    Type     | Size | Default | Not Null | Primary Key | Foreign Key
--------+-------+--------+-------------+------+---------+----------+-------------+-------------
 public | test2 | c      | varchar(10) |   10 |         | f        | f           |
(1 row)

dbadmin=>  COPY test2 FROM '/home/dbadmin/curtis.txt' ENCLOSED BY '"' REJECTED DATA TABLE curtis_bad2;
 Rows Loaded
-------------
           2
(1 row)

dbadmin=> SELECT * FROM test2;
 c
---

 1
(2 rows)

But you found the right way to handle it:

dbadmin=> TRUNCATE TABLE test;
TRUNCATE TABLE

dbadmin=> DROP TABLE curtis_bad;
DROP TABLE

dbadmin=> COPY test (c_filler FILLER VARCHAR, c AS c_filler::!INT)FROM '/home/dbadmin/curtis.txt' ENCLOSED BY '"' REJECTED DATA TABLE curtis_bad;
 Rows Loaded
-------------
           2
(1 row)

dbadmin=> SELECT * FROM test;
 c
---

 1
(2 rows)

I didn't know about that "nifty type of cast (::!)" until Ben Vandiver mentioned it in a post here:

https://forum.vertica.com/discussion/comment/240469#Comment_240469

And I've been using ever since

https://forum.vertica.com/discussion/comment/241138#Comment_241138

I only stumbled upon it because I punted, and went to your step 2 - making the fields type char. And then you can get the same exact error just trying to cast from that holding table into the target table. At that point, I knew that had to be a solvable problem, and wasn't limited to just COPY. A google search led to a deprecated version 8 copy of the documentation, but I looked at the URL and found the equivalent version 9 page, and found that. So obscure.. yet so useful.Sounds like a good tip of the day!

Thanks for the validation, btw.

Great idea about the tip!

https://forum.vertica.com/discussion/240126/return-all-cast-failures-as-null

FYI ...

Note that the ::! cast works great on table data:

dbadmin=> CREATE TABLE test (c VARCHAR(10));
CREATE TABLE

dbadmin=> INSERT INTO test SELECT '';
 OUTPUT
--------
      1
(1 row)

dbadmin=> SELECT c, c::INT FROM test;
ERROR 2827:  Could not convert "" from column test.c to an int8

dbadmin=> SELECT c, c::!INT FROM test;
 c | c
 --+---
   |
(1 row)

But it's a little wonky with string literals:

dbadmin=> SELECT ''::INT;
ERROR 3681:  Invalid input syntax for integer: ""

dbadmin=> SELECT ''::!INT;
ERROR 3681:  Invalid input syntax for integer: ""