End Point

News

Welcome to End Point's blog

Ongoing observations by End Point people.

MySQL and Postgres command equivalents (mysql vs psql)

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)Postgres (using psql)Notes
\c Clears the buffer\r (same)
\d string Changes the delimiterNo equivalent
\e Edit the buffer with external editor\e (same)Postgres also allows \e filename which will become the new buffer
\g Send current query to the server\g (same)
\h Gives help - general or specific\h (same)
\n Turns the pager off\pset pager off (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 to server\c [dbname] [dbuser] (same)
\s Status of serverNo equivalentSome of the same info is available from the pg_settings table
\t Stop teeing output to fileNo equivalentHowever, \o (without any argument) will stop writing to a previously opened outfile
\u dbname Use a different database\c dbname (same)
\w Do not show warningsNo equivalentPostgres always shows warnings by default
\C charset Change the charset\encoding encoding Change the encodingRun \encoding with no argument to view the current one
\G Display results vertically (one column per line)\x (same)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
\P pagername Change the current pager programEnvironment variable PAGER or PSQL_PAGER
\R string Change the prompt\set PROMPT1 string (same)Note that the Postgres prompt cannot be reset by omitting an argument. A good prompt to use is:\set PROMPT1 '%n@%`hostname`:%>%R%#%x%x%x '
\T filename Sets the tee output fileNo direct equivalentPostgres can output to a pipe, so you can do: \o | tee filename
\W Show warningsNo equivalentPostgres always show warnings by default
\? Help for internal commands\? (same)
\# Rebuild tab-completion hashNo equivalentNot needed, as tab-completion in Postgres is always done dynamically
\! command Execute a shell command\! command (same)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 were typed in\i filename (same)
Timing is always on\timing Toggles timing on and off
No equivalent\t Toggles 'tuple only' modeThis shows the data from select queries, with no headers or footers
show tables; List all tables\dt (same)Many also use just \d, which lists tables, views, and sequences
desc tablename; Display information about the given table\d tablename (same)
show index from tablename; Display indexes on the given table\d tablename (same)The bottom of the \d tablename output always shows indexes, as well as triggers, rules, and constraints
show triggers from tablename; Display triggers on the given table\d tablename (same)See notes on show index above
show databases; List all databases\l (same)
No equivalent\dn List all schemasMySQL does not have the concept of schemas, but uses databases as a similar concept
select version(); Show backend server versionselect version(); (same)
select now(); Show current timeselect now(); (same)Postgres will give fractional seconds in the output
select current_user; Show the current userselect current_user; (same)
select database(); Show the current databaseselect current_database(); (same)
show create table tablename; Output a CREATE TABLE statement for the given tableNo equivalentThe closest you can get with Postgres is to use pg_dump --schema-only -t tablename
show engines; List all server enginesNo equivalentPostgres does not use separate engines
CREATE object ... Create an object: database, table, etc.CREATE object ... Mostly the sameMost CREATE commands are similar or identical. Lookup specific help on 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.

Updates: Added PSQL_PAGER and \o |tee filename, thanks to the Davids in the comments section. Added \t back in, per Joe's comment.

21 comments:

Anonymous said...

I imagine you can get close to the effects of \W and \w by changing client_min_messages.

Greg Sabino Mullane said...

Yes, I considered adding a link to client_min_messages but I can't really think of a good reason to ever dial that down so much as to emulate \w :)

David Fetter said...

Greg,

Great little chart, and thanks for doing it. :)

A few things:

* Some of the toggles also let you set: \timing, for example.
* \pset can be used for a lot of this stuff
* It's handy to have a .psqlrc file. Mine looks like this:

\set PSQL_EDITOR "/usr/bin/vim -c ':set ft=sql'"
\set PROMPT1 '%:USER:@%/:%>%R%# '
\set ON_ERROR_STOP

* In addition to \h, 'help' also shows help

Merry Christmas!

Cheers,
David.

David Christensen said...

If you wanted the tee-ing behavior of MySQL, you could just \o |tee outfile; i.e., use Postgres' ability to write to pipes.

Greg Sabino Mullane said...

David Fetter: good points all around. I left out the \timing toggle as it only got recently added. Same with 'help' (plus it doesn't really tell you anything other than how to use \h and \?). I'm a little surprised to see your prompt doesn't contain %x? I'll update the blog to reflect the PSQL_EDITOR env var.

Greg Sabino Mullane said...

David Christensen: good call, I'll add that in there. Does mysql do the tee-ing itself I wonder? Probably so if it works on Windows.

Bruce Momjian said...

Can anyone explain why so many of the commands are identical? I wasn't aware Postgres was using MySQL as a model for its backslash commands, so is the opposite true?

Ethan Rowe said...

Would either camp admit to using the other as a model, even if it is the case?

Jon Jensen said...

My guess is that each has used the other as a model.

Many of the backslash commands that are shared are fairly obvious choices.

Many users of Postgres are also familiar with MySQL, and the reverse is often true too, though perhaps slightly less often just due to market share difference.

I'd certainly like to see the handy \T function in Postgres now that I know it's there in MySQL, and there'd be no point in adopting a different command when it doesn't conflict with an existing one.

David Christensen said...

@Jon Jensen: Unfortunately for that plan, \T in psql already sets html table attributes. I can't say I've ever used any of the alternate output formats in psql, so it seems pretty useless to me... :-)

Robert Treat said...

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.

Joe said...

You're missing \p, which is the same in both MySQL and Postgres. The note on the \R (MySQL) line is correct (\set), but the psql equivalent column shows \pset, which has another function.

@Bruce Didn't the psql backslash commands originate from UCB POSTGRES (which in turn adopted them from UCB INGRES)? I presume MySQL was looking at those and not at UFI (dating myself).

Greg Smith said...

pg_settings is technically a view, not a table. It's assembled using a bunch of internal functions. It does act like a table in that you can UPDATE settings in there.

Greg Sabino Mullane said...

Joe:

Thanks, \p escaped from the final version somehow, it was there in an earlier draft. I'll add it back in. Good catch with \set and \pset, now fixed.

Greg Sabino Mullane said...

Jon, David C:

psql ran out of usable single-letter flags a long time ago. Otherwise I might have been able to use \T back when I wrote \timing. It actually started life as \m (which is still free) but was changed to \timing at some point between the patch and the commit. Perhaps \tee filename?

Jon Jensen said...

Yes, nice idea. I like \tee filename -- it's probably best to reserve single-character backslash commands for more frequently used commands.

`abd ar raafi` said...

Thank you very much, this is awesome.

bojac said...

Hi! thank you for this! such a great help!

I am new to PostgreSQL and I badly need these conversions.!

Thanks!

Mark said...

\d tablename will show you the layout of the table similar to show create table, but can't be copied to do the actual create

Anil Mehrotra said...

the command to jump out from database is not listed

Greg Sabino Mullane said...

> the command to jump out from database is not listed

Not sure what you mean: the commands to exit and run a shell command are already there. If you mean ctrl-z, that's operating system specific (and thus identical to both)