Postgres default value with Insert/Update

Using Postgres, I have a table with a varchar column ALLOW NULL with a default value of ‘’ (empty string).

I have a window with TextFields that I cycle through, assigning the text from each field to the Postgres column:

row.Column(fldname) = mytextfield.text

Then I save the record with either:

myDbase.InsertRecord(sTable, row)

or

rs.Edit
rs.Field(fldname).StringValue = mytextfield.text
rs.Update

However, if mytextfield.text = “”, then Postgres defaults the field to NULL.

If I set the column to NOT NULL, then I get an error that trying to put a NULL value into a column that has a NOT NULL constraint.

Is there a way to force this column to have an empty string and not a NULL value?

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 did not know that. Thanks.

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.

Kem,

How exactly is this done?

rs.field(“myfield”).value = Default //does not work
rs.field(“myfield”).value = “Default” //does not work

Thanks for your help.

dim sql as string = "INSERT INTO my_table (my_int, my_field) VALUES ($1, DEFAULT)"
db.Prepare( sql ).SQLExecute( 1 )

For example.

Thanks.