How unique PK value in Postgres?

Das Goravani goravanis at gmail.com
Tue Feb 1 22:05:12 UTC 2022


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 



More information about the omnisdev-en mailing list