PGSQL CheatSheet Mysql2psql

http://blog.endpoint.com/2009/12/mysql-and-postgres-command-equivalents.
html
Users toggling between MySQL and Postgres are often confused by the equivalent commands to
accomplish basic tasks. Here's a chart listing some of the differences between the command line client
for MySQL (simply called mysql), and the command line client for Postgres (called psql).
MySQL (using mysql)
\c Clears the buffer
\d string Changes the delimiter
\e Edit the buffer with external
editor
\g Send current query to the
server
\h Gives help - general or
specific
Postgres (using psql)

\r (same)
No equivalent
\n Turns the pager off
\pset pager off (same)
\e (same)
Notes
Postgres also allows \e filename which
will become the new buffer
\g (same)
\h (same)
The pager is only used when needed
based on number of rows; to force it
on, use \pset pager always
\p Print the current buffer

\p (same)
\q Quit the client
\q (same)
\r [dbname] [dbhost] Reconnect
\c [dbname] [dbuser] (same)
to server
\s Status of server
No equivalent
\t Stop teeing output to file
No equivalent
\u dbname Use a different

database
\c dbname (same)
\w Do not show warnings
No equivalent
\C charset Change the charset
\encoding encoding Change

the encoding
\G Display results vertically

(one column per line)
\x (same)
\P pagername Change the

current pager program
Environment variable PAGER

or PSQL_PAGER
Some of the same info is available

from the pg_settings table
However, \o (without any argument)
will stop writing to a previously
opened outfile
Postgres always shows warnings by
default
Run \encoding with no argument to
view the current one
Note that \G is a one-time effect, while
\x is a toggle from one mode to
another. To get the exact same effect as
\G in Postgres, use \x\g\x
Note that the Postgres prompt cannot

be reset by omitting an argument. A
\R string Change the prompt
\set PROMPT1 string (same) good prompt to use is:\set PROMPT1
'%n@%`hostname`:%>%R%#%x
%x%x '
\T filename Sets the tee output
Postgres can output to a pipe, so you
No direct equivalent
file
can do: \o | tee filename
Postgres always show warnings by
\W Show warnings
No equivalent
default
\? Help for internal commands \? (same)

\# Rebuild tab-completion hash No equivalent
\! command Execute a shell
command
\! command (same)
Not needed, as tab-completion in

Postgres is always done dynamically
If no command is given with Postgres,
the user is dropped to a new shell (exit
to return to psql)
\. filename Include a file as if it

\i filename (same)
were typed in
\timing Toggles timing on and
Timing is always on
off
No equivalent
\t Toggles 'tuple only' mode
show tables; List all tables
\dt (same)
desc tablename; Display

information about the given
\d tablename (same)
table
show index from tablename;
Display indexes on the given
\d tablename (same)
table
show triggers from tablename;
Display triggers on the given
\d tablename (same)
table
show databases; List all
\l (same)
databases
No equivalent
\dn List all schemas
This shows the data from select

queries, with no headers or footers
Many also use just \d, which lists
tables, views, and sequences
The bottom of the \d tablename output

always shows indexes, as well as
triggers, rules, and constraints
See notes on show index above
MySQL does not have the concept of

schemas, but uses databases as a
similar concept
select version(); Show backend

select version(); (same)
server version
select now(); Show current time select now(); (same)
select current_user; Show the
current user
select database(); Show the
current database
show create table tablename;
Output a CREATE TABLE
statement for the given table
show engines; List all server
engines
Postgres will give fractional seconds in

the output
select current_user; (same)

select current_database();
(same)
No equivalent
The closest you can get with Postgres

is to use pg_dump --schema-only -t
tablename
No equivalent
Postgres does not use separate engines
Most CREATE commands are similar

