• Oficjalny site: http://www.postgresql.org .

• Mirror w Polsce, np.: http://pgsql.profnet.pl/ lub ftp://sunsite.icm.edu.pl/site/postgres .

Na 12 kwietnia 2002: 7.2.1

Bardzo dużo. Implementacja standardu SQL dużo pełniejsza niż w konkurencyjnym MySQL'u. Ciut wolniejszy przy prostych bazach i małej ilości jednoczesnych użytkowników, ale "dostaje skrzydeł" przy większej komplikacji danych (duża ilość joinów) i dużej ilości jendoczesnych połączeń.

Jednyną realna szansą na zrobienie czegoś takiego jest zamiana całej tabeli. Przykład:

CREATE TABLE t (a text, b int4, c int4);

aby pole (b) usunąć:

SELECT a,c into temp_table FROM t;
drop table t;
alter table temp_table rename to t;

aby zmienić typ pola (c):

select a, b, c::int8 as c into temp_table from t;
drop table t;
alter table temp_table rename to t;

W źródłach postgresql'a jest możliwość włączenia funcjonalności "DROP COLUMN", ale jest to kod bardzo "devel" i jako jaki nie powinien być używany w bazach danych działających produkcyjnie.

PL/PGSQL jest proceduralnym językiem rozszerzającym funkcjonalność postgres'a o warunki, pętle, zmienne itp.

Domyślnie nie jest on obługiwany. Aby włączyć możliwość tworzenia w nim funkcji należy:

• Stworzyć tzw. handler języka.

  Robi się to w ten sposób:

  CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS '/usr/local/pgsql/lib/plpgsql.so' LANGUAGE 'C';
  

  ewentualnie zamiast '/usr/local/pgsql/lib/plpgsql.so' należy podać właściwą ścieżkę do pliku plpgsql.so.

• Następnie należy zadeklarować język:

  CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER plpgsql_call_handler LANCOMPILER 'PL/pgSQL';
  

I od tej pory można już korzystać z dodatkowej funkcjonalności jaką dają procedury składowane.

Zaleca się użycie powyższych dwóch komend będąc przyłączonym do bazy "template1". Dzięki temu, każda następna stworzona baza będzie miała od razu zadeklarowany język plpgsql.

Niestety dokumentacji na razie nie ma. Można co najwyżej poczytać kody funkcji pl/pgsql użyte do tzw. regression test. Kody te są w pliku plpgsql.sql w katalogu "src/test/regress/sql/" względem głównego katalogu źródeł postgresa - testy te nie są standardowo instalowane w procesie instalacji binarek!

Częściowo tak: istnieje coś takiego jak "Large Object". Każdy zapisany tak "blob" jest zapisywany na dysk do oddzielnego pliku a w tabeli są tylko i wyłącznie odnośniki do plików.

Należy wziąć pod uwagę, że nie istnieje w tej chwili możliwość łatwego przeszukiwania czy indeksowania zawartości blobów.

Od wersji 7.1 jest możliwość przechowywania w tabelach danych o dowolnej wielkości (technologia T.O.A.S.T.).

Pozostaje tylko pytanie: czy na pewno przechowywanie dużych, nieindeksowalnych plików w bazie danych jest słusznym rozwiązaniem?

Aby w psql'u działała obsługa klawiszy kursora musisz mieć bibliotekę readline. Jeśli dodatkowo samodzielnie kompilujesz PostgreSQLa ze źródeł to (przynajmniej w czasie kompilacji) musisz posiadać także readline-devel. Jeśli po dograniu biblioteki do systemu nadal nie możesz używać klawiszy kursora musisz przekompilować PostgreSQLa. Po samodzielnym kompilowaniu możesz zajrzeć do pliku config.cache, gdzie powinny być takie wpisy:

(pgdba@depeszws[tty4]) 10:31:15 [~/src]
$ cat config.cache | grep readline
ac_cv_header_readline_history_h=${ac_cv_header_readline_history_h=yes}
ac_cv_header_readline_readline_h=${ac_cv_header_readline_readline_h=yes}
ac_cv_search_readline=${ac_cv_search_readline=-lreadline}

