PostgreSQL’de Range Veri Tipleri ve Örnekleri

Merhaba,

Range veri tipleri, verilerin belirli aralıkta olmasıyla ilgilidir. Örneğin otel rezervasyon uygulamasında bir odanın hangi saatler arasında boş veya dolu olduğunu tstzrange(timestamp with time zone) veri tipini kullanarak saklayabilirsiniz veritabanınızda. Postgres’de bahsedilen bu örnekteki gibi farklı veri tiplerine de uygun range veri tipleri bulunmaktadır. Timestamp’in yanında bir de numeric, date, int veri tiplerine de uygun farklı range veri tipleri bulunmaktadır.

Postgres’de mevcut range veri tipleri aşağıdaki gibidir.

• int4range — Range of integer
• int8range — Range of bigint
• numrange — Range of numeric
• tsrange — Range of timestamp without time zone
• tstzrange — Range of timestamp with time zone
• daterange — Range of date

Aralıkların Belirlenmesi

İçi boş olmayan aralığın düşük ve yüksek olmak üzere iki adet sınırı bulunur. Bu iki sınır arasındaki tüm değerler bizim veri tipimize uygun bu aralık içinde yer alır. Düşük sınırın aralığa dahil olması için ‘[‘ işareti kullanılır, eğer başlangıç değerinin bu aralığa dahil olması istenmezse ‘(‘ işareti kullanılır. Yüksek sınır değerinin verilen aralık içine dahil edilmesi istenirse ‘]’ işareti kullanılır. Eğer bu değerin dahil olması istenmezse de ‘)’ yazılır.

Eğer herhangi bir bilgi verilmezse aralıkların başlangıç ve bitişine ait ön tanımlı olarak Postgres ‘[)’ şeklinde alır. Yani düşük değer dahil, yüksek değer dahil değildir bu aralığa.

İstenen aralığı belirlemek için bazı örnekler şöyle;

[gunce] # SELECT '[1,6]'::int4range; -[ RECORD 1 ]----- int4range | [1,7)

Aşağıdaki örneğin sorguda verilen bitiş tarihi ile sorgu sonucunda gelen değere bakın. Son değeri dahil ettiğimiz için bir sonraki günü dahil etmeden bize gösteriyor, postgres’in ön tanımlı olan yukarıda bahsettiğim ‘[)’ durumundan dolayı.

[gunce] # SELECT '[2012-03-28, 2012-04-02]'::tsrange; -[ RECORD 1 ]------------------------------------------ tsrange | ["2012-03-28 00:00:00","2012-04-02 00:00:00"]

Yukarıdaki tsrange veri tipini aşağıdaki şekilde de kullanabiliriz.

[gunce] # SELECT tsrange('[2012-03-28, 2012-04-02]'); -[ RECORD 1 ]------------------------------------------ tsrange | ["2012-03-28 00:00:00","2012-04-02 00:00:00"]

Bu örnekte başlangıç ve bitiş değerlerinin dahil olup olmama durumunu bildirmediğimiz için Postgres’in ön tanımlı özelliği devreye giriyor ve düşük değeri dahil edip, son değeri dahil etmiyor bu aralığa.

[gunce] # SELECT numrange(9.0, 9.5); -[ RECORD 1 ]------- numrange | [9.0,9.5)

Alt ve üst sınıra ait daterange örnekleri;

-- Alt ve üst sınır arasındaki tüm değerler(üst sınır değeri hariç) [gunce] # SELECT daterange'[today, 2019-03-02)'; -[ RECORD 1 ]---------------------- daterange | [2019-02-27,2019-03-02) -- Alt ve üst sınır dahil olmak üzere, bu sınırlar arasında kalan tüm tarihler [gunce] # select daterange'[today, 2019-03-02]'; -[ RECORD 1 ]---------------------- daterange | [2019-02-27,2019-03-03) -- Alt sınırdan sonraki günden başlayarak üst sınır dahil tüm tarihler [gunce] # select daterange'(today, 2019-03-02]'; -[ RECORD 1 ]---------------------- daterange | [2019-02-28,2019-03-03) -- Alt ve üst sınırların olmadığı ve arasındaki tüm tarihler [gunce] # select daterange'(today, 2019-03-02)'; -[ RECORD 1 ]---------------------- daterange | [2019-02-28,2019-03-02)

Aralık için kullanılan değerin dahil olup olmadığını lower_inc() ve upper_inc() fonksiyonlarını kullanarak görebilirsiniz.

SELECT UPPER_INC(numrange'[1,2)'); -- false SELECT LOWER_INC(numrange'[1,2)'); -- true SELECT UPPER_INC(numrange'[1,2]'); -- true SELECT LOWER_INC(numrange'[1,1)'); -- false SELECT UPPER_INC(numrange'[1,1)'); -- false

Sonsuz(Sınırsız) Aralıklar

