Re: Using CTID system column as a "temporary" primary key

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(at)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(at)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(dot)klaver(at)aklaver(dot)com>
Sent: Wednesday, March 29, 2023 4:59 PM
To: Sebastien Flaesch <sebastien(dot)flaesch(at)4js(dot)com>; Kirk Wolak <wolakk(at)gmail(dot)com>
Cc: Geoff Winkless <pgsqladmin(at)geoff(dot)dj>; pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org>
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(dot)klaver(at)aklaver(dot)com