How to Work With a PostgreSQL Database Using psql Commands
PostgreSQL includes a handy command line tool named psql. This program opens an interactive text interface that provides access to your databases. you could use it to find out various information about your database structures and metadata.
You can also use psql to execute any SQL commands to create tables, insert rows, and query existing data.
Using the psql Program
The psql program runs as an interactive terminal that allows you to send commands to a PostgreSQL database. If you have PostgreSQL installed, you should be able to run it on the command line by typing:
If you get apsql: command not founderror then PostgreSQL is either not installed or not on your path. In the latter case, confirm toset your PATH environment variableappropriately.
it’s possible to connect to a specific database by naming it after thepsqlcommand:
By default, your PostgreSQL server is probably running on port 5432. If it isn’t, you can specify the port to connect to using the-poption:
Listing and Connecting to Databases
Once you’re in the interactive shell, you’re able to run various psql commands. These include commands to list available databases, change output style, etc. Additionally, you can execute allstandard SQL commandsthat PostgreSQL supports.
The interactive shell prompt includes the name of the database you are connected to. Your psql command line should look like this:
A good first command to test and get your bearings is\l. This lists the databases available on the PostgreSQL server you’re connected to:
The command produces output in a tabular format, showing the name of each database along with its owner and other attributes.
You can connect to a different database using the\ccommand, followed by the name of your target database:
Type \c on its own to print a message telling you which database you’re currently connected to.
Fetching Table Metadata
Without any following arguments, the\dcommand lists tables, views, and sequences:
you’re able to also use \d to describe a specific table. Simply include the name of the table after the \d:
These commands produce different outputs, but both use a consistent tabular format.
You can list all the available tables in the current database with the\dtcommand:
The output presents each table on a single row, with minimal information.
There are several other, similar commands to view various types of table information. For example, to list available views, use\dv:
Like the \dt command, \dv shows the schema, name, type, and owner of each view.
Miscellaneous Commands
There are many, many psql commands available, far too many to cover in a short article. Possibly the most useful command to remember is the one that tells you about all these commands. Use?and you’ll see a list of each command with a synopsis of how to use it and an explanation of what it does.
\sshows you a history of what you’ve previously typed, including SQL statements and psql commands.
The\ecommand is highly useful, especially if you’re experimenting with long SQL statements. This command opens your default command-line text editor with the last SQL command. you’re able to then edit that command, save and quit your editor, and psql will rerun it with your changes.
psql Commands Are Great For Database Inspection
The psql interactive shell is a versatile program. It allows you to execute standard SQL commands and special psql commands. The latter give you access to all sorts of information which is particularly useful if you’re performance tweaking or designing a complex database.
Remember to use the ? command to query the huge list of available commands.
Unlock a world of entertainment possibilities with this clever TV hack.
Revolutionize your driving experience with these game-changing CarPlay additions.
Sometimes the smallest cleaning habit makes the biggest mess.
When your rival has to bail out your assistant.
This small feature makes a massive difference.
Goodbye sending links via other apps.
