postgres_surrogate_key - tetsuyaf1100/hello-world GitHub Wiki

postgres surrogate key

surrogate_key sample

CREATE SEQUENCE IF NOT EXISTS sample_surrogate_key_seq;

CREATE TABLE  IF NOT EXISTS sample (
    surrogate_key integer DEFAULT nextval('public.sample_surrogate_key_seq'::regclass) NOT NULL,
    name text,
    modified_at timestamp(6) with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL
);

show table

myapp=# select * from sample_surrogate_key_seq ;
 last_value | log_cnt | is_called
------------+---------+-----------
          1 |       0 | f
(1 row)

myapp=# select * from sample;
 surrogate_key | name | modified_at
---------------+------+-------------
(0 rows)

insert data

myapp=# insert into sample (name)values('aaa');
INSERT 0 1
myapp=# insert into sample (name)values('bbb');
INSERT 0 1

table

myapp=# select * from sample_surrogate_key_seq ;
 last_value | log_cnt | is_called
------------+---------+-----------
          2 |      32 | t
(1 row)

myapp=# select * from sample;
 surrogate_key | name |          modified_at
---------------+------+-------------------------------
             1 | aaa  | 2021-10-18 07:30:49.877977+00
             2 | bbb  | 2021-10-18 07:40:47.966525+00
(2 rows)