Zarządzanie obiektami

Typy danych


Mamy 6 typów danych w PostgreSQL

  • Znakowe

  • Liczbowe

  • Logiczne

  • Daty i czasu

  • Specjalne typy PostgreSQL

  • BLOB


Typy znakowe


Typy znakowe to:

  • varchar(X) Ciąg znaków zniennej długości o maksymalnej długości wskazanej przez parametr. W przypadku wstawienia ciągu o długości np. 10 znaków do kolumny zdefiniowanej jako varchar(20), ciąg ten nie będzie uzupełniany spacjami – w przeciwieństwie do typu char

  • char(X) Ciąg tekstwoy złożony z X znaków. W przypadku wstawienia ciągu o długości np. 10 znaków do kolumny zdefiniowanej jako char(20) - ciąg zostanie uzupełniony spacjami do 20 znaków. Jeśli przekroczymy maksymalną zdefiniowaną liczbę znaków, ciąg zostanie obcięty beż żadnego komunikatu

  • char Pojedynczy znak

  • text Nieograniczonej długości ciąg tekstowy. Podobny do varchar, z tą różnicą że jest to typ niestandardowy i niezgodny z ANSI. Mogą pojawić się problemy z rzutowaniem w niektórych językach programowania.


Jedna z pierwszych myśli jaka przychodzi mi do głowy odnośnie typów znakowych to pytanie: jaka jest maksymalna długość wstawianych ciągów tekstowych? Teoretycznie ogranicza nas jedynie maksymalna długość wiersza która od wersji 7.1 wynosi 1GB.

Typy liczbowe


Typów liczbowych mamy 7 i są to:


  • smallint – dwubajtowa liczba całkowita (-32 768 do 32 767)

  • int – czterobajtowa liczba całkowita (-2 147 483 648 do 2 147 483 647)

  • serial – czterobajtowa liczba całkowita której wartość jest nadawana automatycznie przez PostgreSQL (dlatego najczęściej jest wykorzystywana do kluczy głównych).

  • Float(X) - ośmiobajtowa liczba zmiennoprzecinkowa o minimalnej precyzji X znaków

  • real – ośmiobajtowa liczba zmiennoprzecinkowa podwójnej precyzji

  • numeric(x,y) – Liczba rzeczywista. Maksymalnej długości X (przy czym X określa całkowitą długość, włącznie z częścią ułamkową), oraz precyzją określoną przez Y

  • money – specyficzny dla PostgreSQL typ danych. Tak naprawdę to numeric(9,2), a służy do przechowywania wartości monetarnych


Zarówno typ float jak i real stosują zaokrąglenia! Nie dotyczy to innych typów.

Typ logiczny – boolean


Typ mogący przechowywać wartość logiczną prawda/fałsz. Ciekawostka: jako prawda zostaną zinterpretowane następujące wartości: TRUE, '1','yes','t','true'. Jako fałsz: FALSE,'0','no','n', 'false','f'. Jeśli zacząłeś się już zastanawiać - to liczby 1 i 0 muszą być objęte znakami ' , bez nich PostgreSQL nie przyjmie 0 ani 1 do typu boolean.


Typy daty i czasu


Typy daty i czasu dostępne w PostgreSQL:


  • Date – przechowuje datę

  • Time – przechowuje czas

  • Timestamp – przechowuje datę i czas

  • Interval – przechowuje różnicę między wartościami typu timestamp


Specjalne typy PostgreSQL i typ Blob


W bazach PostgreSQL są dostępne jeszcze typy takie jak box,line,point,lseq,polygon i odnoszą się one do figur geometrycznych, ale nie są one tematem niniejszego podręcznika – stosowane są np. w PostGis. Typ blob służy do przechowywania danych binarnych, takich jak np. filmy czy zdjęcia.


Tabele


Tworzenie tabel


Tabele tworzymy z użyciem instrukcji CREATE TABLE w taki sposób:


create table przykladowa(

x serial,

y real,

z varchar(50)

);


gdzie x,y,z są nazwami kolumn. Można też utworzyć tabelę w innym schemacie poprzedzając jej nazwę nazwą schematu. Musimy mieć oczywiście prawa do tworzenia obiektów we wskazanym schemacie:


create table nazwa_schematu.przykladowa(

x serial,

y real,

z varchar(50)

);


Ograniczenia kolumn


Dotychczas tworzyliśmy tabele z kolumnami bez jakichkolwiek ograniczeń. Czasem jednak przydaje się np. możliwość wymuszenia wstawiania wartości do wybranej kolumny, lub unikalności wartości. Poniżej zestawienie ograniczeń dostępnych w bazach danych PostgreSQL:

  • NOT NULL – w kolumnie z tym ograniczeniem nie możemy wprowadzać NULL. Zawsze trzeba będzie tu wrzucić jakąś wartość.

  • UNIQUE – wymuszenie unikalności. Specyficzne jest zachowanie w PostgreSQL tego ograniczenia w zestawieniu z NULLami. Zgodnie ze standardem ANSI powinna móc wystąpić tylko jedna wartość NULL w takiej kolumnie, w PostgreSQL nie ma takiego ograniczenia.

  • DEFAULT – pozwala skonfigurować wartość domyślną dla kolumny

  • CHECK(WARUNKI) – Pozwala na sprawdzanie spełnienia określonego warunku logicznego dotyczącego danych podczas modyfikacji lub wprowadzania wierszy

  • PRIMARY KEY – Powoduje założenie klucza głównego na kolumnie.

  • REFERENCES – zakłada klucz obcy

Przykład użycia ograniczeń:


