MySQLユーザーのためのPostgreSQL対応表

毎回わからなくなってググってるから今度からここに追記していく。

MySQL	PostgreSQL
SHOW DATABASES;	\l
USE dbname	\c dbname
SHOW TABLES;	\dt
SELECT * FROM tblname\G	\x on SELECT * FROM tblname;
SELECT * FROM information_schema.processlist;	SELECT * FROM pg_stat_activity;
KILL <pid>;	SELECT pg_terminate_backend(pid);
KILL QUERY <pid>;	SELECT pg_cancel_backend(pid);

table / column の情報

MySQL	PostgreSQL
SHOW TABLE STATUS FROM dbname;	わからん
SHOW CREATE TABLE [dbname.]tblname;	わからん
SHOW FULL COLUMNS FROM [dbname.]tblname;	\d+ tblname もしくは↓で代替
SELECT * FROM information_schema.columns WHERE table_schema = 'dbname' AND table_name = 'tblname' ORDER BY ordinal_position;	SELECT * FROM information_schema.columns WHERE table_catalog = 'dbname' AND table_name = 'tblname' ORDER BY ordinal_position;
SELECT table_name, engine, table_rows, avg_row_length, floor((data_length+index_length)/1024/1024) as allMB, floor(data_length/1024/1024) as dataMB, floor(index_length/1024/1024) as indexMB FROM information_schema.tables WHERE table_schema=database() ORDER BY (data_length+index_length) DESC;	SELECT relname, cast(reltuples as bigint) as num_rows, (relpages/128) as size_mb, cast(relpages*8192.0/(reltuples+1e-10) as bigint) as avg_row_size FROM pg_class ORDER BY size_mb DESC;

dump / restore

MySQL	PostgreSQL
$ mysqldump --no-data dbname [tblname1 tblname2 ...] > schema.sql	$ pg_dump --schema-only [-t tblname1] [-t tblname2] dbname > schema.sql
$ mysqldump --single-transaction dbname > backup.sql	$ pg_dump dbname > backup.sql
$ mysql dbname < backup.sql	$ psql dbname < backup.sql