Skip to main content
0 reactions

How to Connect to PostgreSQL with a Connection URI

Featured card titled Connect with a Connection URI, with a terminal window showing the parts of a URI, postgresql://, postgres@localhost and :5432/notes, then the notes=# prompt.
Connect to PostgreSQL with a connection URI.

A connection URI puts everything psql needs to reach a database into one string: the user, the server, the port and the database name. Apps and hosting services often give you one, and psql accepts it as it is.

Every command and output on this page is real, from PostgreSQL 19 on macOS.

Syntax

SyntaxPlain text
postgresql://[user@][host][:port][/dbname][?option=value]
  • postgresql://: says this is a PostgreSQL URI. postgres:// works too.
  • user: the database user to connect as.
  • host: the computer the server runs on. localhost means your own computer.
  • port: the server’s port. Leave it out to use the standard 5432.
  • dbname: the database to connect to.
  • ?option=value: optional connection settings, such as sslmode.

Every part after postgresql:// is optional, as the connection URI documentation (opens in a new tab) explains. In Terminal, put the whole URI in double quotes.

Step 1: Create an Example Database

This guide connects to a small database named notes. To use your own, skip this step. Connect with psql, as Part 2 of the PostgreSQL Tutorial shows, and run:

postgres=#SQL
CREATE DATABASE notes;
OutputPlain text
CREATE DATABASE

Then type \q to go back to Terminal.

Step 2: Connect with a URI

TerminalBash
psql "postgresql://postgres@localhost:5432/notes"
OutputPlain text
Password for user postgres:
psql (19.0)
Type "help" for help.

notes=#

psql asks for the password of postgres, then opens the notes database, as the notes=# prompt shows.

Step 3: Check the Connection

notes=#Plain text
\conninfo
OutputPlain text
      Connection Information
      Parameter       |   Value
----------------------+-----------
 Database             | notes
 Client User          | postgres
 Host                 | localhost
 Host Address         | ::1
 Server Port          | 5432
 Options              |
 Protocol Version     | 3.0
 Password Used        | true
 GSSAPI Authenticated | false
 Backend PID          | 16412
 SSL Connection       | false
 Superuser            | on
 Hot Standby          | off
(13 rows)

Because the URI names a host, psql connected over the network port instead of a local socket: Host is localhost, and ::1 is localhost’s IPv6 address.

Step 4: Leave Out the Parts You Don’t Need

Without a port, psql uses 5432:

TerminalBash
psql "postgresql://postgres@localhost/notes"
OutputPlain text
Password for user postgres:
psql (19.0)
Type "help" for help.

notes=#

Clean Up

To remove the example database, connect as usual and run:

postgres=#SQL
DROP DATABASE notes;
OutputPlain text
DROP DATABASE

Warning: DROP DATABASE deletes the database and everything in it at once. There’s no undo, so check the name before you press Return.

Common Mistakes

MistakeWhat happensDo this instead
Leaving out the quotes when the URI has a ?zsh: no matches found: postgresql://postgres@localhost/notes?sslmode=disablePut the URI in double quotes. zsh treats ? as a wildcard.
A typo in the database namepsql: error: connection to server at "localhost" (::1), port 5432 failed: FATAL: database "note" does not existCheck the name with \l.

FAQ

Can I put the password in the URI?

You can, as postgresql://user:password@host/dbname, but Terminal saves every command you type in its history, so the password stays there in plain text. Let psql ask for it instead.

Is postgres:// the same as postgresql://?

Yes. The documentation (opens in a new tab) accepts both.

Conclusion

Pass a URI in double quotes to psql to connect in one go: postgresql://, then the user, host, port and database, with only the parts you need. \conninfo confirms where you landed.

Additional Resources

Postgres, PostgreSQL and the Slonik Logo are trademarks or registered trademarks of the PostgreSQL Community Association of Canada, and used with their permission.

0 comments