Giriş
Neden Lazım
Normalization'dan kurtabilir.
Örnek
Elimizde foreign key table ile birleştirilen iki tablo
olsuncreate table users (
user_id int not null primary key generated always as identity,
name text not null unique
);
create table roles (
role_id int not null primary key generated always as identity,
name text not null unique
);
create table users_roles (
user_id int not null references users,
role_id int not null references roles,
primary key (user_id, role_id)
);Aslında rolleri array olarak ta saklayabiliriz. Şöyle
yaparızcreate table users (
user_id int not null primary key generated always as identity,
name text not null unique,
roles text[] not null,
constraint roles_check check (roles <@ array['admin', 'user', 'guest'])
);Açıklaması
şöyle
The constraint roles_check check (roles <@ array['admin', 'user', 'guest']) uses <@ array operator to ensure that every insert and update can only have array values that exist on the right side array ['admin', 'user', 'guest'].
Eğer constraint değiştirmek istersek şöyle
yaparızbegin;
alter table users drop constraint roles_check;
alter table users add constraint roles_check check
(roles <@ array['super', 'admin', 'user']);
end;
Ancak mevcut kayıtların bazısı yeni kısıtı ihlal edebilir. Bu yüzden not valid kullanmak gerekiyor. Şöyle
yaparızbegin;
alter table users drop constraint roles_check;
alter table users add constraint roles_check check
(roles <@ array['super', 'admin', 'user']) not valid;
end;
not_valid kullanılmasının sebebi
şöyle
- Records with invalid role guest will remain intact. That small deviation from the data integrity can be safely ignored (if business rules permit).
- All new inserts and updates will enforce new data integrity rules — roles must exist in a new array: ['super', 'admin', 'user'].
- The script that updates the new check constraint will run fast, even on big tables and it will not lock or block anyone or anything.
TEXT Array
Örnek CREATE TABLE event (
id INT8 NOT NULL,
version INT4,
sensor_names TEXT[],
sensor_values INTEGER[],
PRIMARY KEY (id)
)
Örnek
CREATE TABLE product (
id BIGINT,
images TEXT[]
)
INSERT INTO product(id, images) VALUES (1, '{"url1", "url2", "url3"}');
SELECT * FROM product WHERE images @> ARRAY['url2']
