PostgreSQL – typy danych

W tym rozdziale omówimy typy danych używane w PostgreSQL. Podczas tworzenia tabeli, dla każdej kolumny określasz typ danych, tzn,

Daje to kilka korzyści –

• Skonsekwencja – Operacje na kolumnach o tym samym typie danych dają spójne wyniki i są zazwyczaj najszybsze.

• Weryfikacja – Właściwe użycie typów danych pociąga za sobą walidację formatu danych i odrzucenie danych spoza zakresu typu danych.

• Kompaktowość – Ponieważ kolumna może przechowywać jeden typ wartości, jest przechowywana w sposób kompaktowy.

• Wydajność – Właściwe użycie typów danych daje najbardziej wydajne przechowywanie danych. Przechowywane wartości mogą być szybko przetwarzane, co zwiększa wydajność.

PostgreSQL obsługuje szeroki zestaw typów danych. Ponadto użytkownicy mogą tworzyć własne typy danych za pomocą polecenia CREATE TYPE SQL. Istnieją różne kategorie typów danych w PostgreSQL.

Typy numeryczne

Typy numeryczne składają się z dwubajtowych, czterobajtowych i ośmiobajtowych liczb całkowitych, czterobajtowych i ośmiobajtowych liczb zmiennoprzecinkowych oraz dziesiętnych z możliwością wyboru precyzji. Poniższa tabela zawiera listę dostępnych typów.

Name	Rozmiar pamięci	Opis	Zakres
smallint	2 bajty	small-przedział integer	-32768 do +32767
integer	4 bajty	typowy wybór dla integer	-.2147483648 do +2147483647
bigint	8 bajtów	wielki zakres liczb całkowitych	-9223372036854775808 do 9223372036854775807
decymalna	zmienna	precyzja określona przez użytkownika,dokładna	do 131072 cyfr przed przecinkiem; do 16383 cyfr po przecinku
numeryczna	zmienna	precyzja określona przez użytkownika, dokładna	do 131072 cyfr przed przecinkiem; do 16383 cyfr po przecinku
rzeczywista	4 bajty	zmienna precyzja, niedokładna	6 cyfr dziesiętnych precyzja
podwójna precyzja	8 bajtów	zmienna precyzja,niedokładna	15 cyfr dziesiętnych precyzja
smallserial	2 bajty	mała autoinkrementacja integer	1 do 32767
serial	4 bajty	autoinkrementacja integer	1 do 2147483647
bigserial	8 bajtów	large autoincrementing integer	1 do 9223372036854775807

Typy monetarne

Typ pieniężny przechowuje kwotę walutową z ustaloną precyzją ułamkową. Wartości typów danych numeric, int i bigint mogą być rzutowane na typ money. Używanie liczb zmiennoprzecinkowych nie jest zalecane do obsługi pieniędzy ze względu na możliwość wystąpienia błędów zaokrąglenia.

Nazwa	Rozmiar pamięci	Opis	Zakres
pieniądze	8 bajtów	kwota waluty	-92233720368547758.08 do +92233720368547758.07

Typy znaków

W tabeli poniżej znajdują się typy znaków ogólnego przeznaczenia dostępne w PostgreSQL.

S. No.	Nazwa & Opis
1	znak varying(n), varchar(n) variable-length with limit
2	character(n), char(n) fixed-length, blank padded
3	text variable unlimited length

Binary Data Types

Typ danych bytea umożliwia przechowywanie łańcuchów binarnych jak w tabeli poniżej.

Nazwa	Rozmiar pamięci	Opis
bytea	1 lub 4 bajty plus rzeczywisty ciąg binarny	łańcuch binarny o zmiennejdługość łańcucha binarnego

Typy daty i czasu

PostgreSQL obsługuje pełen zestaw typów daty i czasu SQL, jak pokazano w poniższej tabeli. Daty liczone są zgodnie z kalendarzem gregoriańskim. Wszystkie typy mają rozdzielczość 1 mikrosekundy / 14 cyfr, z wyjątkiem typu daty, którego rozdzielczość to dzień.

Name	Storage Size	Description	Low Value	High Value
timestamp	8 bajtów	zarówno data jak i czas (bez strefy czasowej)	4713 BC	294276 AD
TIMESTAMPTZ	8 bajtów	zarówno data jak i czas, ze strefą czasową	4713 BC	294276 AD
data	4 bajty	data (bez pory dnia)	4713 BC	5874897 AD
time	8 bajtów	czas dnia (brak daty)	00:00:00	24:00:00
czas ze strefą czasową	12 bajtów	tylko godziny dnia, ze strefą czasową	00:00:00+1459	24:00:00-1459
interwał	12 bajtów	interwał czasowy	-178000000 lat	178000000 lat