Podstawową sprawą jest to by nie korzystać z systemu z konta roota. To raz. Natomiast co do samego problemu. System kont PostgreSQLa jest całkowicie oddzielony od systemu kont maszyny na której baza danych pracuje. Najważniejszą sprawą jest, że "główne" - czyli administracyjne konto w PostgreSQLu to nie jest root tylko postgres lub pgdba lub podobnie - nazwa jest identyczna z nazwą użytkownika systemu z którego był uruchomiony initdb (zazwyczaj jest to ten sam użytkownik z którego działa proces postmastera). Czyli aby wejść do psql'a z konta roota musisz wydać polecenie:

(root@depeszws[tty4]) 10:40:54 [~]
$ psql -U pgdba

Dodatkowo pamiętaj, że psql domyślnie stara się podłączyć do bazy o nazwie takiej samej jak baza użytkownika, a sam fakt założenia użytkownika nie oznacza, że istnieje już dla niego baza.

Sposób autoryzacji (czy ma pytać o hasła czy nie) jest definiowany w pliku pg_hba.conf znajdującym się w katalogu z danymi PostgreSQLa. Na końcu tego pliku znajdziecie dwie takie linie:

local all trust
host all 127.0.0.1 255.255.255.255 trust

Oznaczają one, że

• dla połączeń lokalnych (local) - czyli takich gdzie łączymy się nie korzystająć z socketów tcp/ip, dla dowolnej bazy (all) system ma przyjąć regułę niepytania o hasło (trust).
• dla połączeń zdalnych, ale tylko z maszyny o adresie 127.0.0.1 (czyli z tego samego komputera, ale przez sockety tcp/ip), też będzie ta sama reguła.

