I'm scraping an excel sheet and pushing it to the postgresql database. While pushing, the script is assigning the 'text' datatype to all the columns. There are some datetime fields in the table, so I have to alter them in the date format. The query I wrote for the same is like this:

alter table table_name 
   alter column column_name type DATE 
   using to_timestamp(column_name, 'DD/MM/YYYY HH24:MI:SS');

The problem here is, in the excel sheet, there are some empty rows. When I fire this query, it assigns a default timestamp to the empty rows. How can I skip the empty rows while altering the table?

Thanks in advance.

CodePudding user response：

a select like this results in

select to_timestamp('', 'DD/MM/YYYY HH24:MI:SS');

fiddle

to skip empty values (not null) use a nullif in your using

create table test (
  test varchar
  );

insert into test values (''), (null) ,('10/10/2022')

select * from test

alter table test 
   alter column test type DATE 
   using to_timestamp(nullif(test,''), 'DD/MM/YYYY HH24:MI:SS');

select * from test