The Most Common psql Commands according to ChatGPT
created in January 2023, last update in October 2023
I asked ChatGPT what the most common psql commands, and found the answer so convincing that I wanted to store them on my blog.
Q: what are the most common psql commands?
ChatGPT:
SELECT: retrieves data from one or more tables
INSERT: inserts data into a table
UPDATE: updates existing data in a table
DELETE: deletes data from a table
ALTER: modifies the structure of a table
CREATE: creates a new database, table, index, etc.
DROP: deletes an entire database, table, index, etc.
GRANT: assigns privileges to a user
REVOKE: removes privileges from a user
COMMIT: saves changes made during a transaction
ROLLBACK: undoes changes made during a transaction.
Q: what are the most common psql options, for instance when you want to list all tables?
ChatGPT:
-U or --username: specifies the PostgreSQL username to connect as
-d or --dbname: specifies the name of the database to connect to
-h or --host: specifies the database server's host name or IP address
-p or --port: specifies the database server's port number
-W or --password: prompts for a password before connecting to the database
-l or --list: lists all available databases on the server
-c or --command: executes a single SQL command and quits
-f or --file: executes commands from a file and quits
-q or --quiet: doesn't display any message
-v or --set: sets psql variables, for example: -v table=mytable (\l+ in psql will show all tables including mytable).
Example:
psql -U [username] -d [database_name] -c "\dt"
Q: what are the most common backslash commands in psql?
ChatGPT:
\l or \list: lists all available databases on the server
\c or \connect: connects to a database
\dt or \dT: displays tables in the current database
\d [table_name]: displays the structure of a table
\du or \dU: displays the list of users and their roles
\q or \quit: quits psql
? or \help: displays help on backslash commands
\x or \x auto: toggles expanded table output mode
\g or \g [file]: executes the current query and optionally saves the result to a file
\i [file]: executes commands from a file.