Sütun Tipleri - Serial (Otomatik Sayı Üretir) - Kullanmayın

Giriş

Açıklaması şöyle. Yani SERIAL yerine IDENTITY kullanılırsa daha iyi

It is recommended to use IDENTITY instead since SERIAL has some weird behaviors.

Serial iki çeşit. Bunlar

1. SERIAL

2. BIGSERIAL

Bu sütunlara NOT NULL + PRIMARY KEY + UNIQUE gibi özellikler de atanabilir.

Diğer

Eğer serial veya bigserial yerine sequence kullanmak istersek şöyle yaparız

CREATE TABLE public.contacts
(
  contactid integer NOT NULL DEFAULT nextval('contacts_contactid_seq'::regclass),
  ...
);

1. SERIAL Sütun Tipi

Açıklaması şöyle

the keyword serial is PostgreSQL specific and it set up an auto-incrementing value and that is the typical way for the primary-key.

Açıklaması şöyle. 4 byte uzunluğundadır. Yani integer ile aynıdır.

Serial is just syntactic sugaring on top of an int column that takes its value from a sequence.

Açıklaması şöyle. Eğer transaction başarısız olsa bile serial numarası artmaya devam eder.

To avoid blocking concurrent transactions that obtain numbers from the same sequence, a nextval operation is never rolled back; that is, once a value has been fetched it is considered used, even if the transaction that did the nextval later aborts. This means that aborted transactions might leave unused "holes" in the sequence of assigned values.

Örnek
Şöyle yaparız.

create table testtable(
  id serial primary key,
  data integer not null
);

Şöyle yaparız.

insert into testtable ( data ) values ( 4 ), ( 5 ), ( 6 ), ( 7 );

Örnek

Şöyle yaparız

CREATE SCHEMA retail;
CREATE TABLE retail.orders_info (
  orderid SERIAL NOT NULL PRIMARY KEY,
  ...
);

Cache Parametresi
Açıklaması şöyle.

SERIAL columns are implemented using standard SQL sequences, which might generate out-of-order values when used by multiple concurrent sessions if the CACHE parameter is set to something more than 1

Daha detaylı açıklama şöyle.

Although multiple sessions are guaranteed to allocate distinct sequence values, the values might be generated out of sequence when all the sessions are considered. For example, with a cache setting of 10, session A might reserve values 1..10 and return nextval=1, then session B might reserve values 11..20 and return nextval=11 before session A has generated nextval=2. Thus, with a cache setting of one it is safe to assume that nextval values are generated sequentially; with a cache setting greater than one you should only assume that the nextval values are all distinct, not that they are generated purely sequentially.

2. BIGSERIAL Sütun Tipi
Örnek

Şöyle yaparız

CREATE TABLE foo (
  id BIGSERIAL PRIMARY KEY,
  ...
);

CREATE TABLE bar (
  foo_id BIGINT UNIQUE,
  ...
);

Örnek

Şöyle yaparız.

CREATE TABLE category
(
  id bigserial NOT NULL PRIMARY KEY,
  ...
)