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.