PostgreSQL - typ danych

W tym rozdziale omówimy typy danych używane w PostgreSQL. Tworząc tabelę, dla każdej kolumny określasz typ danych, czyli jaki rodzaj danych chcesz przechowywać w polach tabeli.

Daje to kilka korzyści -

• Consistency - Operacje na kolumnach tego samego typu danych dają spójne wyniki i zwykle są najszybsze.

• Validation - Właściwe użycie typów danych oznacza walidację formatu danych i odrzucenie danych poza zakresem typu danych.

• Compactness - Ponieważ kolumna może przechowywać jeden typ wartości, jest przechowywana w zwarty sposób.

• Performance- Właściwe użycie typów danych zapewnia najbardziej wydajne przechowywanie danych. Zapisane wartości mogą być szybko przetwarzane, co zwiększa wydajność.

PostgreSQL obsługuje szeroki zestaw typów danych. Poza tym użytkownicy mogą tworzyć własne niestandardowe typy danych za pomocą polecenia CREATE TYPE SQL. W PostgreSQL istnieją różne kategorie typów danych. Omówiono je poniżej.

Typy liczbowe

Typy liczbowe składają się z dwu-, czterobajtowych i ośmiobajtowych liczb całkowitych, czterobajtowych i ośmiobajtowych liczb zmiennoprzecinkowych oraz miejsc dziesiętnych z możliwością wyboru. W poniższej tabeli wymieniono dostępne typy.

Nazwa	Rozmiar pamięci	Opis	Zasięg
smallint	2 bajty	liczba całkowita z małym zakresem	-32768 do +32767
liczba całkowita	4 bajty	typowy wybór dla liczby całkowitej	-2147483648 do +2147483647
bigint	8 bajtów	duża liczba całkowita	-9223372036854775808 do 9223372036854775807
dziesiętny	zmienna	precyzja określona przez użytkownika, dokładna	do 131072 cyfr przed przecinkiem; do 16383 cyfr po przecinku
numeryczny	zmienna	precyzja określona przez użytkownika, dokładna	do 131072 cyfr przed przecinkiem; do 16383 cyfr po przecinku
real	4 bajty	o zmiennej precyzji, niedokładne	Dokładność do 6 cyfr dziesiętnych
podwójna precyzja	8 bajtów	o zmiennej precyzji, niedokładne	Dokładność do 15 cyfr dziesiętnych
mały seryjny	2 bajty	mała liczba całkowita z autoinkrementacją	1 do 32767
seryjny	4 bajty	autoinkrementacja całkowita	1 do 2147483647
bigserial	8 bajtów	duża liczba całkowita z autoinkrementacją	1 do 9223372036854775807

Rodzaje monetarne

Typ pieniądza przechowuje kwotę waluty ze stałą ułamkową dokładnością. Wartości liczbowych, int i bigint typów danych można rzutować na pieniądze . Używanie liczb zmiennoprzecinkowych nie jest zalecane do obsługi pieniędzy ze względu na możliwość wystąpienia błędów zaokrągleń.

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

Typy postaci

Poniższa tabela zawiera listę typów znaków ogólnego przeznaczenia dostępnych w PostgreSQL.

S. Nie.	Nazwa i opis
1	character varying(n), varchar(n) o zmiennej długości z limitem
2	character(n), char(n) o stałej długości, wyściełane
3	text zmienna nieograniczona długość

Binarne typy danych

Bytea typ danych umożliwia przechowywanie łańcuchów binarnych jak w tabeli podanej poniżej.

Nazwa	Rozmiar pamięci	Opis
bajt	1 lub 4 bajty plus rzeczywisty ciąg binarny	ciąg binarny o zmiennej długości

Typy daty / godziny

PostgreSQL obsługuje pełny zestaw typów daty i czasu SQL, jak pokazano w poniższej tabeli. Daty liczone są zgodnie z kalendarzem gregoriańskim. Tutaj wszystkie typy mają rozdzielczość1 microsecond / 14 digits z wyjątkiem date typ, którego rozdzielczość to day.