CREATE object ... Mostly the or identical. Lookup specific help on
same
commands (for example: \h CREATE
TABLE)
If there are any commands not listed you would like to see, or if there are errors in the above, please let
me know. There are differences in how you invoke mysql and psql, and in the flags that they use,
but that's a topic for another day.
CREATE object ... Create an
object: database, table, etc.
Updates: Added PSQL_PAGER and \o |tee filename, thanks to the Davids in the comments section.
Added \t back in, per Joe's comment.
A note on the "show create table" equivalent, you can make use of \! to run system commands, so -->
\! pg_dump --schema-only -t tablename
will show you the create sql without having to leave psql.
PostgreSQL cheat sheet for MySQL lamers

So you're switching to PostgreSQL from MySQL? Here is some help
Connecting to MySQL:
1. Use MySQL client CLI to connect: mysql -u user_name -p
Connecting to PostgreSQL:
1. Run the PostgreSQL client as user "postgres": sudo -u postgres psql
Description
Show databases
Use/Connect to a database named
'some_database'
Show tables/relations within one
database
Show table details (columns,
types)
Show indices of some_table (in
case of MySQL) and all indices of
database (PostgreSQL)
MySQL command
SHOW DATABASES;
PostgreSQL equivalent
\l[ist]
USE some_database;
\c some_database
SHOW TABLES;
\dt
DESCRIBE some_table;
\d+ some_table
SHOW INDEX FROM

some_table;
\di
Create user that can create

databases
CREATE USER harry

IDENTIFIED BY 'foo';
Change password of an existing

user
Grants access to a database.
GRANT ALL PRIVILEGES ON

database.* TO
username@localhost;
Grants access to create databases. ?
General hints on PostgreSQL

\? opens the command overview
\d lists things: \du lists users, \dt lists tables etc
CREATE ROLE username

WITH createdb LOGIN
[PASSWORD password];
ALTER ROLE username WITH
PASSWORD 'password';
GRANT ALL PRIVILEGES
ON DATABASE database TO
username;
ALTER USER username
CREATEDB;
Command Equivalents in Postgres Coming From Mysql

Mysql
Postgres
mysqldump
pg_dumpall
Description
pg_dumpall is designed to dump all of the databases and calls pg_dump
to do it. It can also be used to dump global values like roles and
tablespaces.Example with mysql:
mysqldump all-databases > /path/to/file.sql
Example with postgres:
pg_dumpall > /path/to/file.sql
pg_dump is used for dumping individual databases.Example with mysql:
mysqldump mydatabase > /path/to/file.sql
mysqldump
n/a
pg_dump
pg_restore
Example with postgres:

pg_dump mydatabase > /path/to/file.sql
pg_dump is capable of dumping to multiple formats with the -F option.
The default option is as a raw sql file. If you wanted to dump as
postgress custom format, you could add -Fc as options before the
database name when using pg_dump.pg_restore is designed to restore
from output files generated in this fashion.
I am not aware of a mysql equivalent for pg_restore, but it reminds me
more of a restore of a binary type file that you would do with something
like sql server.
innodb_top
pg_top
mysql
psql
innodb_top does not ship with mysql and is a third party executable. It
shows you things like inserts per second, updates per second, transactions
per second and gives a good overview as to what is going on with the
serverpg_top shows similar things but is laid out more similarly to the
native linux top program.
This is the command to enter the CLI utility. You can also pass sql into
the utility from the linux command line like shown below.Passing query
with mysql (-e for execute):
mysql -e select 1;
Passing query with postgres (-c for command):
psql -c select 1;
Exit
Mysql{quit|
exit|[ctrl-c]}
Exit
Postgres\q
I completely felt like a noob the first time I got into the psql CLI. I tried
entering all of the things I would typically enter to exit mysql, or even
terminals and other programs to no avail. I had to google search it and
discovered that postgres does just about everything with slash commands.
\q quits postgres and returns you to your shell.

