Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE Log
(
    LogId             serial      not null,
    JobId             integer     not null,
    Time              timestamp   without time zone,
    LogText           text        not null,
    primary key (LogId)
);
create index log_name_idx on Log (JobId);
insert into log values
  (default,1,now(),'logtext1')
returning *;

create table linked_to_log(
  id bigint generated by default as identity primary key,
  logid int references log(logid));
insert into linked_to_log values
  (default,1)
returning *;

CREATE TABLE



CREATE INDEX




logid
jobid
time
logtext


1
1
2024-03-05 09:08:14.784927
logtext1



INSERT 0 1



CREATE TABLE




id
logid


1
1



INSERT 0 1


select table_schema,table_name,column_name,data_type,udt_name,column_default
      ,is_identity,identity_generation,identity_start,identity_increment
      ,identity_maximum,identity_minimum
from information_Schema.columns where table_name in ('log','linked_to_log')
order by table_schema,table_name,ordinal_position;


table_schema
table_name
column_name
data_type
udt_name
column_default
is_identity
identity_generation
identity_start
identity_increment
identity_maximum
identity_minimum


public
linked_to_log
id
bigint
int8
null
YES
BY DEFAULT
1
1
9223372036854775807
1

public
linked_to_log
logid
integer
int4
null
NO
null
null
null
null
null

public
log
logid
integer
int4
nextval('log_logid_seq'::regclass)
NO
null
null
null
null
null

public
log
jobid
integer
int4
null
NO
null
null
null
null
null

public
log
time
timestamp without time zone
timestamp
null
NO
null
null
null
null
null

public
log
logtext
text
text
null
NO
null
null
null
null
null



SELECT 6


alter table log alter column LogId type bigint;
alter table log alter column LogId drop default;
alter table log alter column LogId 
  add generated by default as identity;

select max(logid)+1 id_seq_restart from log;
alter table log alter column LogId 
  restart with 2;

insert into log values
  (default,1,now(),'logtext2')
returning *;

ALTER TABLE



ALTER TABLE



ALTER TABLE




id_seq_restart


2



SELECT 1



ALTER TABLE




logid
jobid
time
logtext


2
1
2024-03-05 09:08:14.820151
logtext2



INSERT 0 1


select table_schema,table_name,column_name,data_type,udt_name,column_default
      ,is_identity,identity_generation,identity_start,identity_increment
      ,identity_maximum,identity_minimum
from information_Schema.columns where table_name in ('log','linked_to_log')
order by table_schema,table_name,ordinal_position;


table_schema
table_name
column_name
data_type
udt_name
column_default
is_identity
identity_generation
identity_start
identity_increment
identity_maximum
identity_minimum


public
linked_to_log
id
bigint
int8
null
YES
BY DEFAULT
1
1
9223372036854775807
1

public
linked_to_log
logid
integer
int4
null
NO
null
null
null
null
null

public
log
logid
bigint
int8
null
YES
BY DEFAULT
1
1
9223372036854775807
1

public
log
jobid
integer
int4
null
NO
null
null
null
null
null

public
log
time
timestamp without time zone
timestamp
null
NO
null
null
null
null
null

public
log
logtext
text
text
null
NO
null
null
null
null
null



SELECT 6


select pg_get_serial_sequence('public.log','logid');
--that's a leftover sequence after the serial type
drop sequence public.log_logid_seq;
--dropping that old sequence doesn't affect the identity column, it has a new one
insert into log values (default,1,now(),'logtext3') returning *;


pg_get_serial_sequence


public.log_logid_seq



SELECT 1



DROP SEQUENCE




logid
jobid
time
logtext


3
1
2024-03-05 09:08:14.831573
logtext3



INSERT 0 1


--after dropping the old serial sequence, 
--the function will show the new sequence responsible for identity
select pg_get_serial_sequence('public.log','logid');
--this one cannot be dropped
drop sequence public.log_logid_seq1;


pg_get_serial_sequence


public.log_logid_seq1



SELECT 1



ERROR:  cannot drop sequence log_logid_seq1 because column logid of table log requires it
HINT:  You can drop column logid of table log instead.


--identity sequences don't show up here
select * from information_schema.sequences;
--but they do show up here
select * from pg_sequences;


sequence_catalog
sequence_schema
sequence_name
data_type
numeric_precision
numeric_precision_radix
numeric_scale
start_value
minimum_value
maximum_value
increment
cycle_option



SELECT 0




schemaname
sequencename
sequenceowner
data_type
start_value
min_value
max_value
increment_by
cycle
cache_size
last_value


public
linked_to_log_id_seq
postgres
bigint
1
1
9223372036854775807
1
f
1
1

public
log_logid_seq1
postgres
bigint
1
1
9223372036854775807
1
f
1
3



SELECT 2


--referencing tables should be updated to use bigint, separately
alter table linked_to_log alter column logid type bigint;

ALTER TABLE


select table_schema,table_name,column_name,data_type,udt_name,column_default
      ,is_identity,identity_generation,identity_start,identity_increment
      ,identity_maximum,identity_minimum
from information_Schema.columns where table_name in ('log','linked_to_log')
order by table_schema,table_name,ordinal_position;


table_schema
table_name
column_name
data_type
udt_name
column_default
is_identity
identity_generation
identity_start
identity_increment
identity_maximum
identity_minimum


public
linked_to_log
id
bigint
int8
null
YES
BY DEFAULT
1
1
9223372036854775807
1

public
linked_to_log
logid
bigint
int8
null
NO
null
null
null
null
null

public
log
logid
bigint
int8
null
YES
BY DEFAULT
1
1
9223372036854775807
1

public
log
jobid
integer
int4
null
NO
null
null
null
null
null

public
log
time
timestamp without time zone
timestamp
null
NO
null
null
null
null
null

public
log
logtext
text
text
null
NO
null
null
null
null
null



SELECT 6

table_schema	table_name	column_name	data_type	udt_name	column_default	is_identity	identity_generation	identity_start	identity_increment	identity_maximum	identity_minimum
public	linked_to_log	id	bigint	int8	null	YES	BY DEFAULT	1	1	9223372036854775807	1
public	linked_to_log	logid	integer	int4	null	NO	null	null	null	null	null
public	log	logid	integer	int4	nextval('log_logid_seq'::regclass)	NO	null	null	null	null	null
public	log	jobid	integer	int4	null	NO	null	null	null	null	null
public	log	time	timestamp without time zone	timestamp	null	NO	null	null	null	null	null
public	log	logtext	text	text	null	NO	null	null	null	null	null

schemaname	sequencename	sequenceowner	data_type	start_value	min_value	max_value	increment_by	cycle	cache_size	last_value
public	linked_to_log_id_seq	postgres	bigint	1	1	9223372036854775807	1	f	1	1
public	log_logid_seq1	postgres	bigint	1	1	9223372036854775807	1	f	1	3