Re: Using CTID system column as a "temporary" primary key - Mailing list pgsql-general
| From | Sebastien Flaesch |
|---|---|
| Subject | Re: Using CTID system column as a "temporary" primary key |
| Date | |
| Msg-id | AM9P191MB1286CE763D59D8B821F69409B0899@AM9P191MB1286.EURP191.PROD.OUTLOOK.COM Whole thread Raw |
| In response to | Re: Using CTID system column as a "temporary" primary key (Adrian Klaver <adrian.klaver@aklaver.com>) |
| Responses |
Re: Using CTID system column as a "temporary" primary key
Re: Using CTID system column as a "temporary" primary key Re: Using CTID system column as a "temporary" primary key Re: Using CTID system column as a "temporary" primary key |
| List | pgsql-general |
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 -
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@aklaver.com>
Sent: Wednesday, March 29, 2023 4:59 PM
To: Sebastien Flaesch <sebastien.flaesch@4js.com>; Kirk Wolak <wolakk@gmail.com>
Cc: Geoff Winkless <pgsqladmin@geoff.dj>; pgsql-general <pgsql-general@lists.postgresql.org>
Subject: Re: Using CTID system column as a "temporary" primary key
Sent: Wednesday, March 29, 2023 4:59 PM
To: Sebastien Flaesch <sebastien.flaesch@4js.com>; Kirk Wolak <wolakk@gmail.com>
Cc: Geoff Winkless <pgsqladmin@geoff.dj>; pgsql-general <pgsql-general@lists.postgresql.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.klaver@aklaver.com
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@aklaver.com
pgsql-general by date: