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.
ERROR 3457 on COPY containing to_date
+2Hello: 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
Here's the doc pointer for those funcs:
https://my.vertica.com/docs/6.1.x/HTML/index.htm#8657.htm
+1>>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
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_DATEConverts a string value to a DATE type.
Behavior Type
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.
StableI 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.
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.
+1daniel=> 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)
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)
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.
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.
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.
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.
+1>> 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)
"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)
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.
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.
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.
Sign up
Already have an account? Login
Welcome to the Rocket Forum!
Please log in or register:
Employee Login | Registration Member Login | RegistrationEnter your E-mail address. We'll send you an e-mail with instructions to reset your password.
77 4th Avenue
Waltham, MA 02451 USA
