Export danych z PostgreSQL-a

Pisałam już o sposobie export-u danych z MySQL (tego posta możecie znaleźć tutaj), dlatego przyszedł teraz czas na PostgreSQL'a.

Chce zwrócić waszą uwagę na dwa polecenia:

• psql - program klienta do połączenia z serwerem
• polecenie SQL: COPY - do kopiowania danych pomiędzy plikiem a tabelą

Program psql

Opcje programu psql przy exporcie danych:

• -c, --command=ZAPYTANIE - wykonuje zapytanie i kończy połączenie z serwerem
• -f, --file=ŚCIEŻKA_DO_PLIKU - możecie także wykonać zapytanie, które jest zapisane do pliku
• -o, --output=ŚCIEŻKA_DO_PLIKU - wynik zapisujemy w podanym pliku
• -H, --html - wynik zapisany jest w formie html-a
• -A, --no-align - dane nie są wyrównywane. Dane w kolumnach są oddzielane domyślnie znakiem pipe: "|". 
• -x, --expanded - dane są wyświetlane wertykalnie (pionowo)
• -t, --tuples-only - tylko dane są zwracane (bez informacji o nazwie kolumn)
• -F, --field-separator=ZNAK - dane w kolumnach są oddzielne nowo zdefiniowanym separatorem
• -R, --record-separator=ZNAK - rekord jest odseparowany przez znak ZNAK, domyślnie jest to znak nowej linii

Przykłady:

∴ /opt/PostgreSQL/9.1/bin/psql -h127.0.0.1 -p7432 -Upostgres --command="select * from test" test
Password for user postgres: 
 c1 |  c2   |             c3             
----+-------+----------------------------
  1 | test  | 2014-01-07 13:30:23.820911
  2 | test2 | 2014-01-07 13:30:29.892732
  3 | test3 | 2014-01-07 13:30:36.644758
  4 | test4 | 2014-01-07 13:30:43.048741
(4 rows)

∴ /opt/PostgreSQL/9.1/bin/psql -h127.0.0.1 -p7432 -Upostgres --command="select * from test" --no-align test
Password for user postgres: 
c1|c2|c3
1|test|2014-01-07 13:30:23.820911
2|test2|2014-01-07 13:30:29.892732
3|test3|2014-01-07 13:30:36.644758
4|test4|2014-01-07 13:30:43.048741
(4 rows)

∴ /opt/PostgreSQL/9.1/bin/psql -h127.0.0.1 -p7432 -Upostgres --command="select * from test" --expanded test
Password for user postgres: 
-[ RECORD 1 ]------------------
c1 | 1
c2 | test
c3 | 2014-01-07 13:30:23.820911
-[ RECORD 2 ]------------------
c1 | 2
c2 | test2
c3 | 2014-01-07 13:30:29.892732
-[ RECORD 3 ]------------------
c1 | 3
c2 | test3
c3 | 2014-01-07 13:30:36.644758
-[ RECORD 4 ]------------------
c1 | 4
c2 | test4
c3 | 2014-01-07 13:30:43.048741

∴ /opt/PostgreSQL/9.1/bin/psql -h127.0.0.1 -p7432 -Upostgres --command="select * from test" --no-align --expanded test
Password for user postgres: 
c1|1
c2|test
c3|2014-01-07 13:30:23.820911

c1|2
c2|test2
c3|2014-01-07 13:30:29.892732

c1|3
c2|test3
c3|2014-01-07 13:30:36.644758

c1|4
c2|test4
c3|2014-01-07 13:30:43.048741

∴ /opt/PostgreSQL/9.1/bin/psql -h127.0.0.1 -p7432 -Upostgres --command="select * from test" --no-align --field-separator=, --tuples-only test
Password for user postgres: 
1,test,2014-01-07 13:30:23.820911
2,test2,2014-01-07 13:30:29.892732
3,test3,2014-01-07 13:30:36.644758
4,test4,2014-01-07 13:30:43.048741

Polecenie COPY

