Omnis Postgres No nulls puzzle

Mike Rowan michael.rowan3 at gmail.com
Wed Jun 15 01:47:27 UTC 2022


Dear Doug

I take it you are referring to $prepareinsert.  I wonder if you have a
standard paragraph somewhere that describes its use? Of course, I see it
requires several parameters but I'm afraid I'm very bad at interpreting how
to use them.
Perhaps just one line of actual code?

Mike

On Tue, Jun 14, 2022 at 1:25 AM Doug Easterbrook via omnisdev-en <
omnisdev-en at lists.omnis-dev.com> wrote:

> hi Mike
>
> a few things to add
>
> - as dan says, if you put the field in your insert statement, it will NOT
> default to the value, even if the field is null
> - if you have a null value and you include the field in the insert
> statement, then change the insert in your table class to be something like
>
> (assume field2 is null)
>
> insert into table1 (field1,field2) values (@[field1], DEFAULT)
>
> in other words, if you want to enforce defaults for null or zero values,
> then explicitly substitute DEFAULT for the bind variables usint a $replace,
> perhaps in a loop)
>
> Note, since you can change the bind variable in the statement you can also
> include functions like now() for dates and set up sequences to use DEFAULT
> as well.
>
>
> or, you can remove the fields that are zero / null from the insert
> statement and let them take the DEFAULT
>
>
>
>
> DEFAULT is only for insert,   it doesn’t apply to update statements (as
> far as I know - since we remove fields on UPDATE statement as well)
>
>
>
> just my thoughts.
>
>
> finally, we use TMOBJS to set text and number fields to zero and empty as
> paart of our table class.  there is a function to do that which is useful.
> if you use TMOBJS and have over ridden your $insert and $update in the
> rtable class… which is specifically designed to
> - set dates to null (of not specified),
> - numbers to zero (if null)
> - text to empty (if null)
> and fo it fast.
>
>
>
>
>
>
> Doug Easterbrook
> Arts Management Systems Ltd.
> mailto:doug at artsman.com
> http://www.artsman.com
> Phone (403) 650-1978
>
> > On Jun 12, 2022, at 3:43 PM, Mike Rowan <michael.rowan3 at gmail.com>
> wrote:
> >
> > Morning all,
> > An example of a column definition in one of my tables in PG 14 .
> >
> > prename text COLLATE pg_catalog."default" NOT NULL DEFAULT ''::text,
> > (thats two single quotes after DEFAULT)
> >
> > I am no expert with PostgreSQL but this says to me :
> > "When this table is inserted or updated, check that this column is NOT
> NULL
> > and, if it is, set it to EMPTY."
> > Such inserts or updates are rejected due to null value in that column
> >
> > Can someone on this friendly Omnis list please tell me how I am wrong?
> > Thanks as always.
> > _____________________________________________________________
> > Manage your list subscriptions at https://lists.omnis-dev.com
> > Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com
>
> _____________________________________________________________
> Manage your list subscriptions at https://lists.omnis-dev.com
> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com
>


More information about the omnisdev-en mailing list