1. Connect to PostgreSQL:
psql -U username -d database_name -h host -p port
Explanation:
-
psql: Command-line tool for interacting with PostgreSQL. -
-U username: Specifies the PostgreSQL username for authentication. -
-d database_name: Specifies the name of the database to connect to. -
-h host: Specifies the host where the PostgreSQL server is running. -
-p port: Specifies the port number on which the PostgreSQL server is listening.
2. List Databases:
\l
Explanation:
-
\l: Meta-command in the psql interactive terminal to list all available databases.
3. Create a Database:
CREATE DATABASE database_name;
Explanation:
-
CREATE DATABASE: SQL command to create a new PostgreSQL database. -
database_name: Replace with the desired name for the new database.
4. Drop a Database:
DROP DATABASE database_name;
Explanation:
-
DROP DATABASE: SQL command to delete an existing PostgreSQL database. -
database_name: Replace with the name of the database to be dropped.
5. Connect to a Database:
\c database_name;
Explanation:
-
\c: Meta-command in the psql interactive terminal to connect to a specific database. -
database_name: Replace with the name of the database to connect to.
6. List Tables:
\dt
Explanation:
-
\dt: Meta-command to list all tables in the currently connected database.
7. Create a Table:
CREATE TABLE table_name (
column1 data_type,
column2 data_type,
...
);
Explanation:
-
CREATE TABLE: SQL command to create a new table in the database. -
table_name: Name of the table to be created. -
(column1 data_type, column2 data_type, ...): Define the columns and their data types.
8. Insert Data:
INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
Explanation:
-
INSERT INTO: SQL command to insert data into a table. -
table_name: Name of the table to insert data into. -
(column1, column2, ...): Specify the columns to insert data. -
VALUES (value1, value2, ...): Provide the corresponding values for the columns.
9. Select Data:
SELECT * FROM table_name;
Explanation:
-
SELECT *: SQL command to retrieve all columns from a table. -
FROM table_name: Specify the table from which to retrieve data.
10. Update Data:
UPDATE table_name SET column1 = new_value WHERE condition;
Explanation:
-
UPDATE: SQL command to modify existing data in a table. -
table_name: Name of the table to be updated. -
SET column1 = new_value: Specify the column to be updated and its new value. -
WHERE condition: Define the condition to identify the rows to be updated.
11. Delete Data:
DELETE FROM table_name WHERE condition;
Explanation:
-
DELETE FROM: SQL command to remove data from a table. -
table_name: Name of the table from which to delete data. -
WHERE condition: Define the condition to identify the rows to be deleted.
12. Create an Index:
CREATE INDEX index_name ON table_name (column);
Explanation:
-
CREATE INDEX: SQL command to create an index on a table. -
index_name: Name of the index to be created. -
ON table_name: Specify the table on which to create the index. -
(column): Define the column on which the index is created.
13. Backup and Restore:
- To perform a backup:
pg_dump -U username -d database_name > backup.sql
Explanation:
-
pg_dump: Command-line tool to create a backup of a PostgreSQL database. -
-U username: Specifies the PostgreSQL username for authentication. -
-d database_name: Specifies the name of the database to be backed up. > backup.sql: Redirects the backup output to a file named "backup.sql".To restore from a backup:
psql -U username -d database_name < backup.sql
Explanation:
-
psql: Command-line tool to interact with PostgreSQL. -
-U username: Specifies the PostgreSQL username for authentication. -
-d database_name: Specifies the name of the database to be restored. -
< backup.sql: Reads the backup file and restores the database.
These commands are fundamental for working with PostgreSQL databases. Explore the PostgreSQL Documentation for more advanced features and detailed explanations.
Top comments (1)
Good article