Problem Inserting Postgres
Doug Easterbrook
doug at artsman.com
Mon Jul 11 14:13:30 UTC 2022
hey jim.
I didn’t know that.
I have a method in my insert to remove fields that are other than defaults as I make my on insert.
plus I have an exception routine in the table class (that can be over-ridden) to do any sort of crazy things I want to the variables.
for example, in an update routine, I’d replace an ‘editnum’ field to be auto incrementing as in
update table set editnum = @[$cinst.editnum], dateupdated = @[$cinst.editnum]. ….. etc.
to be replaced by
update table set editnum = editnum+1, dateupdated=now() etc.
just by replacing things like @[$cinst.editnum] with editnum+1
if i do it as a standard practice in the table superclass, then I can be assured its being done
anyway…. just me.
Doug Easterbrook
Arts Management Systems Ltd.
mailto:doug at artsman.com
http://www.artsman.com
Phone (403) 650-1978
> On Jul 10, 2022, at 4:28 PM, Jim Pistrang <jim at jpcr.com> wrote:
>
> Hi Doug,
>
> If you are using the built-in $insert method, you can also use $excludefrominsert to leave the column out of the insert statement.
>
> Jim
>
>> based on the two e3mails (this and the nect one)
>>
>> CUST_ID is part of 'dtCustomers' in the insert statement.
>>
>>> Do tStatObjPGRef.$execdirect(con('INSERT INTO dtcustomers ',
>> $ctask.tSessObjPGRef.$insertnames(iro),' RETURNING cust_id')) Returns #F
>>
>>
>> and it is telling you that cust_id is NULL. (I assume the first column)
>>
>>
>>> ERROR: null value in column "cust_id" of relation "dtcustomers"
>> violates not-null constraint
>>> DETAIL: Failing row contains (null, null, null, null, null, null,
>> null, null, null, null, null, D, null).
>>
>>
>>
>>
>> so, the deal is that you are trying to force insert a NULL into
>> cust_id. which is ok as far as omnis goes. but as far as postgres
>> goes, you have defined its as a serial. being a 'serial', postgres
>> would have created a NOT NULL constraint on the column, since its not
>> very useful to have null primary keys.
>>
>> you can check it in pgadmin by looking at the column definition. ours
>> look like below (we don't use serial as a type directly, but for
>> historical reasons, we build our own as integers. non the less, per
>> an ealrier set of emails to the list on this topic, SERIAL type fields
>> will build something like this
>>
>> ALTER TABLE IF EXISTS public.f_acct_chart
>> ADD COLUMN ca_seq integer NOT NULL DEFAULT
>> nextval('f_acct_chart_ca_seq_key'::regclass);
>>
>>
>> NOTE ... NOT NULL on the field.
>>
>>
>>
>> so, how do you make postgres user the next serial number when inserting
>> a record.
>>
>> One of TWO WAYS
>>
>> OPTION 1: leave cust_id out of the sql statement
>>
>> eg
>>
>> INSERT INTO dtcustomers (cust_firstname,cust_lastname) VALUES
>> ('das','Goravani' ) RETURNING cust_id
>>
>>
>> OR
>>
>> Option 2, use the DEFAULT keyword as the value of the field to tell
>> postgres to use the default value for the field.
>>
>> eg
>>
>> INSERT INTO dtcustomers (cust_id,cust_firstname,cust_lastname) VALUES
>> (DEFAULT, 'das','Goravani' ) RETURNING cust_id
>>
>>
>>
>> Any FIELD not EXPLICITLY SPECIFIED in the insert statement takes default
>> values you have specified in the database. Example, if you have a
>> cust_date_entered and the field default is NOW() in the database, then
>> if you do sometihng like
>>
>>
>> INSERT INTO dtcustomers (cust_firstname,cust_lastname) VALUES
>> ('das','Goravani' ) RETURNING cust_id,cust_date_entered
>>
>> you'll get cust_date_entered returned to you as the current server
>> date .. i.e. now()
>>
>>
>> but, if you include it in the insert statement (and cust_id), it will
>> set the date that you specifiy
>>
>> INSERT INTO dtcustomers (cust_id,cust
>> _firstname,cust_lastname,cust_date_entered)
>> VALUES (DEFAULT,'das','Goravani' , TIMESTAMP '2022-07-07 4:00:00')
>> RETURNING cust_id,cust_date_entered
>>
>>
>> if you tell sql to use DEFAULT for the date, you'll get back now
>>
>> INSERT INTO dtcustomers (cust_id,cust
>> _firstname,cust_lastname,cust_date_entered)
>> VALUES (DEFAULT,'das','Goravani' , DEFAULT) RETURNING
>> cust_id,cust_date_entered
>>
>>
>>
>>
>> the important learning
>>
>> 1) leave out a field from the insert statement if you want the default
>> from the database on insert
>> 2) use the field if you want to set a specific value
>> 3) use the field in the insert statement AND use DEFAULT for its value,
>> if you want to have the database use the default.
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> Doug Easterbrook
>> Arts Management Systems Ltd.
>> mailto:doug at artsman.com
>> http://www.artsman.com
>> Phone (403) 650-1978
>>
>>> On Jul 9, 2022, at 10:07 AM, Das Goravani <goravanis at gmail.com> wrote:
>>>
>>>
>>> Dear List,
>>>
>>> I am on my first postgres INSERT in a new app and new database table.
>>> I am using Postgres
>>> I created the table by drag drop schema onto session
>>> This is my INSERT line:
>>>
>>> Do tStatObjPGRef.$execdirect(con('INSERT INTO dtcustomers ',
>> $ctask.tSessObjPGRef.$insertnames(iro),' RETURNING cust_id')) Returns #F
>>>
>>> This is what it says in Native Error Text:
>>>
>>> ERROR: null value in column "cust_id" of relation "dtcustomers"
>> violates not-null constraint
>>> DETAIL: Failing row contains (null, null, null, null, null, null,
>> null, null, null, null, null, D, null).
>>>
>>> The problem with that error is that cust_id is the primary key and I
>> have excluded it from insert just prior and I check SQLText and indeed
>> it is NOT in the list of fields to INSERT nor in the values of course
>> but I am RETURNING cust_id...
>>>
>>> I am hoping Postgres will auto increment it.
>>>
>>> When you drag drop schema onto session and it is Postgres, the PK
>> column is automatically marked as a SERIAL column.
>>>
>>> By excluding cust_id from the list of fields to insert, I thought it
>> did the default action for the excluded column which is nextval correct?
>>>
>>> So I thought I was doing all the right things.
>>>
>>> This is my SQL and it appears correct to me but it doesn't jive with
>> the error being given above which seems to be saying I AM TRYING TO PASS
>> the cust_id field in my SQL but I'm NOT
>>>
>>> Any ideas?
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> _____________________________________________________________
>>> 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
>
> --
> Jim Pistrang
> JP Computer Resources
> 413-237-5710
> <https://jpcr.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