show
This lists the databases on the server instance you are connected to or that
\l or \list
databases;
you have access to.
use [dbname]; \c [dbname] Connect to a database or put yourself in a database context
Show the tables in the database context you are connected to. The plus
show tables; \dt or \dt+
adds size data and description fields
describe
\d
Shows the columns, types, modifiers, indexes, and tables referenced by
[tablename]; [tablename] keys.
No direct
equivalent,
use below
show create
command
table
This will give the sql used to create a table.
from shell:
[tablename];
pg_dump -st
tablename
dbname
select * from Shows all users and their global permissions. Postgres lists the
select * from
pg_user;
permissions as a comma separated string under a filed called attributes.
mysql.user;
\du
Mysql shows a boolean value for each of the possible permissions.
select * from
show full
This will show all of the queries that are currently running and how long
pg_stat_activ
processlist;
they have been running for.
ity;
show
This will show all of the current values for the variables. Postgres even
show all;
variables;
offers a brief description of what each variable is.
SELECT *
FROM
pg_stat_activ
ity;
SELECT *
FROM
pg_stat_data
base;
show engine SELECT * There is no central place in postgres to get all of the information obtained
innodb
FROM
by running show engine innodb status in mysql. There are a number of
status\G
pg_stat_user queries you can run to get roughly equivalent data though.
_tables;
SELECT *
FROM
pg_stat_user
_indexes;
SELECT *
FROM
pg_locks;
show slave
select * from Shows replication information. On idle write masters you can errantly see
status\G
pg_stat_repli replication report as behind or lagging. This is actually showing you the
cation;
current timestamp minus the timestamp of the last item applied on the
select now()
pg_last_xact
_replay_time
stamp() AS
replication_d
elay;
\G
\x
stop slave;
start slave;
select
pg_xlog_repl
ay_pause();
select
pg_xlog_repl
ay_resume();
slave. If the master hasnt written anything, the slave has not applied
anything and can show you that it is behind. It is recommended to use a
written timestamp from cron every minute to gauge replication. This
achieves 2 things, it will guarantee regular writes to the master which will
replicate to the slave, and monitoring can look to the timestamp in a
particular location to know if the server is behind. This is similar to the
heartbeat checks that percona recommends in mysql.
Sometimes it is nice to have information displayed in a non-row format.
Mysql achieves this with using a \G at the end of the query and it will
output each row of database data is a key-value pair. Postgres calls this
expanded output mode. You can toggle expanded output mode to on by
typing \x[enter] and then running your query normally. This is a session
setting, so if you want to go back to row format, you can toggle it back to
off with the same action.

PGSQL CheatSheet Mysql2psql

Uploaded by

Copyright:

Available Formats

PGSQL CheatSheet Mysql2psql

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

PGSQL CheatSheet Mysql2psql

Uploaded by

Copyright:

Available Formats

http://blog.endpoint.com/2009/12/mysql-and-postgres-command-equivalents.

Postgres (using psql)

\n Turns the pager off

\pset pager off (same)

\p Print the current buffer

\t Stop teeing output to file

\u dbname Use a different

\w Do not show warnings

\C charset Change the charset

\encoding encoding Change

\G Display results vertically

\P pagername Change the

Environment variable PAGER

Some of the same info is available

Note that the Postgres prompt cannot

\? Help for internal commands \? (same)

Not needed, as tab-completion in

\. filename Include a file as if it

\t Toggles 'tuple only' mode

show tables; List all tables

desc tablename; Display

\dn List all schemas

This shows the data from select

The bottom of the \d tablename output

MySQL does not have the concept of

select version(); Show backend

Postgres will give fractional seconds in

select current_user; (same)

The closest you can get with Postgres

Postgres does not use separate engines

Most CREATE commands are similar

PostgreSQL cheat sheet for MySQL lamers

SHOW INDEX FROM

Create user that can create

CREATE USER harry

Change password of an existing

Grants access to a database.

GRANT ALL PRIVILEGES ON

Grants access to create databases. ?

General hints on PostgreSQL

CREATE ROLE username

Command Equivalents in Postgres Coming From Mysql

Example with postgres:

\q quits postgres and returns you to your shell.

You might also like