Nazwa	Rozmiar pamięci	Opis	Niska wartość	Wysoka wartość
timestamp [(p)] [bez strefy czasowej]	8 bajtów	data i godzina (bez strefy czasowej)	4713 pne	294276 AD
TIMESTAMPTZ	8 bajtów	data i godzina wraz ze strefą czasową	4713 pne	294276 AD
data	4 bajty	data (bez godziny)	4713 pne	5874897 AD
czas [(p)] [bez strefy czasowej]	8 bajtów	pora dnia (bez daty)	00:00:00	24:00:00
czas [(p)] ze strefą czasową	12 bajtów	tylko pory dnia, ze strefą czasową	00: 00: 00 + 1459	24: 00: 00-1459
interwał [pola] [(p)]	12 bajtów	Przedział czasowy	-178000000 lat	178000000 lat

Typ boolowski

PostgreSQL udostępnia standardowy typ SQL Boolean. Typ danych Boolean może mieć stany prawda , fałsz i trzeci stan, nieznany , który jest reprezentowany przez wartość null języka SQL.

Nazwa	Rozmiar pamięci	Opis
boolean	1 bajt	stan prawdy lub fałszu

Typ wyliczeniowy

Typy wyliczeniowe (wyliczeniowe) to typy danych, które zawierają statyczny, uporządkowany zestaw wartości. Odpowiadają one typom wyliczeń obsługiwanym w wielu językach programowania.

W przeciwieństwie do innych typów, typy wyliczeniowe należy tworzyć za pomocą polecenia CREATE TYPE. Ten typ służy do przechowywania statycznego, uporządkowanego zestawu wartości. Na przykład kierunki z kompasu, tj. PÓŁNOC, POŁUDNIE, WSCHÓD i ZACHÓD lub dni tygodnia, jak pokazano poniżej -

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

Wyliczone, raz utworzone, mogą być używane jak każdego innego typu.

Typ geometryczny

Geometryczne typy danych reprezentują dwuwymiarowe obiekty przestrzenne. Najbardziej podstawowy typ, punkt, stanowi podstawę wszystkich innych typów.

Nazwa	Rozmiar pamięci	Reprezentacja	Opis
punkt	16 bajtów	Wskaż samolot	(x, y)
linia	32 bajty	Nieskończona linia (nie w pełni zaimplementowana)	((x1, y1), (x2, y2))
lseg	32 bajty	Segment linii skończonej	((x1, y1), (x2, y2))
pudełko	32 bajty	Prostokątne pudełko	((x1, y1), (x2, y2))
ścieżka	16 + 16n bajtów	Zamknięta ścieżka (podobnie do wielokąta)	((x1, y1), ...)
ścieżka	16 + 16n bajtów	Otwarta ścieżka	[(x1, y1), ...]
wielokąt	40 + 16n	Wielokąt (podobny do zamkniętej ścieżki)	((x1, y1), ...)
okrąg	24 bajty	okrąg	<(x, y), r> (punkt środkowy i promień)

Typ adresu sieciowego

PostgreSQL oferuje typy danych do przechowywania adresów IPv4, IPv6 i MAC. Lepiej jest używać tych typów zamiast zwykłego tekstu do przechowywania adresów sieciowych, ponieważ te typy oferują sprawdzanie 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 bajtów	Hosty i sieci IPv4 i IPv6
macaddr	6 bajtów	Adresy MAC

Typ ciągu bitów

Typy ciągów bitowych służą do przechowywania masek bitowych. Są to 0 lub 1. Istnieją dwa typy bitów SQL:bit(n) i bit varying(n), gdzie n jest dodatnią liczbą całkowitą.

Typ wyszukiwania tekstowego

