Converting DATE columns in Postgres

Question

I have the following text file aatest.txt:

09/25/2019 | 1234.5
10/01/2018 | 6789.0

that would like to convert into zztext.txt:

2019-09-25 | 1234.5
2018-10-01 | 6789.0

My Postgres script is:

CREATE TABLE documents (tdate TEXT, val NUMERIC);
COPY documents FROM 'aatest.txt' WITH CSV DELIMITER '|';
SELECT TO_DATE(tdate, 'mm/dd/yyyy');
COPY documents  TO 'zztest.txt' WITH CSV DELIMITER '|';

However I am getting the following error message: ERROR: column "tdate" does not exist What am I doing wrong? Thank you!

user330315 · Accepted Answer · 2019-10-06 10:26:16Z

1

Your SELECT has no FROM clause, so you can't reference any columns. But you need to put that SELECT into the COPY statement anyways:

CREATE TABLE documents (tdate TEXT, val NUMERIC);
COPY documents FROM 'aatest.txt' WITH CSV DELIMITER '|';
COPY (select to_char(TO_DATE(tdate, 'mm/dd/yyyy'), 'yyyy-mm-dd'), val FROM documents) 
   TO 'zztest.txt' WITH CSV DELIMITER '|';

answered Oct 6, 2019 at 10:26

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Converting DATE columns in Postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related