Invalid input syntax for type date: ""

💬 App Building
1

I have a simple form for inserting some data. I have a date field where I am getting an error during the insert. I can't seem to figure out why because when I preview the query result and if I hover over the query field parameters it displays the proper date format with the data. I'm using unicode format yyyy-MM-dd. The update works just fine also using the same form with the same field. It just insert that has an issue and it's not clear to me why.

The date is not null that I am inserting. Update works, insert does not. I manually inserted through pgadmin to see and it works just fine. Any ideas or recommendations?

invalid input syntax for type date: ""

1 Like
2

Hey @hexx,

After the error pops up, right click on the query and select "View in State". You should see a bunch of data, and there should be one with the body of the request, would you mind pasting that there (removing any sensitive data)?

That may help us debug.

3

It sounds like a Date object is getting implicitly converted to a string to me.

Since Retool has moment built in you could try wrapping your date with moment and explicitly cast it to an ISO string?

const formattedDate = moment({{ someComponent.value }}).toISOString();

OR

it sounds like the date field is empty and getting send as "" instead of null.
For that issue, I like using lodash (which is also provided in Retool).

const safeNulledDate = _.defaultTo({{ shouldBeDate.value }}, null);
4

The error seem pretty clear that it is getting an empty value on insert and that is not the case. I am selecting the date and format is the same for the table which I performed the exact same insert SQL statement as the app within pgadmin and it works just fine. Is it something with the date component? I even replaced the component in case there was some sort of bug with it.

insert into "metals" ("asset", "asset_type", "location", "purchase_date", "purchase_price", "purity", "quantity", "service_provider", "spot_price", "tag", "weight") values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11) returning * - invalid input syntax for type date: ""

The date in the field being inserted is 2025-03-07. I confirmed the form data key matches the column as well.

5

I figured out the issue. It was a combination of a few things. I had recreated the form so I needed to update the key values for insert. The other issue was I had a mapped value on the date field within the table that needed to be removed. It's all good now.

2 Likes
6

Ok....I am back :grin:

On my other app it is the exact scenario where I have a date field but this time I may not need to select a date. I am getting the same message. What is the best approach when inserting data and you have a date field that is null because you don't need to set it for that insert?

7

OK. I figured out a solution. I had to modify the insert query key value pair for the date field.

I modified it to be like this and it worked.
"my_date_field": {{ my_date_field.value ? my_date_field.value : null }}

1 Like
8

That's almost exactly what I was going to recommend, @hexx! :+1:

9

Cool. Now one other thing. What do I need to do to fix this for inline edits? My query for inline edits I use "Bulk update via primary key" and use this array {{ table1.changesetArray }}. How do I need to modify this for my date fields where it could be empty for insert?

10

My goto is to have a transformer that feeds the bulk update. In the transformer you can do something like this....

return _.map({{ table1.changesetArray }}, row => {
    return {
        ...row,
       date: row.date ?? null
    }
});
1 Like
11

@khill-fbmc Cool. I will give that a try. Thx!