Typ Boolean

PostgreSQL udostępnia standardowy typ SQL Boolean. Typ danych Boolean może posiadać stany true, false oraz trzeci stan, unknown, który jest reprezentowany przez wartość SQL null.

.

Nazwa	Rozmiar pamięci	Opis
boolean	1 bajt	stan true lub false

Typ wyliczeniowy

Typy wyliczeniowe (enum) są typami danych, które obejmują statyczny, uporządkowany zbiór wartości. Są one odpowiednikiem typów enum obsługiwanych w wielu językach programowania.

W przeciwieństwie do innych typów, typy wyliczeniowe muszą być tworzone za pomocą polecenia CREATE TYPE. Ten typ jest używany do przechowywania statycznego, uporządkowanego zbioru wartości. Na przykład kierunki kompasu, czyli NORTH, SOUTH, EAST i WEST lub dni tygodnia, jak pokazano poniżej –

CREATE TYPE week AS ENUM ('Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun');

Enumerated, po utworzeniu, może być używany jak każdy inny typ.

Typ geometryczny

Typy danych geometrycznych reprezentują dwuwymiarowe obiekty przestrzenne. Najbardziej podstawowy typ, punkt, stanowi podstawę dla wszystkich pozostałych typów.

Nazwa	Rozmiar pamięci	Przedstawienie	Opis
punkt	16 bajtów	Punkt na płaszczyźnie	(x,y)
linia	32 bajty	Linia nieskończona (nie w pełni zaimplementowana)	((x1,y1),(x2,y2))
lseg	32 bajty	Segment linii nieskończonej	((x1,y1),(x2,y2))
box	32 bajty	Pudełko prostokątne	((x1,y1),(x2,y2))
ścieżka	16+16n bajtów	Ścieżka zamknięta (podobna do wielokąta)	((x1,y1),…)
ścieżka	16+16n bajtów	Ścieżka otwarta
polygon	40+16n	Poligon (podobny do zamkniętej ścieżki)	((x1,y1),….)
koło	24 bajty	Koło	<(x,y),r> (punkt środkowy i promień)

Typ adresu sieciowego

PostgreSQL oferuje typy danych do przechowywania adresów IPv4, IPv6 oraz MAC. Lepiej jest używać tych typów zamiast typów tekstowych do przechowywania adresów sieciowych, ponieważ typy te oferują kontrolę błędów wejściowych oraz wyspecjalizowane operatory i funkcje.

Nazwa	Rozmiar pamięci	Opis
cidr	7 lub 19 bajtów	Sieci IPv4 i IPv6
inet	7 lub 19 bajty	Hostery i sieci IPv4 i IPv6
macaddr	6 bajtów	Adresy MAC

Typ ciągu bitów

Typy ciągu bitów są używane do przechowywania masek bitów. Mają one wartość 0 lub 1. Istnieją dwa typy bitowe SQL: bit(n) i bit varying(n), gdzie n jest dodatnią liczbą całkowitą.

Text Search Type

Typ ten obsługuje wyszukiwanie pełnotekstowe, które jest czynnością przeszukiwania zbioru dokumentów w języku naturalnym w celu zlokalizowania tych, które najlepiej pasują do zapytania. Istnieją dwa typy danych dla tego typu –

S. No.	Name & Description
1	tsvector Jest to posortowana lista różnych słów, które zostały znormalizowane w celu połączenia różnych wariantów tego samego słowa, zwanych „leksemami”.
2	tsquery To przechowuje leksemy, które mają być wyszukane, i łączy je z uwzględnieniem operatorów boolowskich & (AND), | (OR), i ! (NOT). Nawiasy mogą być użyte do wymuszenia grupowania operatorów.

Typ UUID

A UUID (Universally Unique Identifiers) jest zapisywany jako sekwencja małych cyfr szesnastkowych, w kilku grupach oddzielonych myślnikami, a konkretnie grupa ośmiu cyfr, po której następują trzy grupy po cztery cyfry, po których następuje grupa 12 cyfr, w sumie 32 cyfry reprezentujące 128 bitów.

Przykładem UUID jest – 550e8400-e29b-41d4-a716-446655440000

