How unique PK value in Postgres?

Alex Clay aclay at mac.com
Wed Feb 2 14:15:02 UTC 2022


Hi Das,

Yes, after executing an insert or update we then run a fetch to pull the results back into the row or list containing the data. This means we can do something like:

> your_row_or_list.$insert() Returns liFetchResult


After a successful execution your_row_or_list will have the primary key column populated. Always check the return code to handle errors appropriately. :)

The order matters because there is no guarantee the database column order matches your schema class column order. Using SELECT * or RETURNING * is a gamble. Instead, try something like "RETURNING [your_row_or_list.$selectnames()]" in your SQL. Omnis will build the select names to match the definition of the row or list and quality them with the database schema and table (whatever your $servertablename is on the schema class).

Alex

> On Feb 1, 2022, at 20:17, Das Goravani <goravanis at gmail.com> wrote:
> 
> 
> 
>> On Feb 1, 2022, at 5:31 PM, Alex Clay via omnisdev-en <omnisdev-en at lists.omnis-dev.com <mailto:omnisdev-en at lists.omnis-dev.com>> wrote:
>> 
>> Hi Das,
>> 
>> Just omit the column from the insert and PostgreSQL will automatically assign the next ID. Yes, setting $cols.[your column].$excludefrominsert to kTrue will accomplish this.
>> 
>> This works for other columns with default values or columns with values assigned by triggers.
>> 
>> Before you go much further I recommend you hook up a RETURNING * clause to your insert and update calls (or RETURNING col1, col2, ..., colN to control order). This allows you to fetch back the results from an insert or update and get any automatically-assigned values in a single roundtrip to the server.
> 
> Dear Alex,
> I understood your above post and thank you, but one question, what do you mean by "to control order" in your parentheses about the RETURNING clause.  By "col1" and "col2" you actually mean names go there, column names? I think so. I just don’t get "to control order". 
> 
> You could use the clause to just ask for the PK after it auto assigns a value during an insert. I think I saw Doug type that about getting back the PK
> 
> You tend to have the rest in memory already, except minus any auto assigned columns- Yeah there’s that. 
> 
> Otherwise I get the "single trip to the server" savings
> 
> What I don’t get is where do you receive what it returns.. are the values just automatically in those named fields in the row you used
> 
> I doubt it, and so I don’t know where you receive, how you receive, those returned values
> 
> Do you do a fetchinto some list or row?
> 
> Thanks
> 
> Das
> 
> 
>> 
>> Alex
>> 
>>> On Feb 1, 2022, at 17:05, Das Goravani <goravanis at gmail.com> wrote:
>>> 
>>> 
>>> Thank you Alan, Alex and Doug. I now have enough to use the docs for anything more. I wouldn’t have believed this if I just read it in the docs.. I would think I’m having an existential moment. I can’t believe it, what you have to do to get this.. 
>>> 
>>> Well the following comes to mind additionally:
>>> 
>>> So if one is using $insert on a row that is defined from a table or schema (same thing), it naturally includes the primary key field. So if I understand right, once you set this up, you have to set the value of the PK column to "DEFAULT" in order for it to generate the nextval?
>>> 
>>> Or do you use $excludefrominserts and by excluding it does it do the default behavior of nextval?
>>> 
>>> Because most of my work is done with $insert and $update on rows defined from tables or schemas (I use table classes) and the PK column is naturally included there. Currently in SQLite in my app I have centralized insert and update code.. in the insert method I pack the PK column with NULL which SQLite needs in order to pack PK with the nextval and return it. Would I just replace NULL with "DEFAULT"..?
>>> 
>>> 
>>> 
>>>> On Feb 1, 2022, at 4:55 PM, Doug Easterbrook via omnisdev-en <omnisdev-en at lists.omnis-dev.com> wrote:
>>>> 
>>>> hi allan
>>>> 
>>>> you are most definitely right ..  it is a short cut — however, these days, I’d suggest using BIGSERIAL though to get 64 bit foreign keys
>>>> 
>>>> CREATE TABLE COMPANY
>>>> ID  BIGSERIAL PRIMARY KEY,
>>>> NAME           TEXT      NOT NULL
>>>> );
>>>> 
>>>> 
>>>> 
>>>> and for das.  its all explained here with good examples
>>>> 
>>>> 
>>>> https://www.postgresqltutorial.com/postgresql-serial/
>>>> 
>>>> 
>>>> 
>>>> 
>>>> 
>>>> 
>>>> Doug Easterbrook
>>>> Arts Management Systems Ltd.
>>>> mailto:doug at artsman.com
>>>> http://www.artsman.com
>>>> Phone (403) 650-1978
>>>> 
>>>>> On February 1, 2022, at 1:47 PM, Alan Davey <david.a.davey at gmail.com> wrote:
>>>>> 
>>>>> You can declare the column data type as "serial" and avoid having to create
>>>>> the generator.  You should still add the PK constraint syntax:
>>>>> 
>>>>> CREATE TABLE COMPANY(
>>>>> ID  SERIAL PRIMARY KEY,
>>>>> NAME           TEXT      NOT NULL
>>>>> );
>>>>> 
>>>>> 
>>>>> On Tue, Feb 1, 2022 at 4:35 PM Das Goravani <goravanis at gmail.com> wrote:
>>>>> 
>>>>>> 
>>>>>> Man that’s complicated, and you have to do it for every table too.. wow..
>>>>>> I wish it would default to this behavior.
>>>>>> 
>>>>>> I kind of can’t believe it..
>>>>>> 
>>>>>> Oh well I guess if you want power you then have to deal with it.
>>>>>> 
>>>>>> Kind of makes you "have to have" an Omnis window where you have table
>>>>>> creation set up.. a window where you enter the name of your table and your
>>>>>> columns, and then you have code which creates the table, creates the
>>>>>> columns, and then does this procedure to add the PK behavior…
>>>>>> 
>>>>>> I’m used to dragging schemas onto "Tables" in the SQL Browser to create
>>>>>> tables.. and I’m used to PK operating automatically.
>>>>>> 
>>>>>> SQLIte spoils you in this way.
>>>>>> 
>>>>>> What about indexes? Are they created in the same way as SQLite? You create
>>>>>> them with a single line of SQL.
>>>>>> 
>>>>>> Just "wow".
>>>>>> 
>>>>>> 
>>>>>> 
>>>>>>> On Feb 1, 2022, at 4:16 PM, Doug Easterbrook via omnisdev-en <
>>>>>> omnisdev-en at lists.omnis-dev.com> wrote:
>>>>>>> 
>>>>>>> you have to create a sequence key generator for each primay key you want.
>>>>>>> 
>>>>>>> 
>>>>>>> imagine a table called F_DEFAULT
>>>>>>> and a primary key of D_KEY
>>>>>>> 
>>>>>>> 
>>>>>>> 
>>>>>>> 
>>>>>>> you create a sequence generator.  our naming convention is table_key
>>>>>> or.    ‘f_default_d_seq_key'
>>>>>>> 
>>>>>>> CREATE SEQUENCE IF NOT EXISTS f_default_d_seq_key
>>>>>>> INCREMENT 1
>>>>>>> START 1
>>>>>>> MINVALUE 1
>>>>>>> MAXVALUE 9223372036854775807
>>>>>>> CACHE 1;
>>>>>>> 
>>>>>>> 
>>>>>>> 
>>>>>>> 
>>>>>>> then you give the database column a default of ’nextval’ that references
>>>>>> the unique key generator.
>>>>>>> 
>>>>>>> 
>>>>>>> ALTER TABLE IF EXISTS f_default
>>>>>>> ADD COLUMN d_seq integer NOT NULL DEFAULT
>>>>>> nextval('f_default_d_seq_key'::regclass);
>>>>>>> 
>>>>>>> 
>>>>>>> 
>>>>>>> 
>>>>>>> 
>>>>>>> 
>>>>>>> 
>>>>>>> 
>>>>>>> Doug Easterbrook
>>>>>>> Arts Management Systems Ltd.
>>>>>>> mailto:doug at artsman.com
>>>>>>> http://www.artsman.com
>>>>>>> Phone (403) 650-1978
>>>>>>> 
>>>>>>>> On February 1, 2022, at 12:45 PM, Das Goravani <goravanis at gmail.com>
>>>>>> wrote:
>>>>>>>> 
>>>>>>>> 
>>>>>>>> Hello,
>>>>>>>> 
>>>>>>>> When inserting, how do you get Postgres to assign a new, unique, value
>>>>>> in the PK column?
>>>>>>>> 
>>>>>>>> In SQLite you just pass a #NULL in that column and SQLite assigns a
>>>>>> value.
>>>>>>>> 
>>>>>>>> I was importing data into postgres very recently and no matter what I
>>>>>> put in the PK column it would not accept it as cause for assigning a value.
>>>>>> I tried 0, and NULL and of course a value. It used the values I passed..
>>>>>> they were from SQLite and they were in order, unique, so it worked out OK.
>>>>>>>> 
>>>>>>>> But in general new work when inserting how do you get it to assign the
>>>>>> value?
>>>>>>>> 
>>>>>>>> Do you use $excludefrominserts ?  That didn’t seem to work on a
>>>>>> Smartlist with $doinserts(). I tried it, it came up with errors for the PK
>>>>>> field anyway, even though I excluded it.
>>>>>>>> 
>>>>>>>> ???
>>>>>>>> 
>>>>>>>> Thanks
>>>>>>>> 
>>>>>>>> Das Goravani
>>>>>>>> 
>>>>>>>> 
>>>>>>>> _____________________________________________________________
>>>>>>>> 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
>>>>>> 
>>>>>> _____________________________________________________________
>>>>>> 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 
>>>> 
>>>> _____________________________________________________________
>>>> 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 
>> 
>> _____________________________________________________________
>> Manage your list subscriptions at https://lists.omnis-dev.com <https://lists.omnis-dev.com/>
>> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com <mailto:omnisdev-en at lists.omnis-dev.com> 
> 
> _____________________________________________________________
> Manage your list subscriptions at https://lists.omnis-dev.com <https://lists.omnis-dev.com/>
> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com <mailto:omnisdev-en at lists.omnis-dev.com>


More information about the omnisdev-en mailing list