{"id":15024,"date":"2021-02-24T08:54:36","date_gmt":"2021-02-23T22:54:36","guid":{"rendered":"https:\/\/database.guide\/?p=15024"},"modified":"2021-11-06T14:53:50","modified_gmt":"2021-11-06T04:53:50","slug":"what-is-psql","status":"publish","type":"post","link":"https:\/\/database.guide\/what-is-psql\/","title":{"rendered":"What is psql?"},"content":{"rendered":"\n<p>psql is a terminal based interface for <a href=\"https:\/\/database.guide\/what-is-postgresql\/\" title=\"What is PostgreSQL?\">PostgreSQL<\/a>. <\/p>\n\n\n\n<p>psql enables you to administer PostgreSQL from the command line interface (CLI) as an alternative to using a graphical user interface (GUI), such as pgAdmin, Postico, <a href=\"https:\/\/database.guide\/what-is-azure-data-studio\/\" title=\"What is Azure Data Studio?\">Azure Data Studio<\/a>, etc. <\/p>\n\n\n\n<!--more-->\n\n\n\n<p>Using psql, you can do things such as:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Run <a href=\"https:\/\/database.guide\/what-is-sql\/\" title=\"What is SQL?\">SQL<\/a> commands. For example, you can:<ul><li>Create <a href=\"https:\/\/database.guide\/what-is-a-database\/\" title=\"What is a Database?\">databases<\/a><\/li><li>Create database objects, such as <a href=\"https:\/\/database.guide\/what-is-a-table\/\" title=\"What is a Table?\">tables<\/a>, <a href=\"https:\/\/database.guide\/what-is-a-view\/\" title=\"What is a View?\">views<\/a>, indexes, triggers, etc<\/li><li>Run SQL queries against a database<\/li><li>Use SQL to administer users, roles, etc.<\/li><\/ul><\/li><li>Run commands from a file (instead of standard input).<\/li><li>Enter meta-commands (also known as &#8220;slash commands&#8221; and &#8220;backslash commands&#8221;). Meta-commands can be useful for administration and scripting. <\/li><li>Enter other shell like features to help with administration, scripting, etc.<\/li><li>Pass options to psql. For example, you can specify a database to connect to, the user name, a file name to read, and much more.<\/li><\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">Syntax<\/h2>\n\n\n\n<p>The syntax for psql goes like this:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>psql &#91;OPTION]... &#91;DBNAME &#91;USERNAME]]<\/code><\/pre>\n\n\n\n<p>So you can simply open up a terminal window and type <code>psql<\/code>, or you can type <code>psql<\/code>, followed an option.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Connecting to PostgreSQL via psql<\/h2>\n\n\n\n<p>Here&#8217;s an example of using psql to connect to a PostgreSQL database:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>psql -d music -U barney<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"> psql (12.1)\n Type \"help\" for help.\n \n\n music=#&nbsp;<\/pre>\n\n\n\n<p>This connects to the <code>music<\/code> database using the user called <code>barney<\/code>.<\/p>\n\n\n\n<p>Here it is again, but this time showing the full terminal input and output:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>barney@Barneys-MacBook-Pro ~ % psql -d music -U barney\npsql (12.1)\nType &quot;help&quot; for help.\nmusic=#<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Run SQL Queries<\/h2>\n\n\n\n<p>Now that we&#8217;ve connected to the music database, we can run SQL queries against it.<\/p>\n\n\n\n<p>Example:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>barney@Barneys-MacBook-Pro ~ % psql -d music -U barney\npsql (12.1)\nType &quot;help&quot; for help.\nmusic=# SELECT * FROM Artists;\n artistid |       artistname       | activefrom \n----------+------------------------+------------\n        1 | Iron Maiden            | 1975-12-25\n        2 | AC\/DC                  | 1973-01-11\n        3 | Allan Holdsworth       | 1969-01-01\n        4 | Buddy Rich             | 1919-01-01\n        5 | Devin Townsend         | 1993-01-01\n        6 | Jim Reeves             | 1948-01-01\n        7 | Tom Jones              | 1963-01-01\n        8 | Maroon 5               | 1994-01-01\n        9 | The Script             | 2001-01-01\n       10 | Lit                    | 1988-06-26\n       11 | Black Sabbath          | 1968-01-01\n       12 | Michael Learns to Rock | 1988-03-15\n       13 | Carabao                | 1981-01-01\n       14 | Karnivool              | 1997-01-01\n       15 | Birds of Tokyo         | 2004-01-01\n       16 | Bodyjar                | 1990-01-01\n(16 rows)\nmusic=#<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Getting Help<\/h2>\n\n\n\n<p>You can type the following to return the help list:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>psql --help<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"> psql is the PostgreSQL interactive terminal.\n \n\n Usage:\n &nbsp; psql [OPTION]... [DBNAME [USERNAME]]\n \n\n General options:\n &nbsp; -c, --command=COMMAND&nbsp; &nbsp; run only single command (SQL or internal) and exit\n &nbsp; -d, --dbname=DBNAME&nbsp; &nbsp; &nbsp; database name to connect to (default: \"barney\")\n &nbsp; -f, --file=FILENAME&nbsp; &nbsp; &nbsp; execute commands from file, then exit\n &nbsp; -l, --list &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; list available databases, then exit\n &nbsp; -v, --set=, --variable=NAME=VALUE\n &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; set psql variable NAME to VALUE\n &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; (e.g., -v ON_ERROR_STOP=1)\n &nbsp; -V, --version&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; output version information, then exit\n &nbsp; -X, --no-psqlrc&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; do not read startup file (~\/.psqlrc)\n &nbsp; -1 (\"one\"), --single-transaction\n &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; execute as a single transaction (if non-interactive)\n &nbsp; -?, --help[=options] &nbsp; &nbsp; show this help, then exit\n &nbsp; &nbsp; &nbsp; --help=commands&nbsp; &nbsp; &nbsp; list backslash commands, then exit\n &nbsp; &nbsp; &nbsp; --help=variables &nbsp; &nbsp; list special variables, then exit\n \n\n Input and output options:\n &nbsp; -a, --echo-all &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; echo all input from script\n &nbsp; -b, --echo-errors&nbsp; &nbsp; &nbsp; &nbsp; echo failed commands\n &nbsp; -e, --echo-queries &nbsp; &nbsp; &nbsp; echo commands sent to server\n &nbsp; -E, --echo-hidden&nbsp; &nbsp; &nbsp; &nbsp; display queries that internal commands generate\n &nbsp; -L, --log-file=FILENAME&nbsp; send session log to file\n &nbsp; -n, --no-readline&nbsp; &nbsp; &nbsp; &nbsp; disable enhanced command line editing (readline)\n &nbsp; -o, --output=FILENAME&nbsp; &nbsp; send query results to file (or |pipe)\n &nbsp; -q, --quiet&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; run quietly (no messages, only query output)\n &nbsp; -s, --single-step&nbsp; &nbsp; &nbsp; &nbsp; single-step mode (confirm each query)\n &nbsp; -S, --single-line&nbsp; &nbsp; &nbsp; &nbsp; single-line mode (end of line terminates SQL command)\n \n\n Output format options:\n &nbsp; -A, --no-align &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; unaligned table output mode\n &nbsp; &nbsp; &nbsp; --csv&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CSV (Comma-Separated Values) table output mode\n &nbsp; -F, --field-separator=STRING\n &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; field separator for unaligned output (default: \"|\")\n &nbsp; -H, --html &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; HTML table output mode\n &nbsp; -P, --pset=VAR[=ARG] &nbsp; &nbsp; set printing option VAR to ARG (see \\pset command)\n &nbsp; -R, --record-separator=STRING\n &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; record separator for unaligned output (default: newline)\n &nbsp; -t, --tuples-only&nbsp; &nbsp; &nbsp; &nbsp; print rows only\n &nbsp; -T, --table-attr=TEXT&nbsp; &nbsp; set HTML table tag attributes (e.g., width, border)\n &nbsp; -x, --expanded &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; turn on expanded table output\n &nbsp; -z, --field-separator-zero\n &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; set field separator for unaligned output to zero byte\n &nbsp; -0, --record-separator-zero\n &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; set record separator for unaligned output to zero byte\n \n\n Connection options:\n &nbsp; -h, --host=HOSTNAME&nbsp; &nbsp; &nbsp; database server host or socket directory (default: \"local socket\")\n &nbsp; -p, --port=PORT&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; database server port (default: \"5432\")\n &nbsp; -U, --username=USERNAME&nbsp; database user name (default: \"barney\")\n &nbsp; -w, --no-password&nbsp; &nbsp; &nbsp; &nbsp; never prompt for password\n &nbsp; -W, --password &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; force password prompt (should happen automatically)\n \n\n For more information, type \"\\?\" (for internal commands) or \"\\help\" (for SQL\n commands) from within psql, or consult the psql section in the PostgreSQL\n documentation.\n \n\n Report bugs to &lt;pgsql-bugs@lists.postgresql.org&gt;. <\/pre>\n\n\n\n<p>As you can see, the help list contains further commands you can use to get help about specific areas. <\/p>\n\n\n\n<p>For example, you can use <code>psql --help=commands<\/code> to return a list of commands, or psql -V to <a href=\"https:\/\/database.guide\/how-to-check-your-psql-client-version\/\" title=\"How to Check your psql Client Version\">return the psql version<\/a>. <\/p>\n\n\n\n<p>And while you&#8217;re using psql, you can type <code>\\?<\/code> to get a list of internal commands, or <code>\\help<\/code> to return a list of SQL commands.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>psql is a terminal based interface for PostgreSQL. psql enables you to administer PostgreSQL from the command line interface (CLI) as an alternative to using a graphical user interface (GUI), such as pgAdmin, Postico, Azure Data Studio, etc.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[59],"tags":[142,20],"class_list":["post-15024","post","type-post","status-publish","format-standard","hentry","category-database-tools","tag-psql","tag-what-is"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/15024","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=15024"}],"version-history":[{"count":5,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/15024\/revisions"}],"predecessor-version":[{"id":15033,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/15024\/revisions\/15033"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=15024"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=15024"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=15024"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}