PostgreSQL: serial の使い方

テーブルの作成

create01.sql

create table cities (id varchar(10) primary key,
	name text, population int, date_mod date,
	number serial not null);

実行結果

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

データの挿入

insert01.sql

nsert 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  | number 
-------+--------+------------+------------+--------
 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=# truncate cities;
TRUNCATE TABLE

再びデータを挿入

city=# select * from cities;
  id   |  name  | population |  date_mod  | number 
-------+--------+------------+------------+--------
 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)