error: invalid input syntax for type timestamp: "" while inserting data into the table

Question

I am inserting a response from express api into a postgres table. I have created a table already with a column 'createdon' with type timestamp. In response, I am getting a timestamp which I want to insert in that field. This is the script I wrote:

client.query(`INSERT INTO table (col1, col2,createdon, col3) VALUES ($1, $2, $3, $4) RETURNING *`, [val1, val2, val3, val4])

This is working fine until I get a timestamp in createdon field. If I get empty response for createdon field, it's throwing this error.

How can I bypass this error?

Jim Jones · Accepted Answer · 2022-12-20 16:15:50Z

2

If you want to insert a blank timestamp use null instead. With nullif you can test if the value is empty and replace it with null:

INSERT INTO table (col1, col2,createdon, col3) 
VALUES ($1, $2, NULLIF($3,'')::timestamp, $4) 
RETURNING *

Demo: db<>fiddle

edited Dec 20, 2022 at 16:15

answered Dec 19, 2022 at 14:42

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

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

1 Comment

Suraj221b Over a year ago

Thanks, assigning timestamp to nullif worked. :). NULLIF($3,'')::timestamp

Collectives™ on Stack Overflow

error: invalid input syntax for type timestamp: "" while inserting data into the table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related