Typ XML

Typ danych XML może być używany do przechowywania danych XML. Aby przechowywać dane XML, należy najpierw utworzyć wartości XML za pomocą funkcji xmlparse w następujący sposób –

XMLPARSE (DOCUMENT '<?xml version="1.0"?><tutorial><title>PostgreSQL Tutorial </title> <topics>...</topics></tutorial>')XMLPARSE (CONTENT 'xyz<foo>bar</foo><bar>foo</bar>')

Typ JSON

Typ danych json może być używany do przechowywania danych JSON (JavaScript Object Notation). Takie dane mogą być również przechowywane jako tekst, ale typ danych json ma tę zaletę, że sprawdza, czy każda przechowywana wartość jest poprawną wartością JSON. Dostępne są również powiązane funkcje pomocnicze, które mogą być bezpośrednio używane do obsługi typu danych JSON, jak poniżej.

Example	Example Result
array_to_json('{{1,5},{99,100}}’:int)	,]
row_to_json(row(1,’foo’))	{„f1”:1, „f2”: „foo”}

Typ tablicy

PostgreSQL daje możliwość zdefiniowania kolumny tabeli jako tablicę wielowymiarową o zmiennej długości. Można tworzyć tablice o dowolnym wbudowanym lub zdefiniowanym przez użytkownika typie podstawowym, typie enum lub typie złożonym.

Deklaracja tablic

Typ tablicy można zadeklarować jako

CREATE TABLE monthly_savings ( name text, saving_per_quarter integer, scheme text);

lub za pomocą słowa kluczowego „ARRAY” jako

CREATE TABLE monthly_savings ( name text, saving_per_quarter integer ARRAY, scheme text);

Wstawianie wartości

Wartości tablicy można wstawić jako stałą dosłowną, zamykając wartości elementów w nawiasach klamrowych i oddzielając je przecinkami. Przykład jest pokazany poniżej –

INSERT INTO monthly_savings VALUES ('Manisha', '{20000, 14600, 23500, 13250}', '{{"FD", "MF"}, {"FD", "Property"}}'); 

Dostęp do tablic

Przykład dostępu do tablic jest pokazany poniżej. Podane poniżej polecenie wybierze osoby, których oszczędności są większe w drugim kwartale niż w czwartym.

SELECT name FROM monhly_savings WHERE saving_per_quarter > saving_per_quarter;

Modifying Arrays

Przykład modyfikacji tablic jest jak pokazano poniżej.

UPDATE monthly_savings SET saving_per_quarter = '{25000,25000,27000,27000}'WHERE name = 'Manisha';

lub używając składni wyrażenia ARRAY –

UPDATE monthly_savings SET saving_per_quarter = ARRAYWHERE name = 'Manisha';

Wyszukiwanie tablic

Przykład wyszukiwania tablic jest jak pokazano poniżej.

SELECT * FROM monthly_savings WHERE saving_per_quarter = 10000 ORsaving_per_quarter = 10000 ORsaving_per_quarter = 10000 ORsaving_per_quarter = 10000;

Jeśli rozmiar tablicy jest znany, można użyć metody wyszukiwania podanej powyżej. W przeciwnym razie, poniższy przykład pokazuje, jak wyszukiwać, gdy rozmiar nie jest znany.

SELECT * FROM monthly_savings WHERE 10000 = ANY (saving_per_quarter);

Typy złożone

Typ ten reprezentuje listę nazw pól i ich typów danych, tj, strukturę wiersza lub rekordu tabeli.

Deklaracja typów złożonych

Następujący przykład pokazuje, jak zadeklarować typ złożony

CREATE TYPE inventory_item AS ( name text, supplier_id integer, price numeric);

Tego typu danych można użyć w tworzeniu tabel jak poniżej –

CREATE TABLE on_hand ( item inventory_item, count integer);

Wejście wartości złożonych

Wartości złożone można wstawić jako stałą dosłowną, zamykając wartości pól w nawiasach i oddzielając je przecinkami. Przykład jest pokazany poniżej –

INSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000);

To jest ważne dla inventory_item zdefiniowanego powyżej. Słowo kluczowe ROW jest w rzeczywistości opcjonalne, tak długo, jak masz więcej niż jedno pole w wyrażeniu.

Dostęp do typów złożonych

