PostgreSQL Commands

PostgreSQL is a popular open-source relational database management system that is widely used by developers and organizations around the world. It is known for its powerful features, scalability, and flexibility. One of the primary ways to interact with a PostgreSQL database is through the psql command-line interface.

In this blog post, we will explore some of the most useful psql commands that every PostgreSQL developer should know. From creating databases and tables to querying data and managing users, we'll cover a wide range of commands that will help you become more efficient and effective in working with PostgreSQL. Whether you're a beginner or an experienced PostgreSQL developer, this post will provide you with the knowledge you need to take your skills to the next level. So, let's dive in and discover some of the most essential psql commands!

1. \du: Lists all users in the current database and their roles.

2. \dt: Lists all tables in the current database.

3. \d <table_name>: Provides a description of a specified table, including the columns, their data types, and any constraints.

4. \i <file_name>: Executes the SQL commands contained in the specified file.

5. \e: Opens the default editor to edit the current command or query.

6. \timing: Toggles display of the query execution time after each query.

7. \! <shell_command>: Executes the specified shell command from within psql.

8. \conninfo: Shows information about the current database connection, including the username, database name, host, and port.

9. \x: Toggles expanded display mode, which shows query results in a more readable format.

10. \h: Lists all available psql commands with brief descriptions.

11. psql -d <database_name>: Connect to a database.

12. createdb <database_name>: Create a new database.

13. \l: List databases.

14. \q: Quit PSQL.

15. \connect <database_name> or \c <database_name>: Connect to a specific database.

Note that some of these commands have additional options and arguments that can be used to modify their behavior. You can find more information on these options and arguments in the PostgreSQL documentation.

Commonly used SQL Commands

---

1. Creating a Superuser

Using SQL (within psql or via a script):

-- Using CREATE ROLE:
CREATE ROLE my_superuser WITH LOGIN SUPERUSER PASSWORD 'your_secure_password';

-- Alternatively, using CREATE USER (they’re almost interchangeable in PostgreSQL):
CREATE USER my_superuser WITH SUPERUSER PASSWORD 'your_secure_password';

Replace my_superuser and 'your_secure_password' with your desired username and a strong, secure password.

Tip: On Unix-based systems, you can also create a superuser directly from the shell:

sudo -u postgres createuser --superuser my_superuser

---

2. Commonly Used psql Meta-Commands

When you’re inside the psql shell, these slash (\) commands are super useful:

• \l or \list: List all databases.

• \c [database_name]: Connect to a specific database.

• \du: List all roles (users).

• \d: List all database objects (tables, views, indexes, etc.).

• \dt: List only tables.

• \dv: List views.

• \df: List functions.

• \i [file_name.sql]: Execute commands from an external file.

• \?: Display help on all meta-commands.

• \q: Quit the psql session.

---

3. Common SQL Commands

Creating a Database and Table:

-- Create a new database:
CREATE DATABASE my_database;

-- Connect to the database (if not already connected, use psql's \c command):
\c my_database

-- Create a table:
CREATE TABLE my_table (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Manipulating Data:

-- Inserting Data:
INSERT INTO my_table (name) VALUES ('Alice'), ('Bob');

-- Querying Data:
SELECT * FROM my_table;

-- Updating Data:
UPDATE my_table SET name = 'Charlie' WHERE id = 1;

-- Deleting Data:
DELETE FROM my_table WHERE id = 2;

---

4. Extra Tips & Tricks

• Direct Connection via Command Line:
  Use this command to jump straight into a specific database:

    psql -U postgres -d my_database
  

• Scripting:
  Write your SQL commands in a file (e.g., script.sql) and run them with:

    \i script.sql
  

• Security First:
  Always safeguard your credentials and avoid hard-coding sensitive information in your scripts.

---