CODEWITHSUNDEEP
sqlpostgresql
Masoud
Masoud

Reputation: 118

manipulate data of a column

I have a CSV file containing about 30m rows with a column that has a date type. But the problem is with its format. PSQL supports '-' delimiters for timestamp but my dates are using '/'. For example, the date should be '2021-02-01 00:00:00' but my date format is '2021/02/01 00:00:00'. Also, I can not open the CSV file and change it manually due to its large size. I am trying to import my data into a temporary table to replace the '/' with '-' and then inserting them to a new table and I am using the following command(it is not the real table and it is just an example):

CREATE TABLE TMP(
    dt VARCHAR 
)

CREATE TABLE other_tmp(
    dt TIMESTAMP
)

INSERT INTO TMP VALUES('2020/01/02 22:33:11');

INSERT INTO other_tmp(dt)
SELECT dt, 
REPLACE(dt, '/', '-')
FROM TMP

I get an error with replace function when I want to run it. Does anybody know that how can I solve this problem? Or even is it possible to manipulate the column in the original table?

Upvotes: 0

Views: 90

Answers (2)

user330315
user330315

Reputation:

Your first error is that you have two columns in the SELECT list, but only one column in the INSERT target.

To convert a string to a timestamp, use to_timstamp()

insert into other_tmp(dt)
select to_timestamp(dt, 'yyyy/mm/dd hh24:mi:ss')
from tmp;

Online example

Upvotes: 1

0xdw
0xdw

Reputation: 3842

Try this, In this case, your column "dt" is of type timestamp without time zone but the expression is of type text. So the text should cast to timestamp as below.

CREATE TABLE IF NOT EXISTS TMP
(
    dt VARCHAR
);

CREATE TABLE IF NOT EXISTS other_tmp
(
    dt TIMESTAMP
);

INSERT INTO TMP
VALUES ('2020/01/02 22:33:11'::TIMESTAMP);

INSERT INTO other_tmp(dt)
SELECT REPLACE(dt, '/', '-')::TIMESTAMP AS dt
FROM TMP;

Upvotes: 1

Related Questions