ERROR 3457 on COPY containing to_date

Katherine_Oakey

I have a table with 6 columns including a varchar (that I want to fill with a constant), a timestamp and 5 bigints.

The input file is a CSV where the varchar field is not included (ie, the table has 7 columns, the CSV has 6 columns).

My COPY looks like

COPY task_stats (
   task_name as 'ADD_DEALER',
   sample_time as to_date(sample_time,'DD-MON-YYYY HH:SS'),
   response as response,
   count as count,
   max as max,
   min as min,
   over4 as over4
)
FROM 'ADD_DEALER.CSV' DELIMITER ',' NULL '' SKIP 4 DIRECT;

A sample input row from the CSV appears as

  5-JUL-2013 09:30,     500,       1,     500,     500,       0

When I run the load (in a script) I get the following error:

stms-> \i load_task_stats.sql
vsql:load_task_stats.sql:11: ERROR 3457:  Function to_date(timestamp, unknown) does not exist, or permission is denied for to_date(timestamp, unknown)
HINT:  No function matches the given name and argument types. You may need to add explicit type casts

What am I missing?

The column name in the table is sample_time and is of datatype timestamp.

[Deleted User]

Hello: There are a lot of date/time functions, perhaps you just need DATE or TIME?
Here's the doc pointer for those funcs: 
https://my.vertica.com/docs/6.1.x/HTML/index.htm#8657.htm

Daniel_Leybovic

>>What am I missing?
You don't read documetation.
Wrong data type. Fuction to_date accepts VARCHAR or CHAR, not TIMESTAMP.
https://my.vertica.com/docs/6.1.x/HTML/index.htm#9145.htm

TO_DATE

Converts a string value to a DATE type.

Behavior Type

Stable

Syntax

expression , pattern )TO_DATE ( 

Parameters 

expression

(CHAR or VARCHAR) specifies the value to convert.

pattern

(CHAR or VARCHAR) specifies an output pattern string using the Template Patterns for Date/Time Formatting and/or Template Patterns for Numeric Formatting.

Katherine_Oakey

I did read the documentation.  I have a timestamp field in the table.  I have a character-type date/time in the CSV (in the pattern used in the TO_DATE function).

The table has 7 columns (not clear from my original post).  The CSV has 6 columns, the first of which is text type date/time.  I want a fixed value that I provide in the COPY for the first column.  How do I "map" the 6 columns in the CSV to the correct columns in the table (given the table has 7 columns)?

Or can I not do this? (ie, number of columns in the CSV must match number of columns in the table?)

I looked at a number of examples and couldn't determine how to properly do this.

Daniel_Leybovic

daniel=> create table task_stats (fixed varchar(16), d1 date, d2 date);
CREATE TABLE
daniel=> copy task_stats (fixed as 'FIXED', d1 format 'D-Mon-YYYY', d2 format 'YYYY-Mon-D')
daniel-> from stdin direct delimiter ',' abort on error;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 5-Jul-2013,2013-Jan-01
>> \.
daniel=> select * from task_stats ;
 fixed |     d1     |     d2    
-------+------------+------------
 FIXED | 2013-07-01 | 2013-01-01
(1 row)

Katherine_Oakey

Thank you.

As the "date" column contains both date and time, it seemed as though timestamp was the appropriate datatype.  We will want to be correlating data on both the fixed column as well as the timestamp field. (Yes, I will review what can be contained in a date vs timestamp.)

Would I use the FORMAT rather than TO_DATE function if the input is coming from a file (rather than stdin)? 

Looks like I did guess correctly on how to populate the first column in the table with a fixed value.

Katherine_Oakey

I do see one error that I did (didn't read far enough in the documentation. I want to_timestamp (not to_date).

What I still don't understand is how to map a column in the CSV to a column in the table.

The timestamp column in the CSV is in the pattern I used in the to_date and should be treated as a CHAR.

Daniel_Leybovic

>> Looks like I did guess correctly on how to populate the first column in the table with a fixed value.
"guess"  - means you did read.

daniel=> \! cat /tmp/kath.data
5-JUL-2013 09:30,500,1,500,500,0
daniel=> create table task_stats (task_name varchar(16), sample_time timestamp, response int, count int, max int, min int, over4 int);
CREATE TABLE
daniel=> copy task_stats (task_name as 'READ DOCS!', sample_time format 'D-Mon-YYYY HH:MI', response, count, max, min, over4) from '/tmp/kath.data' direct delimiter ',' abort on error;
 Rows Loaded
-------------
           1
(1 row)

daniel=> select * from task_stats ;
 task_name  |     sample_time     | response | count | max | min | over4
------------+---------------------+----------+-------+-----+-----+-------
 READ DOCS! | 2013-07-01 09:30:00 |      500 |     1 | 500 | 500 |     0
(1 row)

Katherine_Oakey

Thank you.  Was not sure when to use AS vs FORMAT and also how to handle the fact the CSV didn't have as many columns as the table.

I did read and re-read but it was not clear how I should set things up.  Looked at all the examples in both the Admin and SQL Ref but nothing showed or gave a clue (at least to me) how best to handle this.

Katherine_Oakey

And let me rephrase a bit.  AS is a noise word.  Didn't know if/when to use FORMAT vs the TO_TIMESTAMP function;  I assumed incorrectly after looking at examples in the docs that TO_TIMESTAMP was appropriate.