create table dzialy (

id serial primary key,

nazwa varchar

);

create table pracownicy (

id serial primary key,

imie varchar not null,

nazwisko varchar not null,

email varchar not null unique,

pensja float check(pensja>2000),

data_zatrudnienia date default current_date,

dzial int references dzialy(id)

);

Ograniczenia tabel


Niekiedy chcemy zastosować warunek logiczny dla kilku kolumn, albo np. skomponować klucz główny składający się z kilku kolumn. W takich sytuacjach możemy użyć ograniczeń na poziomie tabeli. Przykład:


create table tabelka (

kol1 varchar,

kol2 varchar,

constraint omg_ale_unikalnosc unique(kol1,kol2)

);


Kasowanie tabel


Kasowanie tabel jest bardzo proste i sprowadza się do wydania instrukcji:


drop table nazwa_tabeli;


Tabele tymczasowe


Tabele tymczasowe to tabele które są automatycznie usuwane po zakończeniu sesji. Wykorzystuje się takie cudo np. w procesach ETL. Tworzy się je tak samo jak zwykłe tabele, dodając jedynie słówko „temporary”:


create temporary table tymczasowa (x int,y varchar);


Ograniczenia kluczy obcych


Klucze obce mogą referować wyłącznie do kolumn unikalnych. Wynika to koniecznością jednoznacznego określenia wiersza w tabeli B do którego referuje wiersz z tabeli A. Wyobraźmy sobie że np. mamy tabelę samochody_firmowe i tabelę uzytkownicy. W tabeli samochody_firmowe jest założony klucz obcy do tabeli uzytkownicy, a zawierający ID użytkownika który aktualnie jeździ danym samochodem. Ktoś rozbija firmowego Merca, sprawdzamy kto nim jeździ i widzimy wartość ID=50. Zaglądamy do tabelki uzytkownicy a tam 3 osoby mają taki identyfikator. Kto płaci? ;)


Ograniczenie ON DELETE/UPDATE CASCADE i ON DELETE SET NULL


Wróćmy na moment do naszych przykładowych tabel działy i pracownicy. Pracownicy pracują w działach firmy, a to który w którym determinuje wartość w kolumnie dzia tabeli pracownicy, referującej do kolumny id w tabeli działy. Co powinno się stać z pracownikami kiedy skasujemy dział? Wartości w kolumnie dzial tabeli pracownicy tych pracowników którzy pracują w kasowanym dziale powinny zostać ustawione na NULL, czy też wiersze tych pracowników powinny być skasowane razem z wierszem działu? To zależy od konfiguracji. Domyślnie PostgreSQL nam na to nie pozwoli. W przypadku takiego zapisu:


create table pracownicy (

id serial primary key,

imie varchar not null,

nazwisko varchar not null,

email varchar not null unique,

pensja float check(pensja>2000),

data_zatrudnienia date default current_date,

dzial int references dzialy(id) on delete cascade

);


pracownicy zostaną usunięci razem z działem. W przypadku takiego:


create table pracownicy (

id serial primary key,

imie varchar not null,

nazwisko varchar not null,

email varchar not null unique,

pensja float check(pensja>2000),

data_zatrudnienia date default current_date,

dzial int references dzialy(id) on delete set null

);


wartości kolumny dzial zostaną ustawione na NULL. Ci pracownicy nie będą po prostu przypisani do żadnego działu. Wersja z UPDATE działa podobnie, jednak skasuje wiersze lub ustawi NULL w kolumnie referującej w przypadku zmiany wartości kolumny do której referujemy w drugiej tabeli.


Ograniczenie DEFERRABLE


Z kluczami obcymi wiąże się jeszcze jeden problem. Przypomnijmy sobie tabele pracownicy i działy, oraz łączące je zależności. Wyobraźmy sobie że mamy dział o numerze 10 i pracownika który w nim pracuje. Teraz musimy zmienić numer tego działu na 20. Jeśli spróbujemy najpierw zmienić ID działu, nie uda nam się ponieważ PostgreSQL zauważy że zostałby nam pracownik referujący „w kosmos”. Jeśli spróbujemy najpierw zmienić wartość w kolumnie dział tabeli z pracownikami na 20, też nam się nie uda ponieważ pracownik referowałby do nieistniejącego jeszcze działu. I co teraz? Możemy posłużyć się taką oto konstrukcją:


create table pracownicy (

id serial primary key,

imie varchar not null,

nazwisko varchar not null,

email varchar not null unique,

pensja float check(pensja>2000),

data_zatrudnienia date default current_date,

dzial int references dzialy(id) deferrable

);


Taka konstrukcja pozwoli na naruszenie ograniczenia klucza głównego, ale tylko w ramach transakcji! Update obu tabel będzie musiał być przeprowadzony w ramach jednej transakcji.


Widoki


Widoki są po prostu nazwanymi zapytaniami. Korzystamy z nich tak samo jak z tabel. Można nawet poprzez widoki wstawiać dane do tabel z których korzysta zapytanie na podstawie którego tworzony jest widok. Jedynym ograniczeniem we wstawianiu wierszy poprzez widok jest to by zapytanie nie posiadało : złączeń tabel, agregatów, grupowania, oraz aby wymienione były wszystkie kolumny z ograniczeniem NOT NULL oraz PRIMARY KEY.


create view prac_view as select id,imie,nazwisko,email from pracownicy;

select * from prac_view;

insert into prac_view values (1,'x','y','x@y.pl');


Kasowanie widoków odbywa się z użyciem polecenia:


drop view nazwa_widoku;

Ten artykuł jest elementem poniższych kursów: