If the column has no value, do not assign it in either case and it should use the default. Alternatively, you can use a Prepared Statement for the insert. (Postgres accepts DEFAULT as a value, in case you didn’t know.)
I haven’t seen this problem. But then, as Ken suggests, I always use prepared statements to insert/update. And i use a class to do this, which always includes every column in the statement. One benefit/side-effect of this is my “allow null” columns (with no default) always get set to empty strings when no value was passed.
We use an in-house ORM that is smart about this, i.e., it tracks the values between load and update and only updates the columns that have actually changed.
that is one of the benefits of an ORM. I use a modified version of @Bob Keeney’s ActiveRecord myself. Ever since I moved to AR, I have stopped having issues with INSERTs, UPDATEs, DELETEs, etc.
The drawback to ORMs is it adds another layer between your code and the database which if you are doing lots of INSERTs/UPDATEs in a row (like a ETL load) then it will be a little (or a lot depending on your ORM) slower. Depending how your ORM works, you can minimize the ETL load slowness.
Now I am not suggesting you scrap all SQL code and replace it with an ORM. just might be something you think about before your next application.