Postgres 9.3

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 *;


logid
jobid
time
logtext


1
1
2024-03-05 11:50:36.506363
logtext1



INSERT 0 1


create table linked_to_log(
  id bigserial primary key,
  logid int references log(logid));
insert into linked_to_log values
  (default,1)
returning *;


id
logid


1
1



INSERT 0 1


--the sequences are already bigint here
select * from information_schema.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


postgres
public
log_logid_seq
bigint
64
2
0
1
1
9223372036854775807
1
NO

postgres
public
linked_to_log_id_seq
bigint
64
2
0
1
1
9223372036854775807
1
NO



SELECT 2


--all you need to do
alter table log alter column LogId type bigint;
select setval(pg_get_serial_sequence('public.log','logid'),
              (select max(LogId)+1 from Log));


setval


2



SELECT 1


--setting the id sequence near int limit to test
select setval(pg_get_serial_sequence('public.log','logid'),2147483644);


setval


2147483644



SELECT 1


--the underlying sequence just continues to generate values
--but now the table can accept stuff above max_integer
insert into log values
  (default,1,now(),'2 less than int limit'),
  (default,1,now(),'1 less than int limit'),
  (default,1,now(),'2147483647, int limit'),
  (default,1,now(),'+1 over int limit'),
  (default,1,now(),'+2 over int limit'),
  (9223372036854775807,1,now(),'bigint limit, your new ceiling')
returning *;


logid
jobid
time
logtext


2147483645
1
2024-03-05 11:50:36.562488
2 less than int limit

2147483646
1
2024-03-05 11:50:36.562488
1 less than int limit

2147483647
1
2024-03-05 11:50:36.562488
2147483647, int limit

2147483648
1
2024-03-05 11:50:36.562488
+1 over int limit

2147483649
1
2024-03-05 11:50:36.562488
+2 over int limit

9223372036854775807
1
2024-03-05 11:50:36.562488
bigint limit, your new ceiling



INSERT 0 6


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
nextval('linked_to_log_id_seq'::regclass)
NO
null
null
null
null
null

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

public
log
logid
bigint
int8
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


select * from information_schema.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


postgres
public
log_logid_seq
bigint
64
2
0
1
1
9223372036854775807
1
NO

postgres
public
linked_to_log_id_seq
bigint
64
2
0
1
1
9223372036854775807
1
NO



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
nextval('linked_to_log_id_seq'::regclass)
NO
null
null
null
null
null

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

public
log
logid
bigint
int8
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

sequence_catalog	sequence_schema	sequence_name	data_type	numeric_precision	numeric_precision_radix	numeric_scale	start_value	minimum_value	maximum_value	increment	cycle_option
postgres	public	log_logid_seq	bigint	64	2	0	1	1	9223372036854775807	1	NO
postgres	public	linked_to_log_id_seq	bigint	64	2	0	1	1	9223372036854775807	1	NO

logid	jobid	time	logtext
2147483645	1	2024-03-05 11:50:36.562488	2 less than int limit
2147483646	1	2024-03-05 11:50:36.562488	1 less than int limit
2147483647	1	2024-03-05 11:50:36.562488	2147483647, int limit
2147483648	1	2024-03-05 11:50:36.562488	+1 over int limit
2147483649	1	2024-03-05 11:50:36.562488	+2 over int limit
9223372036854775807	1	2024-03-05 11:50:36.562488	bigint limit, your new ceiling

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	nextval('linked_to_log_id_seq'::regclass)	NO	null	null	null	null	null
public	linked_to_log	logid	integer	int4	null	NO	null	null	null	null	null
public	log	logid	bigint	int8	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