add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
--https://stackoverflow.com/q/77008733/5298879
--bigserial wouldn't be a problem for that alter
create table test(id bigserial,a_id bigserial);
select * from information_schema.columns where table_name='test';
insert into test values
(default,default),
(default,default),
(default,default),
(default,default),
(default,234523452345)
returning *;
CREATE TABLE
table_catalog table_schema table_name column_name ordinal_position column_default is_nullable data_type character_maximum_length character_octet_length numeric_precision numeric_precision_radix numeric_scale datetime_precision interval_type interval_precision character_set_catalog character_set_schema character_set_name collation_catalog collation_schema collation_name domain_catalog domain_schema domain_name udt_catalog udt_schema udt_name scope_catalog scope_schema scope_name maximum_cardinality dtd_identifier is_self_referencing is_identity identity_generation identity_start identity_increment identity_maximum identity_minimum identity_cycle is_generated generation_expression is_updatable
db_738274767 public test id 1 nextval('test_id_seq'::regclass) NO bigint null null 64 2 0 null null null null null null null null null null null null db_738274767 pg_catalog int8 null null null null 1 NO NO null null null null null NO NEVER null YES
db_738274767 public test a_id 2 nextval('test_a_id_seq'::regclass) NO bigint null null 64 2 0 null null null null null null null null null null null null db_738274767 pg_catalog int8 null null null null 2 NO NO null null null null null NO NEVER null YES 
SELECT 2
id a_id
1 1
2 2
3 3
4 4
5 234523452345 
INSERT 0 5
--works just fine with a regular bigserial bigint
alter table test alter column a_id type text using (a_id::text);
ALTER TABLE
select * from information_schema.columns where table_name='test';
select * from test;
table_catalog table_schema table_name column_name ordinal_position column_default is_nullable data_type character_maximum_length character_octet_length numeric_precision numeric_precision_radix numeric_scale datetime_precision interval_type interval_precision character_set_catalog character_set_schema character_set_name collation_catalog collation_schema collation_name domain_catalog domain_schema domain_name udt_catalog udt_schema udt_name scope_catalog scope_schema scope_name maximum_cardinality dtd_identifier is_self_referencing is_identity identity_generation identity_start identity_increment identity_maximum identity_minimum identity_cycle is_generated generation_expression is_updatable
db_738274767 public test id 1 nextval('test_id_seq'::regclass) NO bigint null null 64 2 0 null null null null null null null null null null null null db_738274767 pg_catalog int8 null null null null 1 NO NO null null null null null NO NEVER null YES
db_738274767 public test a_id 2 nextval('test_a_id_seq'::regclass) NO text null 1073741824 null null null null null null null null null null null null null null null db_738274767 pg_catalog text null null null null 2 NO NO null null null null null NO NEVER null YES 
SELECT 2
id a_id
1 1
2 2
3 3
4 4
5 234523452345 
SELECT 5
--identity column would allow OP to insert their data for some time
--before figuring out the accident
create table a_table(id bigserial,a_id bigint generated by default as identity);
select * from information_schema.columns where table_name='a_table';

insert into a_table values
(default,default),
(default,default),
(default,default),
(default,default),
(default,234523452345)
returning *;

CREATE TABLE
table_catalog table_schema table_name column_name ordinal_position column_default is_nullable data_type character_maximum_length character_octet_length numeric_precision numeric_precision_radix numeric_scale datetime_precision interval_type interval_precision character_set_catalog character_set_schema character_set_name collation_catalog collation_schema collation_name domain_catalog domain_schema domain_name udt_catalog udt_schema udt_name scope_catalog scope_schema scope_name maximum_cardinality dtd_identifier is_self_referencing is_identity identity_generation identity_start identity_increment identity_maximum identity_minimum identity_cycle is_generated generation_expression is_updatable
db_738274767 public a_table id 1 nextval('a_table_id_seq'::regclass) NO bigint null null 64 2 0 null null null null null null null null null null null null db_738274767 pg_catalog int8 null null null null 1 NO NO null null null null null NO NEVER null YES
db_738274767 public a_table a_id 2 null NO bigint null null 64 2 0 null null null null null null null null null null null null db_738274767 pg_catalog int8 null null null null 2 NO YES BY DEFAULT 1 1 9223372036854775807 1 NO NEVER null YES 
SELECT 2
id a_id
1 1
2 2
3 3
4 4
5 234523452345 
INSERT 0 5
--matching error
alter table a_table alter column a_id type text using (a_id::text);
ERROR:  identity column type must be smallint, integer, or bigint
alter table a_table alter column a_id drop identity;
ALTER TABLE a_table ALTER COLUMN a_id TYPE VARCHAR(255) USING (a_id::VARCHAR(255));
select * from information_schema.columns where table_name='a_table';
select * from a_table;
ALTER TABLE

ALTER TABLE
table_catalog table_schema table_name column_name ordinal_position column_default is_nullable data_type character_maximum_length character_octet_length numeric_precision numeric_precision_radix numeric_scale datetime_precision interval_type interval_precision character_set_catalog character_set_schema character_set_name collation_catalog collation_schema collation_name domain_catalog domain_schema domain_name udt_catalog udt_schema udt_name scope_catalog scope_schema scope_name maximum_cardinality dtd_identifier is_self_referencing is_identity identity_generation identity_start identity_increment identity_maximum identity_minimum identity_cycle is_generated generation_expression is_updatable
db_738274767 public a_table id 1 nextval('a_table_id_seq'::regclass) NO bigint null null 64 2 0 null null null null null null null null null null null null db_738274767 pg_catalog int8 null null null null 1 NO NO null null null null null NO NEVER null YES
db_738274767 public a_table a_id 2 null NO character varying 255 1020 null null null null null null null null null null null null null null null db_738274767 pg_catalog varchar null null null null 2 NO NO null null null null null NO NEVER null YES 
SELECT 2
id a_id
1 1
2 2
3 3
4 4
5 234523452345 
SELECT 5