|
Oh the use of default keyword is new to me, thanks for that.
But to make PostgreSQL more Informix-compatible, zero should have been considered as well.
Informix:
sf@toro:/tmp$ dbaccess test1 - Database selected.
> create table mytable ( pkey serial not null primary key, name varchar(50) );
Table created.
> insert into mytable values ( 0, 'aaaaa' );
1 row(s) inserted.
> select * from mytable;
pkey name
1 aaaaa
1 row(s) retrieved.
PostgreSQL:
sf@toro:/tmp$ psql test1 --host=localhost --port=5436 --user=pgsuser
psql (14.1)
Type "help" for help.
test1=> create table mytable ( pkey serial not null primary key, name varchar(50) );
CREATE TABLE
test1=> insert into mytable values ( 0, 'aaaaa' );
INSERT 0 1
test1=> select * from mytable;
pkey | name
------+-------
0 | aaaaa
(1 row)
So, I would rather say : no, using zero was not considered.
😉
Seb
From: Adrian Klaver <adrian.klaver@xxxxxxxxxxx>
Sent: Wednesday, March 29, 2023 4:59 PM To: Sebastien Flaesch <sebastien.flaesch@xxxxxxx>; Kirk Wolak <wolakk@xxxxxxxxx> Cc: Geoff Winkless <pgsqladmin@xxxxxxxx>; pgsql-general <pgsql-general@xxxxxxxxxxxxxxxxxxxx> Subject: Re: Using CTID system column as a "temporary" primary key EXTERNAL: Do not click links or open attachments if you do not recognize the sender.
On 3/29/23 07:19, Sebastien Flaesch wrote: > Hello Kirk, > > INSERT statements must not use the serial column, so you have to list > all columns of the table and provide only the values of the non-serial > columns. With Informix you could just specific a zero to get a new > generated serial, but seems this has never been considered with PostgreSQL. Yes it has: \d seq_test Table "public.seq_test" Column | Type | Collation | Nullable | Default --------+-------------------+-----------+----------+-------------------------------------- id | integer | | not null | nextval('seq_test_id_seq'::regclass) fld_1 | character varying | | | Indexes: "seq_test_pkey" PRIMARY KEY, btree (id) insert into seq_test values(default, 'test'); select * from seq_test; id | fld_1 ----+------- 1 | test > > SELECT * FROM table will return all column, user-defined ROWID included... > This is not the case with Informix or Oracle ROWID columns. > So, either you specify all columns except user-def ROWID or you add the > rowid field to the program variable structure that receives the row. > > ... > > Seb > ------------------------------------------------------------------------ -- Adrian Klaver adrian.klaver@xxxxxxxxxxx |
