PostgreSQL: sequence の使い方

こちらと同様のことを、自動増加の変数を、integer でなく、varchar(6) で行います。
PostgreSQL: serial の使い方

シーケンスとテーブルの作成

create01.sql

drop sequence seq_cities cascade;
CREATE SEQUENCE seq_cities;
drop table if exists cities;
create table cities (id varchar(10) primary key,
	name text, population int, date_mod date,
	nnx varchar(6) default nextval('seq_cities'));

実行結果

city=# \d cities
                                   Table "public.cities"
   Column   |         Type          | Collation | Nullable |             Default             
------------+-----------------------+-----------+----------+---------------------------------
 id         | character varying(10) |           | not null | 
 name       | text                  |           |          | 
 population | integer               |           |          | 
 date_mod   | date                  |           |          | 
 nnx        | character varying(6)  |           |          | nextval('seq_cities'::regclass)
Indexes:
    "cities_pkey" PRIMARY KEY, btree (id)

city=# \d seq_cities
                         Sequence "public.seq_cities"
  Type  | Start | Minimum |       Maximum       | Increment | Cycles? | Cache 
--------+-------+---------+---------------------+-----------+---------+-------
 bigint |     1 |       1 | 9223372036854775807 |         1 | no      |     1

city=# select last_value from seq_cities;
last_value 
------------
         1
(1 row)

データの挿入

insert01.sql

insert into cities values ('t3461','広島',72814,'2001-9-14');
insert into cities values ('t3462','福山',41738,'2001-7-21');
insert into cities values ('t3463','東広島',92513,'2001-6-12');
insert into cities values ('t3464','呉',93167,'2001-9-29');
insert into cities values ('t3465','尾道',95419,'2001-3-18');
insert into cities values ('t3466','竹原',82314,'2001-2-21');
insert into cities values ('t3467','三次',76152,'2001-8-16');
insert into cities values ('t3468','大竹',37541,'2001-7-7');
insert into cities values ('t3469','府中',46518,'2001-10-9');

実行結果

city=# select * from cities;
  id   |  name  | population |  date_mod  | nnx 
-------+--------+------------+------------+-----
 t3461 | 広島   |      72814 | 2001-09-14 | 1
 t3462 | 福山   |      41738 | 2001-07-21 | 2
 t3463 | 東広島 |      92513 | 2001-06-12 | 3
 t3464 | 呉     |      93167 | 2001-09-29 | 4
 t3465 | 尾道   |      95419 | 2001-03-18 | 5
 t3466 | 竹原   |      82314 | 2001-02-21 | 6
 t3467 | 三次   |      76152 | 2001-08-16 | 7
 t3468 | 大竹   |      37541 | 2001-07-07 | 8
 t3469 | 府中   |      46518 | 2001-10-09 | 9
(9 rows)

city=# select last_value from seq_cities;
 last_value 
------------
          9
(1 row)

データを削除して、再びデータを挿入

データを削除

city=# truncate cities;
TRUNCATE TABLE

再びデータを挿入

city=# select * from cities;
  id   |  name  | population |  date_mod  | nnx 
-------+--------+------------+------------+-----
 t3461 | 広島   |      72814 | 2001-09-14 | 10
 t3462 | 福山   |      41738 | 2001-07-21 | 11
 t3463 | 東広島 |      92513 | 2001-06-12 | 12
 t3464 | 呉     |      93167 | 2001-09-29 | 13
 t3465 | 尾道   |      95419 | 2001-03-18 | 14
 t3466 | 竹原   |      82314 | 2001-02-21 | 15
 t3467 | 三次   |      76152 | 2001-08-16 | 16
 t3468 | 大竹   |      37541 | 2001-07-07 | 17
 t3469 | 府中   |      46518 | 2001-10-09 | 18
(9 rows)

city=# select last_value from seq_cities;
 last_value 
------------
         18
(1 row)