Ten typ obsługuje wyszukiwanie pełnotekstowe, czyli przeszukiwanie kolekcji dokumentów w języku naturalnym w celu zlokalizowania tych, które najlepiej pasują do zapytania. Istnieją dwa typy danych do tego -

S. Nie.	Nazwa i opis
1	tsvector Jest to posortowana lista odrębnych słów, które zostały znormalizowane w celu scalenia różnych wariantów tego samego słowa, zwanych „leksemami”.
2	tsquery Przechowuje leksemy, które mają być wyszukiwane, i łączy je z zachowaniem operatorów boolowskich & (AND), | (Albo i ! (NIE). Aby wymusić grupowanie operatorów, można użyć nawiasów.

Typ UUID

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

Przykład identyfikatora UUID to - 550e8400-e29b-41d4-a716-446655440000

Typ XML

Typ danych XML może służyć do przechowywania danych XML. Aby przechowywać dane XML, najpierw musisz 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

Json typu dane mogą być używane do przechowywania JSON (JavaScript Object Notation) dane. Takie dane mogą być również przechowywane jako tekst , ale typ danych json ma tę zaletę, że sprawdza, czy każda przechowywana wartość jest prawidłową wartością JSON. Dostępne są również powiązane funkcje pomocnicze, których można użyć bezpośrednio do obsługi typu danych JSON w następujący sposób.

Przykład	Przykładowy wynik
array_to_json ('{{1,5}, {99,100}}' :: int [])	[[1,5], [99,100]]
row_to_json (row (1, 'foo'))	{"f1": 1, "f2": "foo"}

Typ tablicy

PostgreSQL daje możliwość zdefiniowania kolumny tabeli jako wielowymiarowej tablicy o zmiennej długości. Można tworzyć tablice dowolnego wbudowanego lub zdefiniowanego przez użytkownika typu podstawowego, typu wyliczeniowego lub typu złożonego.

Deklaracja tablic

Typ tablicy można zadeklarować jako

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

lub używając słowa kluczowego „ARRAY” jako

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

Wstawianie wartości

Wartości tablicowe można wstawiać jako stałą literału, umieszczając wartości elementów w nawiasach klamrowych i oddzielając je przecinkami. Przykład pokazano 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 przedstawiono poniżej. Polecenie podane poniżej wyłoni osoby, których oszczędności są większe w drugim kwartale niż w czwartym.

SELECT name FROM monhly_savings WHERE saving_per_quarter[2] > saving_per_quarter[4];

Modyfikowanie tablic

Przykład modyfikacji tablic 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 = ARRAY[25000,25000,27000,27000]
WHERE name = 'Manisha';

Przeszukiwanie tablic

Przykład wyszukiwania tablic przedstawiono poniżej.

SELECT * FROM monthly_savings WHERE saving_per_quarter[1] = 10000 OR
saving_per_quarter[2] = 10000 OR
saving_per_quarter[3] = 10000 OR
saving_per_quarter[4] = 10000;

Jeśli rozmiar tablicy jest znany, można skorzystać z 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

Ten typ reprezentuje listę nazw pól i ich typów danych, tj. Strukturę wiersza lub rekord tabeli.

Deklaracja typów złożonych

Poniższy przykład pokazuje, jak zadeklarować typ złożony

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

Ten typ danych może być używany w tworzeniu tabel, jak poniżej -

CREATE TABLE on_hand (
   item inventory_item,
   count integer
);

Wejście wartości złożonej

Wartości złożone można wstawiać jako stałą literału, umieszczając wartości pól w nawiasach i oddzielając je przecinkami. Przykład pokazano poniżej -

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

Dotyczy to elementu zapasów zdefiniowanego powyżej. Słowo kluczowe ROW jest w rzeczywistości opcjonalne, o ile w wyrażeniu występuje więcej niż jedno pole.

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 wybieranie pola z nazwy tabeli. Na przykład, aby wybrać niektóre podpola z naszej przykładowej tabeli on_hand, zapytanie wyglądałoby tak, jak pokazano 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 z wieloma tabelami), na przykład:

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 używają zakresu danych. Typ zakresu może obejmować zakresy dyskretne (np. Wszystkie wartości całkowite od 1 do 10) lub zakresy ciągłe (np. Dowolny punkt w czasie między 10:00 a 11:00).

