How unique PK value in Postgres?

Doug Easterbrook doug at artsman.com
Tue Feb 1 21:55:16 UTC 2022


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 



More information about the omnisdev-en mailing list