CODEWITHSUNDEEP
dateamazon-redshiftpsql
swarup
swarup

Reputation: 11

How to upload data into a Redshift Table with a Date Format 'MMDDYYYY'

I need to upload a Data in the format 'MMDDYYYY'

current way code i am using to send via psql

SET BaseFolder=C:\


psql -h hostname -d database -c "\copy test_table(id_test, 
colum_test,columndate DATEFORMAT 'MMDDYYYY') 
from '%BaseFolder%\test_table.csv' with delimiter ',' CSV HEADER;"

here test_table is the table in the postgres DB

Id_test: float8

Column_test: float8

columndate: timestamp

id_test colum_test  colum_date

94  0.3306  12312017

16  0.3039  12312017

25  0.5377  12312017

88  0.6461  12312017

i am getting the following error when i run the above query in CMD in windows 10

ERROR: date/time field value out of range: "12312017"

HINT: Perhaps you need a different "datestyle" setting.

CONTEXT: COPY test_table, line 1, column columndate : "12312017"

Upvotes: 0

Views: 3382

Answers (2)

John Rotenstein
John Rotenstein

Reputation: 270294

The DATEFORMAT applies to the whole COPY command, not a single field.

I got it to work as follows...

Your COPY command suggests that the data is comma-separated, so I used this input data and stored it in an Amazon S3 bucket:

id_test colum_test,colum_date
94,0.3306,12312017
16,0.3039,12312017
25,0.5377,12312017
88,0.6461,12312017

I created a table:

CREATE TABLE foo (
    foo_id BIGINT,
    foo_value DECIMAL(4,4),
    foo_date DATE
    )

Then loaded the data:

COPY foo (foo_id, foo_value, foo_date)
FROM 's3://my-bucket/foo.csv'
IAM_ROLE 'arn:aws:iam::123456789012:role/Redshift-Role'
CSV
IGNOREHEADER 1
DATEFORMAT 'MMDDYYYY'

Please note that the recommended way to load data into Amazon Redshift is from files stored in Amazon S3. (I haven't tried using the native psql copy command with Redshift, and would recommend against it — particularly for large data files. You certainly can't mix commands from the Redshift COPY command into the psql Copy command.)

Then, I ran SELECT * FROM foo and it returned:

16  0.3039  2017-12-31
88  0.6461  2017-12-31
94  0.3306  2017-12-31
25  0.5377  2017-12-31

Upvotes: 1

Yann Vernier
Yann Vernier

Reputation: 15887

That is a horrible format for dates. Don't break your date type, convert your data to a saner format. 

=> select to_date('12312017', 'MMDDYYYY');
  to_date
------------
 2017-12-31

Upvotes: 0

Related Questions