Dostępne wbudowane typy zakresów obejmują następujące zakresy -

• int4range - zakres liczby całkowitej

• int8range - Gama bigintów

• numrange - zakres liczbowy

• tsrange - Zakres sygnatury czasowej bez strefy czasowej

• tstzrange - Zakres datownika ze strefą czasową

• daterange - Zakres dat

Można tworzyć niestandardowe typy zakresów, aby udostępniać nowe typy zakresów, takie jak zakresy adresów IP przy użyciu typu inet jako podstawy lub zakresy zmiennoprzecinkowe przy użyciu typu danych zmiennoprzecinkowych jako podstawy.

Typy zakresów obsługują włączające i wyłączne granice zakresu przy użyciu odpowiednio znaków [] i (). Na przykład „[4,9)” oznacza wszystkie liczby całkowite zaczynające się od 4 i włącznie, aż do 9, ale z wyłączeniem.

Typy identyfikatorów obiektów

Identyfikatory obiektów (OID) są używane wewnętrznie przez PostgreSQL jako klucze podstawowe dla różnych tabel systemowych. Jeśli określono WITH OIDS lub zmienna konfiguracyjna default_with_oids jest włączona, tylko w takich przypadkach OIDy są dodawane do tabel utworzonych przez użytkownika. W poniższej tabeli wymieniono kilka typów aliasów. Typy aliasów OID nie mają własnych operacji, z wyjątkiem wyspecjalizowanych procedur wejścia i wyjścia.

Nazwa	Bibliografia	Opis	Przykład wartości
oid	każdy	numeryczny identyfikator obiektu	564182
regproc	pg_proc	nazwa funkcji	suma
regprocedure	pg_proc	funkcja z typami argumentów	suma (int4)
regoper	pg_operator	imię operatora	+
regoperator	pg_operator	operator z typami argumentów	* (liczba całkowita, liczba całkowita) lub - (NONE, liczba całkowita)
regclass	pg_class	nazwa relacji	pg_type
regtype	pg_type	nazwa typu danych	liczba całkowita
regconfig	pg_ts_config	konfiguracja wyszukiwania tekstu	język angielski
regdictionary	pg_ts_dict	słownik wyszukiwania tekstu	prosty

Pseudo typy

System typów PostgreSQL zawiera szereg wpisów specjalnego przeznaczenia, które są zbiorczo nazywane pseudo typami. Pseudo-typ nie może być używany jako typ danych kolumny, ale może służyć do deklarowania argumentu funkcji lub typu wyniku.

Poniższa tabela zawiera listę istniejących pseudotypów.

S. Nie.	Nazwa i opis
1	any Wskazuje, że funkcja akceptuje dowolny typ danych wejściowych.
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 niebędący tablicą.
5	anyenum Wskazuje, że funkcja akceptuje dowolny typ danych wyliczenia.
6	anyrange Wskazuje, że funkcja akceptuje dowolny typ danych zakresu.
7	cstring Wskazuje, że funkcja akceptuje lub zwraca ciąg znaków w języku C zakończony wartością null.
8	internal Wskazuje, że funkcja akceptuje lub zwraca wewnętrzny typ danych serwera.
9	language_handler Zadeklarowano procedurę obsługi wywołań języka proceduralnego w celu zwrócenia języka language_handler.
10	fdw_handler Zadeklarowano, że program obsługi opakowania danych obcych zwraca fdw_handler.
11	record Identyfikuje funkcję zwracającą nieokreślony typ wiersza.
12	trigger Zadeklarowano, że funkcja wyzwalacza zwraca wyzwalacz.
13	void Wskazuje, że funkcja nie zwraca żadnej wartości.