Re: Using CTID system column as a "temporary" primary key - Mailing list pgsql-general

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