Düşük veya yüksek sınır dışındaki tüm verileri saklamak istenebilir. Örneğin düşük sınır dışındaki tüm değerleri kabul etmek demek yüksek sınırın sınırsız olması anlamına gelir. Aynı şekilde yüksek sınırın dışındaki tüm değerlerin kabul edilmesi için düşük sınırının olmaması gerekir, yani yüksek sınırınızdan düşük sınırınıza doğru sonsuz değerlerin kabul edilmesi durumu vardır.

Sonsuzluk ifadesini “infinity” ifadesi ile belirleyebiliriz. Bunu timestamp değerler için kullanabiliriz. Örneğin, tsrange veri tipi için, bugünden itibaren sonsuz değerin kabul edildiği bir aralık olması için

[gunce] # select tsrange'[today,infinity)', numrange'[0,)'; -[ RECORD 1 ]------------------------------ tsrange  | ["2019-02-27 00:00:00",infinity) numrange | [0,)

Aralığın herhangi bir tarafına doğru sonsuzluk olması istenirse NULL eklenebilir veya herhangi bir değer eklemezseniz de aynı durum söz konusudur. Bir değer ile sınırlandırma yapmayacağınız için sonsuzluk sağlanmış olur.
 
Aralığın herhangi bir tarafı için sonsuzluk durumunun olup olmadığını lower_inf() ve upper_inf() fonksiyonlarını kullanarak bulabiliriz fakat bu iki fonksiyon, aralık içi kullanılan özel ifadeleri algılayamaz, infinity gibi. Bu fonksiyonlar sınırın boş oluş olmadığını kontrol ederek sonsuzluk olup olmadığına karar verir.

select upper_inf(tsrange'[today,infinity)'); --false select upper_inf(numrange'[0,)'); --true select upper_inf(numrange(1, null)); --true select upper_inf(numrange'[1, 4)'); --false

Yukarıdaki örnekte “infinity” ifadesi sonsuzluğu verse de bize, bu iki fonksiyon bu gibi özel ifadelerin tiplerini anlayamaz. Dolayısıyla bu alan kontrol edilemeyen alanlardan olduğu için sonuç false yani sonsuz değilmiş gibi döndü bize. Numrange için aynı kontrolü yaptığımızda sonsuzluk kontrolünü yapıp bize true dönüyor. Bu kısma dikkat edilmelidir. upper_inf(tsrange'[today,infinity)') ifadesinin neden false dönmesiyle ilgili açıklamayı Tom Lane yılar önce burada yapmıştır.

Aralıkların Belirlenmesinde Kullanılan Bloklar

“()”, “(]”, “[)”, or “[]” blokları yukarı kullanıldığından daha farklı şekilde kullanılabilir.

[gunce] # SELECT int8range(1, 8, '(]'), numrange(NULL, 5), numrange(1,6); -[ RECORD 1 ]----- int8range | [2,9) numrange  | (,5) numrange  | [1,6)

Range Veri Tiplerinin Kullanımı

Bu aralıkların getirilmesinin yanında karşılaştırma yapmak için de kullanılabilir.

-- Bir aralık bulamayacağı için sonuç true döner. SELECT isempty(numrange(1,1)); -- true -- Bir aralık mevcut olduğu için sonuç false gelir. SELECT isempty(numrange(1,5)); -- false

Range veri tipleri için aşağıdaki gibi karşılaştırma yapılabilir.

select daterange'[2019-01-01, 2019-02-01]' = daterange'[2019-01-01, 2019-02-01]'; -- true select daterange'[2019-01-01, 2019-02-01]' = daterange'[2019-03-01, 2019-04-01]'; -- false select daterange'[2019-01-01, 2019-02-01]' >= daterange'[2019-03-01, 2019-04-01]'; -- false

-- Alt ve üst sınıra ait verileri getirir. [gunce] # SELECT upper(int8range(15, 25)), lower(int8range(15,25)); -[ RECORD 1 ] upper | 25 lower | 15 -- Ortak aralığı alır [gunce] # SELECT int4range(10, 20) * int4range(15, 25); -[ RECORD 1 ]----- ?column? | [15,20) -- 3'ün int4range aralığındaki elementlerden biri olup olmadığına bakar ve olmadığı için false döner. [gunce] # SELECT int4range(10, 20) @> 3; -- false -- 11'in 10,20 aralığında olup olmadığına bakar ve bu aralıkta yer aldığı için true döner. [gunce] # SELECT int4range(10, 20) @>11; -- true

Otel Rezervasyon Örneği

Odaların hangi gün ve saatler arasında rezervasyonunun olduğu bilgisinin saklandığı bir tablomuz olsun. Oda numarası ve rezervasyon tarih aralığının girildiği sütunlara sahip olsun. Kalış süresini timestamp için bir range eklemeliyiz. Eğer timezone olacaksa tztsrange veri tipini, eğer timezone kullanılmayacaksa tsrange veri tipini kullanmamız yeterlidir.