Aby to zmienić należy zmienić ostatnie słowo (trust) na "password" lub "crypt" (różnią się one metodą przesyłania hasła. Np. zapis:

host all 148.81.17.60 255.255.255.0 password

Oznacza, że osoby łączące się z serwera o ip 148.81.17.60 oraz z całej klasy C (czyli naprawdę adres ip musi być 148.81.17.*) muszą podać hasło aby dostać się do dowolnej bazy.

A zapis:

host template1 148.81.17.60 255.255.255.0 password

(oczywiście bez poprzedniego zapisu) oznacza, że te same osoby będą mogły teraz dostać się tylko do bazy template1 i będą musiały podać hasło.

Przy pisaniu reguł dostępu należy pamiętać o kolejności. Użyta zostanie ta reguła która jest pierwsza w pliku i pasuje do sytuacji. Dlatego zapis ten jest bez sensu:

local all trust
host all 127.0.0.1 255.255.255.255 trust
local template1 password
host template1 127.0.0.1 255.255.255.255 password

a aby uzyskać to co się chce zapis powinien być taki:

local template1 password
host template1 127.0.0.1 255.255.255.255 password
local all trust
host all 127.0.0.1 255.255.255.255 trust

Wbrew wszelkim obawom uzyskanie prawidłowej obsługi polskich liter jest trywialne. Aha. poniższy tekst tyczy się tylko i wyłącznie środowisk U*IX'owych - w windows jest to jakaś magia.

Przede wszystkim czego nie potrzebujesz:

• obsługi "multibyte"
• obsługi Unicode
• definiowania "ENCODING'u" przy każdym CREATE DATABASE

Potrzebujesz za to:

• "wsparcia" locale w postgresie
• zainstalowanego prawidłowego locale w systemie

Najprostszą i najskuteczniejszą metodą jest własnoręczna kompilacja. Czemu? proste: nigdy nie mamy pewności z jakimi opcjami zostało skompilowane to co dostajemy w paczce z binarkami. Przebiegu kompilacji nie będę opisywał (każdy chyba czytał plik INSTALL???), ale:

na etapie kompilacji musimy włączyć locale. robi się to tak:

./configure --enable-locale

i to w zasadzie wszystko. oczywiście configre'owi można podać także stado innych parametrów, ale z punktu widzenia przeciętnego zjadacza ogonków to co powyżej jest tym najistotniejszym. Po kompilacji i zainstalowaniu czas na "initdb". już w tym momencie polecam mieć ustawione locale (do wersji 7.1 locale trzeba było mieć ustawione tylko w czasie uruchamiania postmastera, ale w 7.1 i później należy locale ustawiać przy initdb) czyli:

export LC_COLLATE=pl_PL
export LC_CTYPE=pl_PL
export LC_MESSAGES=pl_PL
export LC_MONETARY=pl_PL
export LC_NUMERIC=pl_PL
export LC_TIME=pl_PL

Powyższe zadziała jak używasz bash'a lub czegoś kompatybilnego. Jeśli używasz innego shella - poradź się swojego lokalnego guru jak się w nim ustawia zmienne środowiskowe.

Ponieważ zmienne te się ogólnie rzecz biorąc przydają także po initdb polecam wpisanie powyższego do pliku ~/.bash_profile lub ~/.bashrc lub odpowiedniego - wywoływanego przy zalogowaniu się na konto.

Po wykonaniu initdb z ustawionymi zmiennymi LC_* i wystartowaniu postmastera (zmienne LC_* nadal powinny być ustawione) wszystko powinno działać jak trzeba.

Pełno informacji na ten temat znajdziesz na tej stronie . Sam Windows nie używam. Jak chcesz aby było tu coś więcej na ten temat - napisz i podeślij.

Tu możesz ściągnąć gotową, skompilowaną wersję PostgreSQL'a dla Windows. Pliku tu umieszczone są przygotowane przez Ronalda Kuczka .

Aby stworzyć pole typu AUTO_INCREMENT należy zastosować jedną z dwóch metod:

1. SERIAL
2. SEKWENCJA (SEQUENCE)

Jak się używa? SERIAL:

depesz=# create table a (id serial, pole text);
NOTICE: CREATE TABLE will create implicit sequence 'a_id_seq' for SERIAL column 'a.id'
NOTICE: CREATE TABLE/UNIQUE will create implicit index 'a_id_key' for table 'a'
CREATE
depesz=# insert into a (pole) values ('wartość pierwsza');
INSERT 529488 1
depesz=# insert into a (pole) values ('wartość druga');
INSERT 529489 1
depesz=# select * from a;
id | pole
----+------------------
1 | wartość pierwsza
2 | wartość druga
(2 rows)

SEQUENCE:

depesz=# CREATE SEQUENCE b_id;
CREATE
depesz=# create table b (id int4 not null default nextval('b_id'), pole text);
CREATE
depesz=# insert into b (pole) values ('wartość pierwsza');
INSERT 529536 1
depesz=# insert into b (pole) values ('wartość druga');
INSERT 529537 1
depesz=# select * from b;
id | pole
----+------------------
1 | wartość pierwsza
2 | wartość druga
(2 rows)

Czyli jak widać obie metody dają mniej więcej to samo. W dodatku wewnętrznie użycie typu serial powoduje zdefiniowanie sekwencji i stworzenia bardzo podobnej struktury jak to co pokazałemw drugim przykładzie. Jakie są więc różnice?

• serial definiuje automatycznie indeks unikalny na polu
• w serialu nie mamy wpływu na nazwę utworzonej sekwencji
• w serialu nie możemy wpływać na inne parametry sekwencji (skok, wartość początkowa czy końcowa)

Tak więc: jeśli potrzebujesz tylko licznika od 1 do około 2 miliardów: używaj seriala. Jeśli jednak potrzebujesz czasem mieć licznik liczący w dół (od -1 do -dwóch miliardów), albo przeskakujący co dwie lub więcej liczb to używaj sekwencji.

Nie zapomnij jednak, że niezależnie czego użyjesz w obu przypadkach powstaje sekwencja, która nie zniknie po usunięciu tabeli.

Zakładając, że jest select typu:

SELECT * FROM tabela;

Aby móc pobierać wiersze porcjami należy po pierwsze zdefiniować sposób sortowania. W najprostszej wersji będzie to:

SELECT * FROM tabela ORDER BY OID;

w innych np.:

SELECT * FROM tabela ORDER BY pole;

Następnie definiujemy ile chcemy wierszy uzyskać:

SELECT * FROM tabela ORDER BY pole LIMIT 5;

Wyciągnie to z bazy 5 pierwszych rekordów. Aby wyciągnąć kolejne rekordy należy zrobić:

SELECT * FROM tabela ORDER BY pole LIMIT 5 OFFSET 5;

Wyświetli to pięć rekordów poczynając od rekordu o numerze 6 (pominie 5).

Bez stosowania żadnych sztuczek możliwe jest tylko i wyłączenie odebranie innym użytkownikom praw do odczytu zapisu itp. istniejących tabel, natomiast nie można zakazać im w ogóle dostępu do baz innych użytkowników - oraz np. zakładania tam własnych tabel.

O ile w/g mnie nie jest to problem, o tyle części ludzi to przeszkadza. Skonfigurowanie tego polega na zmianie zawartości pliku pg_hba.conf i pg_ident.conf. Przykład (pg_hba.conf):

local all reject
local sameuser password
host sameuser 127.0.0.1 255.255.255.255 password
host all 127.0.0.1 255.255.255.255 ident postgres

Przykład (pg_ident.conf):

#MAP IDENT POSTGRES USERNAME
postgres postgres postgres

Po kolei (plik pg_hba.conf):

• linia 1: blokuje dostęp z localhosta (nie powinna być potrzebna, ale nie zaszkodzi)
• linia 2: zapewnia, że użytkownicy z localhosta będą się mogli dostać tylko do swojej bazy (sameuser) i to po podaniu hasła
• linia 3: to samo co linia 2, ale przez tcp/ip
• linia 4: zapewnia dostęp do wszystkich baz dla użytkowników zdefiniowanych w idencie pod mapą "postgres"

Wpis w pliku pg_ident.conf oznacza, że mapa postgres definiuje, że użytkownik łączący się z konta postgres będzie widziany wewnętrznie jako konto postgres.

I już. Powinno działać.

Powyższe rozwiązanie (nieprzetestowane przeze mnie) przetłumaczyłem (w skrócie) z listu na grupę pgsql-admin (autor: jkm@patriot.net (Kevin McFadden))

aby dostać czas w postaci unix-timestamp, należy odczytać go (czas) w przez:

# select extract(epoch from now());
date_part
------------
1003126751
(1 row)

natomiast aby przekształcić z powrotem taki timestamp na datetime postgresql'owy wystarczy wiedzieć (pamiętać), że unix-timestamp jest to po prostu liczba sekund od "epoch". czyli poniższe zapytanie zadziała dokładnie tak jak chcemy:

# select 'epoch'::datetime + interval('999999999 seconds');
?column?
------------------------
2001-09-09 03:46:39+02
(1 row)

do wersji 7.1.x jedyną możliwością było tworzenie tabeli tymczasowej, insert do niej i odczyt z zewnątrz. brzydkie. od wersji 7.2 istnieje możliwość zwracania z funkcji pl/pgsql'owych kursorów. oczywiście wymaga to używania transakcji (kursory działają tylko wewnątrz transakcji), ale nie jest to przecież wielki problem. czyż nie? zaczynamy:

robimy tabelę:

# CREATE TABLE tabela (
# id INT4,
# name TEXT
# );
CREATE

wstawiamy tam kilka testowych rekordów:

# INSERT INTO tabela VALUES (1, 'bart');
INSERT 48525 1
# INSERT INTO tabela VALUES (2, 'lisa');
INSERT 48526 1
# INSERT INTO tabela VALUES (3, 'marge');
INSERT 48527 1
# INSERT INTO tabela VALUES (4, 'homer');
INSERT 48528 1
# INSERT INTO tabela VALUES (5, 'maggie');
INSERT 48529 1

no i nasza magiczna funkcja:

# CREATE FUNCTION funkcja(INTEGER) RETURNS REFCURSOR AS '
# DECLARE
# num ALIAS FOR $1;
# cur REFCURSOR;
# BEGIN
# cur:=''new_cursor'';
# OPEN cur FOR SELECT * FROM tabela WHERE id >= num ORDER BY id;
# RETURN cur;
# END;
# ' LANGUAGE 'plpgsql';

(oczywiście nazwa kursora (tu: new_cursor) może być dowolna).

no to testujemy:

# BEGIN;
BEGIN
# select funkcja(3);
funkcja
------------
new_cursor
(1 row)

# fetch from new_cursor;
id | name
----+-------
3 | marge
(1 row)

# fetch from new_cursor;
id | name
----+-------
4 | homer
(1 row)

# fetch backward 2 from new_cursor;
id | name
----+-------
3 | marge
(1 row)

# fetch ALL from new_cursor;
id | name
----+--------
3 | marge
4 | homer
5 | maggie
(3 rows)

# close new_cursor;
CLOSE
# end;
COMMIT

działa. oczywiście (jak już nadmieniałem) działa to tylko w postgresie 7.2 i wyższych.

powyższy kod jest autorstwa Tomka Zielonki - ja naniosłem tam tylko drobne poprawki (możliwość zwracania kursora bez przekazywania go jako parametru).

Aby wylistować tabele (widoki, sekwencje, funkcje) należy użyć w psql'u komendy \d lub \d<parametr> gdzie parametr to np. t, s, v, S, f czy inne.

Jeśli natomiast potrzebujesz zrobić to ze swojego programu bez pośrednictwa psql'a, to uruchom psql'a z opcją -E po czym wydaj interesującą cię komendę.

Poza wynikiem działania komendy dostaniesz także zapytanie jakie jest wysyłane do bazy aby pokazać ci te informacje. np:

$ psql template1 -E
Password:
********* QUERY **********
SELECT usesuper FROM pg_user WHERE usename = 'pgdba'
**************************

Welcome to psql, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit

template1=# \d
********* QUERY **********
SELECT c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",
u.usename as "Owner"
FROM pg_class c LEFT JOIN pg_user u ON c.relowner = u.usesysid
WHERE c.relkind IN ('r','v','S','')
AND c.relname !~ '^pg_'
ORDER BY 1;
**************************

List of relations
Name | Type | Owner
------------+------+--------
hdl_custom | view | depesz
(1 row)

template1=#

Należy użyć komendy

ALTER TABLE tabela RENAME COLUMN nazwa_pola TO nowa_nazwa_pola;

Aby wyeksportować bazę danych do pliku .sql wystarczy wydac polecenie:

pg_dump <nazwa_bazy_danych> > dump.sql

Oczywiście w trakcie wykonywania tej operacji trzeba mieć dostęp do bazy danych na poziomie administratora.

Aby natomiast skopiować tak wyeksportowane dane do innej bazy wystarczy:

1. stworzyć docelową bazę danych: CREATE DATABASE docelowa_baza;
2. wykonać polecenie: psql -i dump.sql

To wszystko. Po tym baza powinna być już skopiowana.

Zakładając, że masz już indeks na polu które jest typu INT2 lub INT8, wykonujesz regularne vacuum analyze, danych w tabeli jest odpowiednio dużo i postgresql nadal nie wykorzystuje index scanów przy wyszukiwaniu:

SELECT * FROM tabela WHERE pole = 12;

musisz wymusić na nim odpowiednie rzutowanie podanej liczby (12). Robi się to tak:

SELECT * FROM tabela WHERE pole = 12::int8;

lub tak:

SELECT * FROM tabela WHERE pole = CAST(12 as int8);

Przyczyna takiego zachowania postgresa leży w fakcie, że domyślnym typem danych na jaki są rzutowane podane liczby jest int4. Przy polu typu int2 czy int8 musi zostać zrobiona później odpowiednia konwersja, a liczby skonwertowane nie są już indeksowane.

Poniższy tekst jest kompilacją wypowiedzi poniższych osób na grupie dyskusyjnej pl.comp.bazy-danych w marcu 2002:

• <newbie_2002 at poczta.onet.pl>
• "Sławomir Szyszło" <slaszysz at poczta.onet.pl>
• "Ronald Kuczek" <ronald at breitenbach.pl>
• "Sylwester Szady" <sszady at zke.com.pl>

Aby przenieść dane z plików .dbf do PostgreSQL'a można się posłużyć jedną z poniższych metod:

1. Tak wszystkiego, to się chyba nie da opisać w paru słowach. Jak dla mnie kroki to:

   1. konwersja do pliku tekstowego *.csv (np z EXCELA) - czyli plik tekstowy z polami dzielonymi ";"
   2. obróbka pod vi do pliku sql [- jak się uprzeć to można to zrobić pod EXCELEM poprzez zamiane wartości poszczególnych kolumn] (do postaci każdy wiersz przekształcony jako: insert into tabela values (coś,coś,cośtamjeszcze..); )
   3. utworzenie bazy pod psql-em albo pgAdmin II (pod Windows)
   4. utworzenie struktury tabeli z odpowiednimi polami; lepiej z pgAdmin II
   5. zapuszczenie pliku sql.

2. Pg2Xbase - program do konwersji tabel PostgreSQL do DBF i odwrotnie

   http://www.klaban.torun.pl/prog/pg2xbase/

3. dbfdump - dumps/updates xBase files in human-readable, NoSQL, or PostgreSQL script forms

   http://sourceforge.net/projects/dbfdump/
4. Access->Import z dbf, potem PGAdmin->Import z Accessa (bezposrednio przez *.mdb).
5. ODBC, PGADmin->Import z ODBC.
6. Access->Polacz tabele przez ODBC->Exportuj tabele do PostgreSQL przez ODBC.
7. Datapump (Borland).
8. własny programik w Delphi/BCB/VC++
9. http://www.but.pl/docs/pgsql-pl/PostgreSQL-HOWTO.pl-13.html#ss13.27

ze swojej (depesza) strony mogę powiedzieć, że gdybym potrzebował coś takiego zrobić to przypuszczalnie napisałbym programik w perlu przy użyciu biblioteki DBD::Xbase, wyciągnął dane, zapisał jako INSERTy w pliku .sql i wykonał przez psql'a.

Jeśli chcesz po prostu sprawdzić, czy dane pole jest równe jakiemuś stringowi, ale bez sprawdzania wielkości liter to zrób tak:

CREATE INDEX nazwa_indeksu ON nazwa_tabeli (upper(nazwa_pola));

i potem wyszukiwanie:

SELECT * FROM nazwa_tabeli WHERE upper(nazwa_pola) = upper('jakiś ciąg znaków');

Jeśli natomiast chcesz wyszukiwać używając operatorów LIKE lub ~ (tylda - do wyrażeń regularnych); to użyj ich wersji odpornych na wielkość liter:

SELECT * FROM nazwa_tabeli WHERE nazwa_pola ILIKE 'jakiś%';
SELECT * FROM nazwa_tabeli WHERE nazwa_pola ~* '^jakiś';

Należy jedynie zwrócić uwagę na to, że operator ILIKE pojawił się dopiero od wersji 7.1 PostgreSQL'a, dla wersji wcześniejszych należy używać albo operatora ~* lub funkcji upper/lower.

Pełną lista programów wspierających graficzne tworzenie baz danych (UML) można znaleźć na techdocs'ach . Ze swojej strony polecam program dia , którego używam od dłuższego czasu i sprawdza się bardzo dobrze. Napisałem dla niego mały program , który przekształca pliki .dia (ale tylko nieskompresowane) na plik typu .sql zawierający ładnie (?) sformatowane polecenia tworzące bazę. Jeśli jesteś zainteresowany tym programikiem to informuję: wymaga perla i bibliotek perlowych: Data::Dumper i XML::Parse.