How to Exit psql: \q, exit, quit and Ctrl+D

You are done, you type exit, and psql answers with postgres-# and waits.
Nearly two thousand people upvoted the original
Stack Overflow question
about this, and the accepted answer is two characters long: \q. This fix
covers that command, the exit/quit aliases PostgreSQL 11 added, Ctrl+D,
and the three situations where none of them seem to work: a paused pager, an
unterminated string, and a Windows console.

The command that works everywhere: \q

postgres=> \q

\q (long form \quit) is a psql meta-command, so it never touches the
server. The psql manual
adds one nuance worth knowing: "In a script file, only execution of that
script is terminated." So a \q inside a file you ran with \i or
psql -f ends that file, not your interactive session.

exit and quit (PostgreSQL 11 and newer)

Since psql 11 you can also type exit or quit on their own line. The
PostgreSQL 11 release notes
describe it as: "Allow quit and exit to exit psql when given with no prior
input", and: "Also print hints about how to exit when quit and exit are
used alone on a line while the input buffer is not empty."

The rules, straight from psql's input loop (src/bin/psql/mainloop.c):

• The word must be at the very start of the line, no leading whitespace.
• Nothing may follow it except whitespace and an optional semicolon.
• The query buffer must be empty. If it is not, psql keeps the text as part of your statement and prints a hint instead.

That last rule explains the classic trap. You ran SELECT 1 without a
semicolon, the prompt changed from => to ->, and now exit does this:

postgres=> SELECT 1
postgres-> exit
Use \q to quit.
postgres->

On psql 10 and older, exit is just an unknown word: with a semicolon you get
ERROR: syntax error at or near "exit", without one the prompt keeps waiting.
Check your client with psql --version; the
which version of PostgreSQL am I running
fix shows the difference between client and server versions.

Ctrl+D (end of file)

On Linux and macOS, Ctrl+D on an empty input line sends EOF and psql
exits like a shell would. If you have already typed anything on the line, even
a space, Ctrl+D does nothing; clear the line first. On the Windows console
the EOF key is Ctrl+Z followed by Enter, not Ctrl+D.

Stuck in a half-typed statement

The second character of the prompt tells you why psql wants more input. The
manual defines the continuation marker as: - if the command simply was not
terminated yet, * for an unfinished /* ... */ comment, ' for an
unfinished quoted string, " for an unfinished quoted identifier, $ for an
unfinished dollar-quoted string, and ( for an unmatched left parenthesis.

postgres=> SELECT 'hello
postgres'> \q
postgres'>

Inside an open quote, backslash commands are not recognised at all: that \q
became part of the string literal. Get out in this order:

1. Close what is open: type the missing ', ", $$, */ or ) and press Enter. The prompt returns to ->.
2. Reset the buffer with \r (\reset), which the manual defines as "Resets (clears) the query buffer." The prompt returns to =>.
3. Now \q, exit or Ctrl+D work as expected.

On Unix builds, Ctrl+C at the prompt clears the input buffer too. psql says
so itself when you type help mid-statement: "Use \? for help or press
control-C to clear the input buffer." When a query is actually running,
Ctrl+C cancels it and returns you to the prompt rather than quitting.

The pager is not psql

If the screen shows : or (END) at the bottom and ignores \q, you are
inside the pager, not psql. Press q to return to the prompt.

psql only invokes the pager "when the output is to a terminal and will not fit
on the screen", and it uses the program named by PSQL_PAGER or PAGER,
"otherwise a platform-dependent default program (such as more) is used". To
stop it for the session:

postgres=> \pset pager off
Pager usage is off.

or start psql with psql --pset=pager=off when you script around it.

Windows quirks

Two behaviours differ on Windows, both visible in psql's own source:

• Type exit inside an unfinished quote or comment and Unix psql prints Use control-D to quit., while the Windows build prints Use control-C to quit.: there is no readline-style buffer clearing, and Ctrl+C ends the whole psql process.
• The default pager is more, which still exits with q.

Supabase-hosted sessions

Nothing changes when the server is Supabase: \q closes the pooled
connection and hands you back to the shell. What matters is which port you
connected on. Supabase's
connection docs
give the direct string as postgresql://postgres:[password]@db.[ref].supabase.co:5432/postgres
and the Supavisor pooler as postgres://postgres.[ref]:[password]@aws-0-eu-central-1.pooler.supabase.com:5432/postgres
(session mode) or port 6543 (transaction mode) for a Frankfurt project.
For an interactive psql session prefer the direct string or session mode;
Supabase positions transaction mode for "serverless or edge functions", and
it "does not support prepared statements".

If you only wanted to switch to another database rather than leave, that is
\c, covered in the
switch databases in psql
fix. And if you cannot get in at all, start with the
psql: command not found
or peer authentication failed
fixes.

Common mistakes

• Typing exit; on psql 10 or older. It is a syntax error there. Use \q.
• Typing exit after an unterminated statement. psql prints Use \q to quit.; type \q, or \r first.
• Mistaking the pager for psql. (END) at the bottom means press q.
• Quitting before COMMIT. An open transaction is rolled back. Verify your writes first; show tables in PostgreSQL covers \dt and friends.

Related

• Switch Databases in psql: The \connect Command
• Fix: psql: command not found (Install PostgreSQL Client)
• Which Version of PostgreSQL Am I Running?
• PostgreSQL SHOW TABLES / DESCRIBE TABLE (psql + Supabase)
• PostgreSQL DESCRIBE TABLE: The psql \d Equivalent
• Fix: Peer Authentication Failed for User "postgres"

---

Originally published at https://www.iloveblogs.blog