Commands
In this article, “Useful LVM commands with examples,” we will explore a selection of essential LVM commands commonly encountered by Linux sysadmins, engineers, and enthusiasts
Discover 30 essential PostgreSQL commands to enhance your database management skills. This comprehensive guide covers key commands, examples, and tips for efficient PostgreSQL use. Perfect for beginners and experts alike!
PostgreSQL, an advanced and powerful open-source relational database system, offers a plethora of commands that are essential for database management. Mastering these commands can significantly enhance your ability to interact with and manage PostgreSQL databases effectively. In this post, we’ll explore 30 essential PostgreSQL commands, complete with examples and explanations to ensure you get the most out of your PostgreSQL experience.
1. Connecting to a Database |
Before you can execute any commands, you need to connect to your PostgreSQL database. Use the following command:
psql -h hostname -d dbname -U username
2. Listing All Databases |
To list all the databases in your PostgreSQL server, use:
\l
This command is particularly useful when you want to see an overview of all existing databases.
3. Creating a New Database |
Creating a new database is straightforward with the following command:
CREATE DATABASE dbname;
Replace dbname with your desired database name.
4. Connecting to a Specific Database |
After creating a database, you might want to connect to it:
\c dbname
This command switches your connection to the specified database.
5. Listing All Tables |
To see all the tables in your current database, use:
\dt
This command provides a quick overview of the tables.
Photo by admingeek from Infotechys
6. Creating a New Table |
Creating a table involves specifying the table structure:
CREATE TABLE tablename (
id SERIAL PRIMARY KEY,
column1 TEXT,
column2 INTEGER
);
Modify tablename and column definitions as needed.
7. Inserting Data into a Table |
To insert data into a table, use the following command:
INSERT INTO tablename (column1, column2) VALUES ('value1', 123);
8. Selecting Data from a Table |
Selecting data allows you to view the table contents:
SELECT * FROM tablename;
9. Updating Data in a Table |
To update existing data, execute:
UPDATE tablename SET column1 = 'new_value' WHERE id = 1;
10. Deleting Data from a Table |
Deleting data is just as simple:
DELETE FROM tablename WHERE id = 1;
11. Dropping a Table |
To remove a table and its data:
DROP TABLE tablename;
12. Adding a Column to a Table |
If you need to add a new column to an existing table:
ALTER TABLE tablename ADD COLUMN new_column TEXT;
13. Dropping a Column from a Table |
Conversely, to remove a column:
ALTER TABLE tablename DROP COLUMN column_name;
14. Renaming a Table |
To rename a table:
ALTER TABLE oldname RENAME TO newname;
15. Creating an Index |
Indexes improve query performance:
CREATE INDEX indexname ON tablename (columnname);
16. Dropping an Index |
To remove an index:
DROP INDEX indexname;
17. Viewing Table Structure |
To view the structure of a table:
\d tablename
18. Granting Privileges |
To grant privileges to a user:
GRANT ALL PRIVILEGES ON DATABASE dbname TO username;
19. Revoking Privileges |
Revoking privileges is equally important:
REVOKE ALL PRIVILEGES ON DATABASE dbname FROM username;
20. Backing Up a Database |
For backup purposes:
pg_dump dbname > backupfile.sql
21. Restoring a Database |
To restore a database from a backup:
psql dbname < backupfile.sql
22. Executing SQL File |
Run a SQL file using:
\i filename.sql
23. Exiting psql |
To exit the psql interface:
\q
24. Changing User Password |
Change a user’s password with:
ALTER USER username WITH PASSWORD 'newpassword';
25. Viewing Active Connections |
To see all active connections:
SELECT * FROM pg_stat_activity;
26. Terminating a Connection |
Terminate a specific connection:
SELECT pg_terminate_backend(pid);
27. Checking PostgreSQL Version |
To check your PostgreSQL version:
SELECT version();
28. Vacuuming a Database |
For database maintenance:
VACUUM;
29. Analyzing a Database |
To gather statistics for query optimization:
ANALYZE;
30. Setting Configuration Parameters |
Change configuration settings with:
ALTER SYSTEM SET parameter = 'value';
| Command | Description |
|---|---|
psql -h hostname -d dbname -U username | Connect to a PostgreSQL database |
\l | List all databases |
CREATE DATABASE dbname; | Create a new database |
\c dbname | Connect to a specific database |
\dt | List all tables in the current database |
CREATE TABLE tablename (...) | Create a new table |
INSERT INTO tablename (columns) VALUES (...); | Insert data into a table |
SELECT * FROM tablename; | Select data from a table |
UPDATE tablename SET column = value WHERE ...; | Update data in a table |
DELETE FROM tablename WHERE ...; | Delete data from a table |
DROP TABLE tablename; | Drop a table |
ALTER TABLE tablename ADD COLUMN ...; | Add a column to a table |
ALTER TABLE tablename DROP COLUMN ...; | Drop a column from a table |
ALTER TABLE oldname RENAME TO newname; | Rename a table |
CREATE INDEX indexname ON tablename (...); | Create an index |
DROP INDEX indexname; | Drop an index |
\d tablename | View table structure |
GRANT ALL PRIVILEGES ON DATABASE ... TO ...; | Grant privileges to a user |
REVOKE ALL PRIVILEGES ON DATABASE ... FROM ...; | Revoke privileges from a user |
pg_dump dbname > backupfile.sql | Backup a database |
psql dbname < backupfile.sql | Restore a database |
\i filename.sql | Execute a SQL file |
\q | Exit psql |
ALTER USER username WITH PASSWORD ...; | Change user password |
SELECT * FROM pg_stat_activity; | View active connections |
SELECT pg_terminate_backend(pid); | Terminate a connection |
SELECT version(); | Check PostgreSQL version |
VACUUM; | Vacuum a database |
ANALYZE; | Analyze a database |
ALTER SYSTEM SET parameter = value; | Set configuration parameters |
By integrating these commands into your workflow, you will be well-equipped to handle various database tasks with ease and efficiency.
Related Posts
In this article, “Useful LVM commands with examples,” we will explore a selection of essential LVM commands commonly encountered by Linux sysadmins, engineers, and enthusiasts
Discover the 25 basic Docker commands every beginner needs to know. This comprehensive guide covers everything from pulling images to managing containers, complete with examples
Discover 12 essential Deja Dup commands to enhance your backup management on Linux. Learn how to perform, verify, encrypt, and schedule backups effectively using the