Basic Postgres Commands
PostgreSQL commands can be broadly divided into command-line tools and SQL commands or functions you’d use within the psql
interface. I’ll provide an overview of both:
Command-Line Tools:
-
psql
: The PostgreSQL command-line client.- Connect to a database:
psql -h [hostname] -U [username] -d [database_name]
- List all databases:
psql -l
- Connect to a database:
-
createdb
: Create a new PostgreSQL database.- Create a database:
createdb [database_name]
- Create a database:
-
dropdb
: Remove a PostgreSQL database.- Drop a database:
dropdb [database_name]
- Drop a database:
-
createuser
: Create a new PostgreSQL role/user.- Create a new user:
createuser [username]
- Create a new user:
-
dropuser
: Remove a PostgreSQL role/user.- Drop a user:
dropuser [username]
- Drop a user:
-
pg_dump
: Create a backup of a PostgreSQL database.- Create a backup:
pg_dump [database_name] > backup.sql
- Create a backup:
-
pg_restore
: Restore a PostgreSQL database from a backup.- Restore a backup:
pg_restore -d [database_name] backup.sql
- Restore a backup:
psql
Commands:
When you’re inside the psql
interface, you can use the following commands:
-
\l
or\list
: List all databases. -
\c [database_name]
: Connect to another database. -
\dt
: List all tables in the current database. -
\du
: List all users/roles. -
\d [table_name]
: Describe a table (shows columns, types, etc.). -
\h
: Get help on SQL commands. -
\?
: List allpsql
commands. -
\x
: Toggle expanded display. Useful when the rows have a lot of data and don’t fit the screen width. -
\a
: Toggle between aligned and unaligned output format. -
\timing
: Toggle timing of commands to see how long they take. -
\i [filename]
: Execute SQL commands from a file. -
\o [filename]
: Send query results to a file. -
\q
: Quitpsql
.
SQL Commands:
-
Data Retrieval
SELECT * FROM [table_name];
: Select all records from a table.SELECT column1, column2 FROM [table_name] WHERE condition;
: Select specific columns based on a condition.
-
Data Modification
INSERT INTO [table_name] (column1, column2) VALUES (value1, value2);
: Insert a new record.UPDATE [table_name] SET column1 = value1 WHERE condition;
: Update records.DELETE FROM [table_name] WHERE condition;
: Delete records.
-
Table Operations
CREATE TABLE [table_name] (...);
: Create a new table.DROP TABLE [table_name];
: Delete a table.ALTER TABLE [table_name] ...;
: Modify an existing table (e.g., add/remove columns).
-
Database Operations
CREATE DATABASE [database_name];
: Create a new database.DROP DATABASE [database_name];
: Delete a database.
-
Role/User Operations
CREATE USER [username] WITH PASSWORD '[password]';
: Create a new user.DROP USER [username];
: Delete a user.GRANT ... ON ... TO [username];
: Grant privileges to a user.
This is just a basic overview. PostgreSQL has a vast set of functionalities, and the commands and functionalities you use will depend on your specific tasks and requirements.