• Home

  • Custom Ecommerce
  • Application Development
  • Database Consulting
  • Cloud Hosting
  • Systems Integration
  • Legacy Business Systems
  • Security & Compliance
  • GIS

  • Expertise

  • About Us
  • Our Team
  • Clients
  • Blog
  • Careers

  • CasePointer

  • VisionPort

  • Contact
  • Our Blog

    Ongoing observations by End Point Dev people

    MySQL and PostgreSQL command equivalents (mysql vs. psql)

    Greg Sabino Mullane

    By Greg Sabino Mullane
    December 24, 2009

    Users toggling between the MySQL and PostgreSQL command-line clients 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
    Clears the buffer
    \d string
    Changes the delimiter
    No equivalent
    Edit the buffer with external editor
    Postgres also allows \e filename which will become the new buffer
    Send current query to the server
    Gives help — general or specific
    Turns the pager off
    \pset pager off
    The pager is only used when needed based on number of rows; to force it on, use \pset pager always
    Print the current buffer
    Quit the client
    \r [dbname] [dbhost]
    Reconnect to server
    \c [dbname] [dbuser]
    Status of server
    No equivalentSome of the same info is available from the pg_settings table
    Stop teeing output to file
    No equivalentHowever, \o (without any argument) will stop writing to a previously opened outfile
    \u dbname
    Use a different database
    \c dbname
    Do not show warnings
    No equivalentPostgres always shows warnings by default
    \C charset
    Change the charset
    \encoding encoding
    Change the encoding
    Run \encoding with no argument to view the current one
    Display results vertically (one column per line)
    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 program
    Environment variable PAGER or PSQL_PAGER
    \R string
    Change the prompt
    \set PROMPT1 string
    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 file
    No direct equivalentPostgres can output to a pipe, so you can do: \o | tee filename
    Show warnings
    No equivalentPostgres shows warnings by default
    Help for internal commands
    Rebuild tab-completion hash
    No equivalentNot needed, as tab-completion in Postgres is always done dynamically
    \! command
    Execute a shell command
    \! command
    If no command is given with Postgres, the user is dropped to a new shell (exit to return to psql)
    \. filename
    Include (“source”) a file as if it were typed in
    \i filename
    Timing is always on\timing
    Toggles timing on and off
    No equivalent\t
    Toggles “tuple only” mode
    This shows the data from select queries, with no headers or footers
    show tables;
    List all tables
    Many also use just \d, which lists tables, views, and sequences
    desc tablename;
    Display information about the given table
    \d tablename
    show index from tablename;
    Display indexes on the given table
    \d tablename
    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
    See notes on show index above
    show databases;
    List all databases
    No equivalent\dn
    List all schemas
    MySQL does not have the concept of schemas, but uses databases as a similar concept
    select version();
    Show backend server version
    select version();
    select now();
    Show current time
    select now();
    Postgres gives fractional seconds in the output
    select current_user;
    Show the current user
    select current_user;
    select database();
    Show the current database
    select current_database();
    show create table tablename;
    Output a CREATE TABLE statement for the given table
    No equivalentThe closest you can get with Postgres is to use pg_dump --schema-only -t tablename
    show engines;
    List all server engines
    No equivalentPostgres does not use separate engines
    CREATE object ...
    Create an object: database, table, etc.
    CREATE object ...
    Mostly the same
    Most 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.

    database mysql open-source postgres tips