To polecenie ma pewne ograniczenia - tylko superuser kopiować dane do lub z pliku. Jeśli jednak mamy takie prawa, plik który zdefiniujemy dla danych wyjściowych będzie zapisany na maszynie, gdzie działa serwer PostgreSQL.

Jako zwykły użytkownik, możemy tylko kopiować dane do standardowego wyjścia (stdout) lub z standardowego wejścia (stdin).

Opcje, o których warto wspomnieć:

• FORMAT - TEXT (domyślny), CSV, BINARY
• DELIMITER - w zależności od formatu, znak oddzielający dane w kolumnach są różne. Dla TEXT jest to znak tabulator-u, CSV - przecinek.
• NULL - co ma być zwracane gdy pojawi się NULL
• HEADER - czy zwracać nagłówek
• ESCAPE - czy i jak escapować dane

O innych szczegółach możecie przeczytać w dokumentacji tutaj.

Przykłady:

postgres@test(localhost) # COPY test (c1,c2,c3) TO '/tmp/test.csv';
COPY 4

14:23:44 ela@localhost:/tmp  ruby-1.9.3-p194 
∴ cat test.csv 
1 test 2014-01-07 13:30:23.820911
2 test2 2014-01-07 13:30:29.892732
3 test3 2014-01-07 13:30:36.644758
4 test4 2014-01-07 13:30:43.048741

Przekierowanie do standardowego wyjścia:

postgres@test(127.0.0.1) # COPY (select * from test) TO stdout WITH (FORMAT CSV, DELIMITER '|', HEADER ON, FORCE_QUOTE (c2));
c1|c2|c3
1|"test"|2014-01-07 13:30:23.820911
2|"test2"|2014-01-07 13:30:29.892732
3|"test3"|2014-01-07 13:30:36.644758
4|"test4"|2014-01-07 13:30:43.048741
postgres@test(127.0.0.1) # COPY (select * from test) TO stdout WITH (FORMAT CSV, DELIMITER '|', HEADER ON, FORCE_QUOTE (c2,c3));
c1|c2|c3
1|"test"|"2014-01-07 13:30:23.820911"
2|"test2"|"2014-01-07 13:30:29.892732"
3|"test3"|"2014-01-07 13:30:36.644758"
4|"test4"|"2014-01-07 13:30:43.048741"
postgres@test(127.0.0.1) # COPY (select * from test) TO stdout WITH (FORMAT CSV, DELIMITER '|', HEADER ON);
c1|c2|c3
1|test|2014-01-07 13:30:23.820911
2|test2|2014-01-07 13:30:29.892732
3|test3|2014-01-07 13:30:36.644758
4|test4|2014-01-07 13:30:43.048741
postgres@test(127.0.0.1) # COPY (select * from test) TO stdout WITH (FORMAT TEXT, DELIMITER '|');
1|test|2014-01-07 13:30:23.820911
2|test2|2014-01-07 13:30:29.892732
3|test3|2014-01-07 13:30:36.644758
4|test4|2014-01-07 13:30:43.048741
postgres@test(127.0.0.1) # COPY (select * from test) TO stdout WITH (FORMAT TEXT);
1 test 2014-01-07 13:30:23.820911
2 test2 2014-01-07 13:30:29.892732
3 test3 2014-01-07 13:30:36.644758
4 test4 2014-01-07 13:30:43.048741

Oczywiście najlepszym rozwiązaniem jest wykonać komendę COPY z polecenia psql i przekierowanie wyniku do pliku:

∴ /opt/PostgreSQL/9.1/bin/psql -h127.0.0.1 -p7432 -Upostgres -c "COPY (select * from test) TO stdout WITH (FORMAT CSV, DELIMITER '|', HEADER ON, FORCE_QUOTE (c2,c3))" test > /tmp/data.csv
Password for user postgres: 

∴ cat /tmp/data.csv
c1|c2|c3
1|"test"|"2014-01-07 13:30:23.820911"
2|"test2"|"2014-01-07 13:30:29.892732"
3|"test3"|"2014-01-07 13:30:36.644758"
4|"test4"|"2014-01-07 13:30:43.048741"