Aby uzyskać dostęp do pola kolumny złożonej, użyj kropki, po której następuje nazwa pola, podobnie jak przy wyborze pola z nazwy tabeli. Na przykład, aby wybrać kilka podpól z naszej przykładowej tabeli on_hand, zapytanie powinno wyglądać jak poniżej –

SELECT (item).name FROM on_hand WHERE (item).price > 9.99;

Możesz nawet użyć nazwy tabeli (na przykład w zapytaniu multitable), jak poniżej –

SELECT (on_hand.item).name FROM on_hand WHERE (on_hand.item).price > 9.99;

Typy zakresów

Typy zakresów reprezentują typy danych, które wykorzystują zakres danych. Typ zakresu może być zakresem dyskretnym (np. wszystkie wartości całkowite od 1 do 10) lub zakresem ciągłym (np, dowolny punkt w czasie pomiędzy 10:00 a 11:00).

Wbudowane typy

• int4range – Zakres integer

• int8range – Zakres bigint

• numrange – Zakres numeric

• tsrange – Zakres timestamp bez strefy czasowej. Zakres znacznika czasu bez strefy czasowej

• tstzrange – Zakres znacznika czasu ze strefą czasową

• daterange – Zakres daty

Niestandardowe typy zakresów mogą być tworzone w celu udostępnienia nowych typów zakresów, takie jak zakresy adresów IP przy użyciu typu inet jako bazy lub zakresy float przy użyciu typu danych float jako bazy.

Typy zakresów obsługują inkluzywne i ekskluzywne granice zakresów, używając odpowiednio znaków i ( ). Na przykład „[4,9)” reprezentuje wszystkie liczby całkowite od 4 włącznie do 9 włącznie.

Typy identyfikatorów obiektów

Typy identyfikatorów obiektów (OID) są używane wewnętrznie przez PostgreSQL jako klucze główne dla różnych tabel systemowych. Jeśli określona jest opcja WITH OIDS lub włączona jest zmienna konfiguracyjna default_with_oids, tylko wtedy OID są dodawane do tabel tworzonych przez użytkownika. Poniższa tabela zawiera listę kilku typów aliasów. Typy aliasów OID nie posiadają własnych operacji, poza wyspecjalizowanymi procedurami wejścia i wyjścia.

.

.

Nazwa		Opis	Wartość Przykład
oid	any	numeryczny Identyfikator obiektu	564182
regproc	pg_proc	nazwa funkcji	suma
regrocedura	pg_proc	funkcja z typami argumentów	sum(int4)
regoper	pg_operator	pg_operator	nazwa operatora	+
regoperator	pg_operator	operator z argumentami typu	*(integer,integer) lub -(NONE,integer)
regclass	pg_class	nazwa relacji	pg_type
regtype	pg_type	nazwa typu danych	integer
regconfig	pg_ts_config	konfiguracja wyszukiwania tekstowego	English
regdictionary	pg_ts_dict	słownik wyszukiwania tekstowego	simple

Typy pseudo

System typów PostgreSQL zawiera szereg specjalnychSystem typów PostgreSQL zawiera wiele specjalnych wpisów, które nazywane są pseudo-typami. Pseudo-typ nie może być użyty jako typ danych kolumny, ale może być użyty do deklaracji typu argumentu lub wyniku funkcji.

Poniższa tabela zawiera listę istniejących pseudo-typów.

S. No.	Nazwa & Opis
1	any Indicates that a function accepts any input data type.
2	anyelement Wskazuje, że funkcja akceptuje dowolny typ danych.
3	anyarray Wskazuje, że funkcja akceptuje dowolny typ danych tablicy.
4	anynonarray Wskazuje, że funkcja akceptuje dowolny typ danych nonarray.
5	anyenum Wskazuje, że funkcja akceptuje dowolny typ danych enum.
6	anyrange Wskazuje, że funkcja akceptuje dowolny typ danych zakresu.
7	cstring Wskazuje, że funkcja akceptuje lub zwraca łańcuch zakończony zerem.
8	internal Indicates that a function accepts or returns a server-internal data type.
9	language_handler Podręcznik wywołania języka proceduralnego jest zadeklarowany do zwracania language_handler.
10	fdw_handler Podręcznik zawijania obcych danych jest zadeklarowany do zwrócenia fdw_handler.
11	record Identyfikuje funkcję zwracającą nieokreślony typ wiersza.
12	trigger Funkcja wyzwalająca jest zadeklarowana jako zwracająca trigger.
13	void Oznacza, że funkcja nie zwraca żadnej wartości.

.