create table user_table ( id bigserial primary key, name varchar(255) );
demo=# 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 ------------------+-----------------+----------------------+-----------+-------------------+-------------------------+---------------+-------------+---------------+---------------------+-----------+-------------- demo | public | user_table_id_seq | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO demo | public | logging_event_id_seq | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO (2 rows)
demo=# SELECT nextval('user_table_id_seq'); nextval --------- 1 (1 row) demo=# SELECT currval('user_table_id_seq'); currval --------- 1 (1 row) demo=# SELECT nextval('user_table_id_seq'); nextval --------- 2 (1 row) demo=# SELECT setval('user_table_id_seq',123); setval -------- 123 (1 row) demo=# SELECT currval('user_table_id_seq'); currval --------- 123 (1 row) demo=# SELECT nextval('user_table_id_seq'); nextval --------- 124 (1 row) demo=# SELECT nextval('user_table_id_seq'); nextval --------- 125 (1 row) demo=#
postgresql では、oracle のように sequence を副問合せで生成する必要なし。単に bigserial 列に値を設定しなければ良い
demo=# SELECT nextval('user_table_id_seq'); nextval --------- 125 (1 row) demo=# insert into user_table (name) values ('Hiromi'); INSERT 0 1 demo=# insert into user_table (name) values ('Kana'); INSERT 0 1 demo=# select * from user_table; id | name -----+-------- 126 | Hiromi 127 | Kana (2 rows)
\(2^{63}=2^3\times(2^{10})^6=10\times(10^3)^6=10^{19}\)
\(365\times24\times60\times60=6\times60\times6\times4\times60\times60=2^7\times3^5\times10^3=128\times243\times10^3=10^7\)
\(9223372036854775807\div365\div24\div60\div60=292471203704\)