Postgres 12

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

select version();

version

PostgreSQL 12.15 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-18), 64-bit

SELECT 1

create table atable
             ( id integer  generated by default  as identity (maxvalue 999999999)
             , ext_id text generated always as  (id::text) stored
             , colx text 
             ) ;

CREATE TABLE

insert into atable( colx) values ('abc'),('det');
select * from atable;

INSERT 0 2

id
ext_id
colx

1
1
abc

2
2
det

SELECT 2

-- particulat value has NO significance 
alter sequence atable_id_seq start with 581688 restart;

ALTER SEQUENCE

-- value 'changes' with sequence alteration
insert into atable (colx)
select 'Col val ' || x::text
  from generate_series(1,5) gs(x);
select * from atable;

INSERT 0 5

id
ext_id
colx

1
1
abc

2
2
det

581688
581688
Col val 1

581689
581689
Col val 2

581690
581690
Col val 3

581691
581691
Col val 4

581692
581692
Col val 5

SELECT 7

Update atable set ext_id = '1111111' where id < 581691;

ERROR:  column "ext_id" can only be updated to DEFAULT
DETAIL:  Column "ext_id" is a generated column.

update atable set id = 385922 where id = 1;
select * from atable order by colx;

UPDATE 1

id
ext_id
colx

385922
385922
abc

581688
581688
Col val 1

581689
581689
Col val 2

581690
581690
Col val 3

581691
581691
Col val 4

581692
581692
Col val 5

2
2
det

SELECT 7

id	ext_id	colx
1	1	abc
2	2	det
581688	581688	Col val 1
581689	581689	Col val 2
581690	581690	Col val 3
581691	581691	Col val 4
581692	581692	Col val 5

id	ext_id	colx
385922	385922	abc
581688	581688	Col val 1
581689	581689	Col val 2
581690	581690	Col val 3
581691	581691	Col val 4
581692	581692	Col val 5
2	2	det