Přechod z MySQL
MySQL a PostgreSQL jsou dvě různé databáze s rozdílnou filozofií a samozřejmě i rozdílným uživatelským rozhraním. V tomto dokumentu byste měli najít tipy, které Vám usnadní přechod z MySQL do PostgreSQL (a i případně naopak).
Obsah
Prvotní přihlášení, vytvoření uživatele
Po instalaci PostgreSQL je vytvořen pouze jediný databázový účet a to účet postgres (Platí pro Unix). Uživatel root nemá přístup k databázi. Uživatel postgres má plnou kontrolu nad databází. Jedná se o tzv. superuživatele. Nedoporučuje se běžně používat tohoto uživatele (má příliš velká práva), vhodnější je vytvořit si vlastní účet.
[root@localhost ~]# su postgres bash-3.2$ createuser tomas could not change directory to "/root" Shall the new role be a superuser? (y/n) n Shall the new role be allowed to create databases? (y/n) n Shall the new role be allowed to create more new roles? (y/n) n bash-3.2$ exit [root@localhost ~]# exit [pavel@localhost ~]$ psql -U tomas postgres Timing is on. psql (8.4beta1) Type "help" for help. postgres=> SELECT current_user; current_user -------------- tomas (1 row) Time: 36,533 ms postgres=> \q
Pod uživatelem root se k databázi nepřipojíte!
Konzole
SQL konzole se v PostgreSQL spouští příkazem psql. Parametrem tohoto příkazu je název databáze, ke které se chcete přihlásit. Na rozdíl od MySQL vždy musíte uvést název databáze. Pokud nespecifikujete databázi, psql se zkouší připojit k databázi, jejíž jméno odpovídá Vašemu uživatelskému účtu. Obyčejně taková databáze neexistuje. Pokud ještě nemáte vytvořenou svou databázi, můžete se připojit do databáze postgres. V konzoli pak vytvoříte databázi příkazem CREATE DATABASE nazev. Další možností je spustit program createdb.
V konzoli můžeme zadávat SQL příkazy nebo tzv. metapříkazy. Jednoduše řečeno, metapříkazy jsou příkazy pro konzolu, SQL příkazy se posílají na databázový server. Takovým zásadním metapříkazem je příkaz \q, kterým se ukončuje konzole. Musím dodat, všechny metapříkazy začínají zpětným lomítkem a následuje několik písmen, které specifikují (upřesňují) smysl příkazu. V podstatě se jedná o analogii ovládání editoru vi (vim). Pouze místo dvojtečky je zpětné lomítko "\". Metapříkazy se používají i pro zobrazení struktury tabulek, seznamu tabulek, zobrazení nápovědy k SQL příkazům. Seznam metapříkazů zobrazí metapříkaz \?.
[pavel@localhost ~]$ psql postgres
Timing is on.
psql (8.4beta1)
Type "help" for help.
postgres=# help
You are using psql, the command-line interface to PostgreSQL.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
postgres=# CREATE DATABASE test;
CREATE DATABASE
Time: 4145,002 ms
výpis databází (metapříkaz \l)
postgres=# \l
List of databases
Name | Owner | Encoding | Collation | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | cs_CZ.UTF-8 | cs_CZ.UTF-8 |
template0 | postgres | UTF8 | cs_CZ.UTF-8 | cs_CZ.UTF-8 | =c/postgres
: postgres=CTc/postgres
template1 | postgres | UTF8 | cs_CZ.UTF-8 | cs_CZ.UTF-8 | =c/postgres
: postgres=CTc/postgres
test | pavel | UTF8 | cs_CZ.UTF-8 | cs_CZ.UTF-8 |
(4 rows)
Připojení se k databázi test (metapříkaz \c) a vytvoření a plnění tabulky foo:
postgres=# \c test psql (8.4beta1) You are now connected to database "test". test=# CREATE TABLE foo(a integer); CREATE TABLE Time: 9,318 ms test=# INSERT INTO foo VALUES(10),(20); INSERT 0 2 Time: 2,759 ms test=# SELECT * FROM foo; a ---- 10 20 (2 rows) Time: 1,102 ms
Seznam tabulek (metapříkaz \dt) a detail tabulky (metapříkaz \d)
test=# \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+-------
public | foo | table | pavel
(1 row)
test=# \d foo
Table "public.foo"
Column | Type | Modifiers
--------+---------+-----------
a | integer |
test=#
Konec práce (metapříkaz \q)
test=# \q [pavel@localhost ~]$
Zásadní rozdíl mezi MySQL a PostgreSQL je v chování příkazu SHOW. V MySQL se tento příkaz používá k zobrazení struktury tabulek, k zobrazení seznamu tabulek. Naopak, v PostgreSQL, se pro tento účel používají buďto metapříkazy, nebo SQL dotazy do informačního schématu. Příkaz SHOW, v PostgreSQL, slouží pouze k zobrazení obsahu systémových proměnných databáze.
[pavel@localhost ~]$ psql test
Timing is on.
psql (8.4beta1)
Type "help" for help.
test=# \d foo
Table "public.foo"
Column | Type | Modifiers
--------+---------+-----------
a | integer |
test=# SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'foo';
column_name | data_type
-------------+-----------
a | integer
(1 row)
Time: 23,149 ms
test=# \q
Tabulku zachycující odpovídající si příkazy MySQL a PostgreSQL naleznete na adrese http://blog.endpoint.com/2009/12/mysql-and-postgres-command-equivalents.html.
Identifikátory a řetězce
Ohledně zápisu identifikátorů a řetězců se PostgreSQL relativně hodně drží standardu ANSI SQL, což znamená, že pro zápis řetězců se používají apostrofy a pro zápis nestandardních identifikátorů uvozovky.
Identifikátory v MySQL jsou case insensitive a nesmějí obsahovat některé znaky (např. mezeru nebo tečku). Pokud takový identifikátor potřebujeme, použijeme uvozovky. Pozn. Zásadně nedoporučuji používat takové identifikátory pro názvy tabulek nebo sloupců. Dejte si pozor v případě různých GUI, které automaticky používají uvozovky pro identifikátory. V kombinaci s názvy, obsahujícími jak malá, tak velká písmena, je to příčina obtíží při psaní SQL dotazů. MySQL používá obrácený apostrof, a na rozdíl od PostgreSQL je zápis identifikátorů v apostrofech poměrně rozšířený.
Pro zápis řetězců se v MySQL používají uvozovky i apostrofy. Podobně jako v C nebo v Javě:
mysql> select 'xxxx' as Test; +------+ | Test | +------+ | xxxx | +------+ 1 row in set (0.00 sec) mysql> select "xxxx" as `Test ss`; +---------+ | Test ss | +---------+ | xxxx | +---------+ 1 row in set (0.00 sec) mysql> select 'xxxx' as `Test ss`; +---------+ | Test ss | +---------+ | xxxx | +---------+ 1 row in set (0.00 sec)
V PostgreSQL:
postgres=# select 'xxxx' as Test; test ------ xxxx (1 row) Time: 99,891 ms postgres=# select 'xxxx' as "Test ss"; Test ss --------- xxxx (1 row) Time: 0,796 ms
Pozor na citlivost identifikátorů!:
postgres=# create table g1(a integer); CREATE TABLE Time: 187,855 ms postgres=# select * from g1; a --- (0 rows) postgres=# select * from G1; a --- (0 rows)
V MySQL:
mysql> create table g1(a integer); Query OK, 0 rows affected (0.04 sec) mysql> select * from g1; Empty set (0.00 sec) mysql> select * from G1; ERROR 1146 (42S02): Table 'foo.G1' doesn't exist
V MySQL nedochází k automatické normalizaci identifikátorů na malá písmena. Proto tam nezpůsobí problém velké písmeno v názvu identifikátoru:
mysql> create table `G3`(a integer); Query OK, 0 rows affected (0.01 sec) mysql> select * from G3; Empty set (0.00 sec)
Pozor, ovšem v PostgreSQL:
postgres=# create table "G3"(a integer);
CREATE TABLE
Time: 20,139 ms
postgres=# select * from G3;
ERROR: relation "g3" does not exist
LINE 1: select * from G3;
^
GROUP BY
V PostgreSQL povinně musí být zobrazovaný sloupec buďto v agregační funkci nebo v seznamu sloupců v klauzuli GROUP BY. V MySQL toto není podmínkou:
postgres=# select * from zamestnanec;
id | jmeno | prijmeni
----+-------+----------
1 | Pavel | Stehule
(1 row)
Time: 1,287 ms
postgres=# select * from statistika ;
id_zam | typ
--------+-----------------
1 | odpracovany den
1 | odpracovany den
1 | odpracovany den
(3 rows)
V MySQL můžeme napsat následující funkční dotaz:
mysql> select jmeno, prijmeni, count(*)
from zamestnanec
join
statistika
on id = id_zam;
+-------+----------+----------+
| jmeno | prijmeni | count(*) |
+-------+----------+----------+
| Pavel | Stehule | 3 |
+-------+----------+----------+
1 row in set (0.00 sec)
Tentýž dotaz bude ovšem není syntakticky správný v PostrgeSQL:
postgres=# select jmeno, prijmeni, count(*) from zamestnanec join statistika on id = id_zam; ERROR: column "zamestnanec.jmeno" must appear in the GROUP BY clause or be used in an aggregate function
Musí se upravit - např. s využitím derivované tabulky:
postgres=# select jmeno, prijmeni, count
from zamestnanec
join
(select id_zam, count(*)
from statistika
group by id_zam) x
on id = id_zam;
jmeno | prijmeni | count
-------+----------+-------
Pavel | Stehule | 3
(1 row)
Ekvivalenty funkcí MySQL, které nejsou v PostgreSQL (MySQL functions for PostgreSQL)
- Expected PostgreSQL 8.4 and higher.
- Vyžaduje PostgreSQL 8.4 a vyšší
Funkce pro práci s řetězci (String Functions)
CREATE OR REPLACE FUNCTION concat(variadic str text[])
RETURNS text AS $$
SELECT array_to_string($1, '');
$$ LANGUAGE sql
CREATE OR REPLACE FUNCTION concat_ws(separator text, variadic str text[])
RETURNS text as $$
SELECT array_to_string($2, $1);
$$ LANGUAGE sql;
CREATE OR REPLACE FUNCTION elt(int, VARIADIC text[])
RETURNS text AS $$
SELECT $2[$1];
$$ LANGUAGE sql;
CREATE OR REPLACE FUNCTION field(varchar, VARIADIC text[])
RETURNS int AS $$
SELECT i
FROM generate_subscripts($2,1) g(i)
WHERE $1 = $2[i]
UNION ALL
SELECT 0
LIMIT 1
$$ LANGUAGE sql STRICT;
CREATE OR REPLACE FUNCTION find_in_set(str text, strlist text)
RETURNS int AS $$
SELECT i
FROM generate_subscripts(string_to_array($2,','),1) g(i)
WHERE (string_to_array($2, ','))[i] = $1
UNION ALL
SELECT 0
LIMIT 1
$$ LANGUAGE sql STRICT;
CREATE OR REPLACE FUNCTION hex(int)
RETURNS text AS $$
SELECT upper(to_hex($1));
$$ LANGUAGE sql;
CREATE OR REPLACE FUNCTION hex(bigint)
RETURNS text AS $$
SELECT upper(to_hex($1));
$$ LANGUAGE sql;
CREATE OR REPLACE FUNCTION hex(text)
RETURNS text AS $$
SELECT upper(encode($1::bytea, 'hex'))
$$ LANGUAGE sql;
/*
* char is keyword, double quotes are necessary.
*
* postgres=# select "char"(77,121,83,81,'76');
* char
* -------
* MySQL
*/
CREATE OR REPLACE FUNCTION "char"(VARIADIC int[])
RETURNS text AS $$
SELECT array_to_string(ARRAY(SELECT chr(unnest($1))),'')$$
LANGUAGE sql;
CREATE OR REPLACE FUNCTION lcase(str text)
RETURNS text AS $$
SELECT lower($1)
$$ LANGUAGE sql;
CREATE OR REPLACE FUNCTION left(str text, len int)
RETURNS text AS $$
SELECT substring($1 FROM 1 FOR $2)
$$ LANGUAGE sql;
CREATE OR REPLACE FUNCTION locate(substr text, str text)
RETURNS int AS $$
SELECT position($1 in $2)
$$ LANGUAGE sql;
CREATE OR REPLACE FUNCTION reverse(str text)
RETURNS text AS $$
SELECT array_to_string(ARRAY(SELECT substring($1 FROM i FOR 1)
FROM generate_series(length($1),1,-1) g(i)),
'')
$$ LANGUAGE sql;
CREATE OR REPLACE FUNCTION right(str text, len int)
RETURNS text AS $$
SELECT substring($1 FROM length($1) - $2 FOR $2)
$$ LANGUAGE sql;
CREATE OR REPLACE FUNCTION space(n int)
RETURNS text AS $$
SELECT repeat(' ', $1)
$$ LANGUAGE sql;
CREATE OR REPLACE FUNCTION strcmp(text, text)
RETURNS int AS $$
SELECT CASE WHEN $1 < $2 THEN -1
WHEN $1 > $2 THEN 1
ELSE 0 END;
$$ LANGUAGE sql;
CREATE OR REPLACE FUNCTION substring_index(str text, delim text, count int)
RETURNS text AS $$
SELECT CASE WHEN $3 > 0 THEN array_to_string((string_to_array($1, $2))[1:$3], $2)
ELSE array_to_string(ARRAY(SELECT unnest(string_to_array($1,$2))
OFFSET array_upper(string_to_array($1,$2),1) + $3),
$2)
END
$$ LANGUAGE sql;
CREATE OR REPLACE FUNCTION ucase(str text)
RETURNS text AS $$
SELECT upper($1)
$$ LANGUAGE sql;
CREATE CAST (bytea AS text) WITHOUT FUNCTION AS ASSIGNMENT;
/*
* SELECT hex('žlutý kůň'), unhex(hex('žlutý kůň'))
*/
CREATE OR REPLACE FUNCTION unhex(text)
RETURNS text AS $$
SELECT decode($1, 'hex')::text;
$$ LANGUAGE sql;
Místo funkce GROUP_CONCAT použijte agregační funkci array_agg:
postgres=# select * from omega; +---+ | x | +---+ | 1 | | 3 | | 6 | +---+ (3 rows) postgres=# select array_to_string(array_agg(x),',') from omega; +-----------------+ | array_to_string | +-----------------+ | 1,3,6 | +-----------------+ (1 row)
Funkce pro práci s typem datum a čas (Date, Time Functions)
/*
* postgres=# select adddate('2008-01-02','31 day');
* adddate
* -----------
* 2008-02-02
*/
CREATE OR REPLACE FUNCTION adddate(date, interval)
RETURNS date AS $$
SELECT ($1 + $2)::date; $$
LANGUAGE sql;
CREATE OR REPLACE FUNCTION curdate()
RETURNS date AS $$
SELECT CURRENT_DATE
$$ LANGUAGE sql;
CREATE OR REPLACE FUNCTION convert_tz(dt timestamp, from_tz text, to_tz text)
RETURNS timestamp AS $$
SELECT ($1 AT TIME ZONE $2) AT TIME ZONE $3;
$$ LANGUAGE sql;
CREATE OR REPLACE FUNCTION date(anyelement)
RETURNS date AS $$
SELECT $1::date;
$$ LANGUAGE sql;
SELECT OR REPLACE FUNCTION datediff(date, date)
RETURNS int AS $$
SELECT $1 - $2
$$ LANGUAGE sql;
CREATE OR REPLACE FUNCTION date_add(date, interval)
RETURNS date AS $$
SELECT adddate($1, $2)
$$ LANGUAGE sql;
CREATE OR REPLACE FUNCTION date_format(date, text)
RETURNS text AS $$
SELECT to_char($1, _mysqlf_pgsql($2))
$$ LANGUAGE sql;
CREATE OR REPLACE FUNCTION date_format(timestamp, text)
RETURNS text AS $$
SELECT to_char($1, _mysqlf_pgsql($2))
$$ LANGUAGE sql;
CREATE OR REPLACE FUNCTION date_sub(date, interval)
RETURNS date AS $$
SELECT ($1 - $2)::date;
$$ LANGUAGE sql;
CREATE OR REPLACE FUNCTION dayofmonth(date)
RETURNS int AS $$
SELECT EXTRACT(day from $1)::int
$$ LANGUAGE sql;
CREATE OR REPLACE FUNCTION day(date)
RETURNS int AS $$
SELECT dayofmonth($1)
$$ LANGUAGE sql;
CREATE OR REPLACE FUNCTION dayname(date)
RETURNS text AS $$
SELECT to_char($1, 'TMDay')
$$ LANGUAGE sql;
CREATE OR REPLACE FUNCTION dayofweek(date)
RETURNS int AS $$
SELECT EXTRACT(dow FROM $1)::int
$$ LANGUAGE sql;
CREATE OR REPLACE FUNCTION dayofyear(date)
RETURNS int AS $$
SELECT EXTRACT(doy FROM $1)::int
$$ LANGUAGE sql;
CREATE OR REPLACE FUNCTION from_days(int)
RETURNS date AS $$
SELECT date '0001-01-01bc' + $1
$$ LANGUAGE sql;
CREATE OR REPLACE FUNCTION from_unixtime(double precision)
RETURNS timestamp AS $$
SELECT to_timestamp($1)::timestamp
$$ LANGUAGE sql;
CREATE OR REPLACE FUNCTION _mysqlf_pgsql(text)
RETURNS text AS $$
SELECT array_to_string(ARRAY(SELECT s
FROM (SELECT CASE WHEN substring($1 FROM i FOR 1) <> '%'
AND substring($1 FROM i-1 FOR 1) <> '%'
THEN substring($1 FROM i for 1)
ELSE CASE substring($1 FROM i FOR 2)
WHEN '%H' THEN 'HH24'
WHEN '%p' THEN 'am'
WHEN '%Y' THEN 'YYYY'
WHEN '%m' THEN 'MM'
WHEN '%d' THEN 'DD'
WHEN '%i' THEN 'MI'
WHEN '%s' THEN 'SS'
WHEN '%a' THEN 'Dy'
WHEN '%b' THEN 'Mon'
WHEN '%W' THEN 'Day'
WHEN '%M' THEN 'Month'
END
END s
FROM generate_series(1,length($1)) g(i)) g
WHERE s IS NOT NULL),
'')
$$ LANGUAGE sql;
CREATE OR REPLACE FUNCTION get_format(text, text)
RETURNS text AS $$
SELECT CASE lower($1)
WHEN 'date' THEN
CASE lower($2)
WHEN 'usa' THEN '%m.%d.%Y'
WHEN 'jis' THEN '%Y-%m-%d'
WHEN 'iso' THEN '%Y-%m-%d'
WHEN 'eur' THEN '%d.%m.%Y'
WHEN 'internal' THEN '%Y%m%d'
END
WHEN 'datetime' THEN
CASE lower($2)
WHEN 'usa' THEN '%Y-%m-%d %H-.%i.%s'
WHEN 'jis' THEN '%Y-%m-%d %H:%i:%s'
WHEN 'iso' THEN '%Y-%m-%d %H:%i:%s'
WHEN 'eur' THEN '%Y-%m-%d %H.%i.%s'
WHEN 'internal' THEN '%Y%m%d%H%i%s'
END
WHEN 'time' THEN
CASE lower($2)
WHEN 'usa' THEN '%h:%i:%s %p'
WHEN 'jis' THEN '%H:%i:%s'
WHEN 'iso' THEN '%H:%i:%s'
WHEN 'eur' THEN '%H.%i.%s'
WHEN 'internal' THEN '%H%i%s'
END
END;
$$ LANGUAGE sql;
CREATE OR REPLACE FUNCTION hour(time)
RETURNS int AS $$
SELECT EXTRACT(hour FROM $1)::int;
$$ LANGUAGE sql;
CREATE OR REPLACE FUNCTION hour(timestamp)
RETURNS int AS $$
SELECT EXTRACT(hour FROM $1)::int;
$$ LANGUAGE sql;
CREATE OR REPLACE FUNCTION last_day(date)
RETURNS date AS $$
SELECT (date_trunc('month',$1 + interval '1 month'))::date - 1
$$ LANGUAGE sql;
CREATE OR REPLACE FUNCTION makedate(year int, dayofyear int)
RETURNS date AS $$
SELECT (date '0001-01-01' + ($1 - 1) * interval '1 year' + ($2 - 1) * interval '1 day'):: date
$$ LANGUAGE sql;
CREATE OR REPLACE FUNCTION maketime(int, int, double precision)
RETURNS time AS $$
SELECT time '00:00:00' + $1 * interval '1 hour' + $2 * interval '1 min'
+ $3 * interval '1 sec'
$$ LANGUAGE sql;
CREATE OR REPLACE FUNCTION minute(timestamp)
RETURNS int AS $$
SELECT EXTRACT(minute FROM $1)::int
$$ LANGUAGE sql;
CREATE OR REPLACE FUNCTION month(date)
RETURNS int AS $$
SELECT EXTRACT(month FROM $1)::int
$$ LANGUAGE sql;
CREATE OR REPLACE FUNCTION monthname(date)
RETURNS text AS $$
SELECT to_char($1, 'TMMonth')
$$ LANGUAGE sql;
CREATE OR REPLACE FUNCTION str_to_date(text, text)
RETURNS date AS $$
SELECT to_date($1, _mysqlf_pgsql($2))
$$ LANGUAGE sql;
CREATE OR REPLACE FUNCTION time(timestamp)
RETURNS time AS $$
SELECT $1::time
$$ LANGUAGE sql;
CREATE OR REPLACE FUNCTION to_days(date)
RETURNS int AS $$
SELECT $1 - '0001-01-01bc'
$$ LANGUAGE sql;
CREATE OR REPLACE FUNCTION unix_timestamp()
RETURNS double precision AS $$
SELECT EXTRACT(epoch FROM current_timestamp)
$$ LANGUAGE sql;
CREATE OR REPLACE FUNCTION unix_timestamp(timestamp)
RETURNS double precision AS $$
SELECT EXTRACT(epoch FROM $1)
$$ LANGUAGE sql;
CREATE OR REPLACE FUNCTION year(date)
RETURNS int AS $$
SELECT EXTRACT(year FROM $1)
$$ LANGUAGE sql;
CREATE OR REPLACE FUNCTION week(date)
RETURNS int AS $$
SELECT EXTRACT(week FROM $1)::int;
$$ LANGUAGE sql;
