Экзотические типы данных в PostgreSQL

В докладе описана работа с экзотическими для БД типами данных: диапазоны, массивы, JSON и работа с ними в ActiveRecord

Transcript

1. Экзотические типы данных в PostgreSQL
2. То, чего нет в других СУБД • Range • Array • JSON Нагло вру! На самом деле, в MySQL есть JSON
3. Range int4range, int8range — диапазон целых чисел [1, 10] numrange — диапазон вещественных чисел [1.3278, 3.3498] tsrange, tstzrange — диапазоны даты и времени [‘2014-01-14 09:00:00’, ‘2014-01-14 09:00:00’] daterange — диапазон дат [‘2013-01-14’, ‘2014-01-15’] В Ruby эта запись смотрелась бы вот так: 1..10 А эта вот так: 14 Jan 2013..15 Jan 2014
4. Диапазоны (1, 10) - от двух до девяти (1, 10] - от двух до десяти [1, 10) - от единицы до девяти [1, 10] - от единицы до десяти empty - пустой диапазон
5. Добавление столбца daterange SQL: ALTER TABLE my_table ADD COLUMN my_range daterange; ActiveRecord: add_column :my_table, :my_range, :daterange
6. Добавление записи с dates_range SQL: INSERT INTO my_table (my_range) VALUES('[2014-01-14, 2014-01-15]'); или INSERT INTO my_table (my_range) VALUES('(2014-01-13, 2014-01-17]'::daterange); Если вы хотите указать, какие границы включить в диапазон, а какие нет
7. ActiveRecord: range = Date.today..(Date.today + 1.day) record = MyTable.create(my_range: range) puts record.my_range #=> 14 Feb 2014...Mon, 16 Feb 2014 Добавление записи с daterange в базе данных хранится значение [‘2014-01-14’, ‘2014-01-16’)
8. Изменение границ диапазонов при создании записи [1, 5] [1, 6) [1, 5) [1, 5) (1, 5] [2, 6) (1, 5) [2, 5)
9. Функции выборки всем известные функции: =, <> - равно / не-равно <, >, <=, >= - меньше / больше
10. @> - содержит ли range другой range int4range(1, 10) @> int4range(3, 7) -- true int4range(3, 7) @> int4range(1, 10) -- false или элемент int4range(2,4) @> 3 -- true int4range(2,4) @> 28 -- false и его аналог наоборот: <@ Функции выборки
11. && - пересекаются ли два range: int4range(2,4) && int4range(2,3) -- true int4range(2,4) && int4range(5,8) -- false Функции выборки
12. << - какой из range слева, а какой справа int8range(1, 10) << int8range(100, 110) -- true int8range(200, 300) << int8range(100, 110) -- false и его собрат >>, выполняющий все с точностью до наоборот Функции выборки
13. объединение numrange(5,15) + numrange(10,20) #=> [5,20) пересечение int8range(5,15) * int8range(10,20) #=> [10,15) разность int8range(5,15) - int8range(10,20) #=> [5,10) Функции выборки
14. Примеры для ActiveRecord MyTable.where(‘my_range @> date(?)', Date.today) # SELECT * FROM "my_tables" WHERE (dates_range @> date('2014-01-15'))
15. Array integer[] - одномерный массив целых чисел integer[][] - а это уже двумерный xml[], hstore[], text[] - Почти любой тип данных Postgresql можно запихнуть в массив
16. Добавление столбца массива SQL: ALTER TABLE my_table ADD COLUMN my_array integer[]; ActiveRecord: add_column :my_table, :my_range, 'integer[]'
17. Добавление записи с массивом SQL: INSERT INTO my_table (my_array) VALUES('{1, 3, 4}'); ActiveRecord: MyTable.create(my_array: [1, 3, 4]) Ага, именно так выглядит в массив в PostgreSQL
18. Выборка {1, 2, 3} SELECT my_array[2] FROM my_table; 2 {2, 45, 4} 45 {3, 4,6} 4
19. Выборка {1, 2, 3} SELECT my_array FROM my_table WHERE 2 = ANY(my_array); {1, 2, 3} {2, 45, 4} {2, 45, 4} {3, 4,6}
20. Выборка {2, 2, 2} SELECT my_array FROM my_table WHERE 2 = ALL(my_array); {2, 2, 2} {2, 45, 4} {3, 4,6}
21. Выборка {1, 2, 3} SELECT array_length(my_array) FROM my_table; 3 {2, 45, 4} 3 {3, 4, 6, 7} 4
22. Пример для ActiveRecord MyTable.where(‘2 = ALL(my_array)’) # SELECT my_array FROM my_table WHERE 2 = ALL(my_array);
23. JSON { "firstName": "Иван", "lastName": "Иванов", "address": { "streetAddress": "Московское ш., 101, кв.101", "city": "Ленинград", "postalCode": 101101 }, "phoneNumbers": [ "812 123-1234", "916 123-4567" ] }
24. Добавление столбца JSON SQL: ALTER TABLE my_table ADD COLUMN my_json json; ActiveRecord: add_column :my_table, :my_json, :json
25. Добавление записи SQL: INSERT INTO my_table (my_array) VALUES('{ "a": "a", "b": { "c": "c", "d": "d" } }');
26. Добавление записи ActiveRecord: MyTable.create(my_json: { a: "a", b: { c: "c", d: "d" } }) или MyTable.create(my_json: "{\"a\"=>2, \"b\"=>{\"c\"=>\"c\", \" d\"=>\"d\"}}")
27. Выборка SELECT my_json->’b’ FROM my_table; или SELECT my_json->>’b’ FROM my_table; { "a": "a", "b": { "c": "c", "d": "d" } } { "a": "a", "b": { "c": "c", "d": "d" } } { "c": "c", "d": "d" } { "c": "c", "d": "d" } Вернет JSON Вернет Text
28. Выборка SELECT my_json FROM my_table WHERE my_json->>'e' <> ''; { "a": "a", "b": { "c": "c", "d": "d" } } { "e": "e" } { "e": "e" }
29. автор: Юрьев Виталик github: wwju