Adding a string that is a number with a leading zero

Okay, I’m running into the bug described at [Bug] In prepared statements, it is impossible to use numeric values with leading zero as strings · Issue #4056 · appsmithorg/appsmith · GitHub

I’m trying to run an insert query, with the iAddValue.text being the string “0447” (for example). When it gets inserted into the database, the value “447” gets inserted. This only happens if I use prepared statements, not if I turn prepared statements off. It’s identical to the issue I listed above, but that issue is marked as fixed. So I’m not sure if I need to change my query, or if it isn’t really fixed. Here’s the query I’m using (though I’ve tried several different iterations of it):

insert into keysandvalues(name, key, value) values ({{}},{{iAddKey.text}}, {{iAddValue.text}}::varchar);

Hi Anthony,

This change of typecasting a value in Prepared Statements has only been made for Postgres database today. What database are you using? I could raise an issue and this can be tackled for the others as well.

I’m using postgres. I’ll check it out again later today.

Until it is fixed, I’ve found that turning off prepared statements and dollar-quoting with a long random string seems to be the workaround.

Thanks! This fix went out to on prem deployments 6 days ago. It would be great if you can check it out and let me know if you are still facing this.

Yes, it works now.

A note about the benefit of adding typecasting at Postgres - Appsmith would probably save people some time debugging this relatively rare issue.

I guess best practice will be to explicitly typecast everything?

Use the following two functions to solve your problem