CODEWITHSUNDEEP
copycommandamazon-redshift
Raam
Raam

Reputation: 49

Redshift:COPY command against NOT NULL column with DEFAULT value

I have a scenario in my redShift database, where my table has NOT NULL date column with default as SYSDATE. I am trying to load the data through COPY command without the date column in the csv file. But, when I load, the COPY command throws out an error. Please let me know do we have any other option to overcome this issue.

Upvotes: 2

Views: 3689

Answers (1)

Red Boy
Red Boy

Reputation: 5729

Since you have not provided exact error you are getting, I will try to navigate you through with simple use case. Then, I think your could try replicating it.

If you just skip the column with default from your copy command should work and resolve your issue, one more thing, if you are doing Copy via code(java/python etc) make sure you are committing the transaction.

Use following steps to make a simple working program.

create table sales(
salesid integer not null,
category varchar(10),
update_at timestamp DEFAULT SYSDATE);

Create a file Test.csv with following content.

1,Cat1
2,Cat2
3,Cat3
4,Cat1
5,Cat1
6,Cat3
7,Cat1
10,Cat1

Run the following command.

Copy sales(salesid,category) from 's3://***/Test.csv' credentials'aws_access_key_id=*******;aws_secret_access_key=********' delimiter ',';

Output: Copy sales(salesid,category) from 's3:////Test.csv' credentials'aws_access_key_id=*****;aws_secret_access_key=****' delimiter ','; INFO: Load into table 'sales' completed, 8 record(s) loaded successfully. COPY;

$Select * from Sales;

 salesid | category |         update_at
---------+----------+----------------------------
       4 | Cat1     | 2018-08-14 05:56:33.644541
      10 | Cat1     | 2018-08-14 05:56:33.644541
       2 | Cat2     | 2018-08-14 05:56:33.644541
       6 | Cat3     | 2018-08-14 05:56:33.644541
       3 | Cat3     | 2018-08-14 05:56:33.644541
       7 | Cat1     | 2018-08-14 05:56:33.644541
       1 | Cat1     | 2018-08-14 05:56:33.644541
       5 | Cat1     | 2018-08-14 05:56:33.644541
(8 rows)

Upvotes: 2

Related Questions