CREATE TABLE ROOM_BOOKING (id serial, room_number int, stay tsrange);CREATE TABLE INSERT INTO room_booking(room_number, stay) values(100, '[2019-02-25 14:00, 2019-02-26 11:00]'); INSERT INTO room_booking(room_number, stay) values(100, '[2019-02-26 14:00, 2019-02-27 11:00]'); INSERT INTO room_booking(room_number, stay) values(100, '[2019-02-27 14:00, 2019-02-28 11:00]'); INSERT INTO room_booking(room_number, stay) values(103, '[2019-02-27 14:00, 2019-02-28 11:00]'); INSERT INTO room_booking(room_number, stay) values(103, '[2019-02-27 14:00, 2019-02-28 11:00]'); INSERT INTO room_booking(room_number, stay) values(104, '[2019-02-28 14:00, 2019-03-05 11:00]'); [gunce] # SELECT * FROM room_booking; id | room_number |                     stay                     ----+-------------+-----------------------------------------------  1 |         100 | ["2019-02-25 14:00:00","2019-02-26 11:00:00"]  2 |         100 | ["2019-02-26 14:00:00","2019-02-27 11:00:00"]  3 |         100 | ["2019-02-27 14:00:00","2019-02-28 11:00:00"]  4 |         103 | ["2019-02-27 14:00:00","2019-02-28 11:00:00"]  5 |         103 | ["2019-02-27 14:00:00","2019-02-28 11:00:00"]  6 |         104 | ["2019-02-28 14:00:00","2019-03-05 11:00:00"] (6 rows)

Çıkış tarihlerine göre sıralamak için aşağıdaki script kullanılabilir.

[gunce] # SELECT * FROM room_booking ORDER BY upper(stay) desc; id | room_number |                     stay                     ----+-------------+-----------------------------------------------  6 |         104 | ["2019-02-28 14:00:00","2019-03-05 11:00:00"]  3 |         100 | ["2019-02-27 14:00:00","2019-02-28 11:00:00"]  4 |         103 | ["2019-02-27 14:00:00","2019-02-28 11:00:00"]  5 |         103 | ["2019-02-27 14:00:00","2019-02-28 11:00:00"]  2 |         100 | ["2019-02-26 14:00:00","2019-02-27 11:00:00"]  1 |         100 | ["2019-02-25 14:00:00","2019-02-26 11:00:00"] (6 rows)

Belirli tarih aralığını çekelim.

[gunce] # SELECT * FROM room_booking  WHERE stay && tsrange'[2019-02-25, 2019-02-27)'; id | room_number |                     stay                     ----+-------------+-----------------------------------------------  1 |         100 | ["2019-02-25 14:00:00","2019-02-26 11:00:00"]  2 |         100 | ["2019-02-26 14:00:00","2019-02-27 11:00:00"] (2 rows)

Bir tarih aralığından büyük olanları çekelim.

[gunce] # SELECT * FROM room_booking WHERE stay >> tsrange'[2019-02-25, 2019-02-27)'; id | room_number |                     stay                     ----+-------------+-----------------------------------------------  3 |         100 | ["2019-02-27 14:00:00","2019-02-28 11:00:00"]  4 |         103 | ["2019-02-27 14:00:00","2019-02-28 11:00:00"]  5 |         103 | ["2019-02-27 14:00:00","2019-02-28 11:00:00"]  6 |         104 | ["2019-02-28 14:00:00","2019-03-05 11:00:00"] (4 rows)

Değerin belirlenen aralıkta olup olmadığını kontrol edelim.

[gunce] # SELECT stay * tsrange'[2019-02-25, 2019-02-27)',room_number FROM room_booking;                   ?column?                    | room_number -----------------------------------------------+------------- ["2019-02-25 14:00:00","2019-02-26 11:00:00"] |         100 ["2019-02-26 14:00:00","2019-02-27 00:00:00") |         100 empty                                         |         100 empty                                         |         103 empty                                         |         103 empty                                         |         104 (6 rows)

Range veri tipleri için kullanılan operatörlerin bazıları aşağıdaki gibidir. Tamamına buradan ulaşabilirsiniz.

Zamanlama

Postgres'in harika bir özelliği bulunuyor: EXCLUDE constraint. Bu constraintin eklenmesiyle tablodaki iki satırın karşılaştırması yapılıyor belirli sütun için. EXCLUDE ifadesi index bazlı bir constraint olduğu için benzer bir değerin olup olmadığını ve daha önceden eklenmemiş verinin girilmesini kontrol eder. 

Aşağıdaki örnekte takvimde bir zaman aralığını doldurduysak o zaman aralığına başka veri girilmesini önlemek için bu constraint kullanılabilir. 

CREATE TABLE reservation_new  (id SERIAL,  room_number INT,  stay DATERANGE,  EXCLUDE USING GIST(stay WITH &&)); INSERT INTO reservation_new (room_number, stay) VALUES(100, daterange'(2019-01-01, 2019-01-05)'); INSERT INTO reservation_new (room_number, stay) VALUES(100, daterange('2019-01-01', '2019-01-05')); ERROR: 23P01: conflicting key value violates exclusion  constraint "reservation_new_stay_excl" DETAIL: Key (stay)=([2019-01-01,2019-01-05)) conflicts with  existing key (stay)=([2019-01-01,2019-01-05)). LOCATION: check_exclusion_or_unique_constraint, execIndexing.c:836

 Sevgiler,