postgresql

Common SQL queries and commands for working with PostgreSQL databases and schemas.

User Authentication

Commands for logging into PostgreSQL and managing sessions.

psql -U <username> -d <database_name>

psql -U postgres -d my_database

Replace <username> with your PostgreSQL user and <database_name> with your desired database.

Permissions

Commands for managing user roles and permissions in PostgreSQL.

Create a user

Creates a new user in PostgreSQL with a password.

CREATE USER <username> WITH PASSWORD '<password>';

CREATE USER john_doe WITH PASSWORD 'secret123';

Replace <username> with the PostgreSQL user and <password> with their password.

Grant privileges to a user

Grants specific permissions to a user on a database.

GRANT <privilege_type> ON <table_name> TO <username>;

GRANT SELECT ON users TO john_doe;

Replace <privilege_type> with desired permissions (e.g., SELECT, INSERT, UPDATE).

Grant all privileges to a user

Grants all privileges on a database to a user.

GRANT ALL PRIVILEGES ON DATABASE <database_name> TO <username>;

GRANT ALL PRIVILEGES ON DATABASE my_database TO john_doe;

This gives the user full control over the database.

Grant table privileges

Grants specific privileges on all tables in a schema.

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA <schema_name> TO <username>;

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO john_doe;

Replace public with the target schema if needed.

Grant role to a user

Assigns a role to a user.

GRANT <role_name> TO <username>;

GRANT db_admin TO john_doe;

This allows the user to inherit the granted role's privileges.

Revoke privileges from a user

Revokes specific permissions from a user.

REVOKE <privilege_type> ON <table_name> FROM <username>;

REVOKE SELECT ON users FROM john_doe;

Drop a user

Deletes a user from PostgreSQL.

DROP USER <username>;

DROP USER john_doe;

Database Management

Commands related to database creation, deletion and listing.

Create a database

Creates a new database in PostgreSQL.

CREATE DATABASE <database_name>;

CREATE DATABASE my_database;

Ensure the database name is unique.

Drop a database

Deletes a database from PostgreSQL.

DROP DATABASE <database_name>;

DROP DATABASE my_database;

Be cautious, as this will permanently delete the database.

List all databases

Lists all databases in PostgreSQL.

\l

You can also use SELECT database_name FROM pg_database; for a more detailed output.

Schema Management

Commands related to managing schemas in PostgreSQL.

Create a schema

Defines a new schema in the database.

CREATE SCHEMA <schema_name>;

CREATE SCHEMA analytics;

Schemas help organize database objects.

Drop a schema

Removes a schema and all objects within it.

DROP SCHEMA <schema_name> CASCADE;

DROP SCHEMA analytics CASCADE;

Use CASCADE to remove dependent objects.

Table Management

Commands related to table creation, modification and deletion.

Create a table

Creates a new table in the database.

CREATE TABLE <table_name> (
  id SERIAL PRIMARY KEY,
  column_name VARCHAR(100),
  created_at TIMESTAMP
);

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(100),
  created_at TIMESTAMP
);

Ensure the column data types and constraints match your needs.

List all tables

Shows all tables in the current database.

\dt

You can also use SELECT * FROM information_schema.tables; for more info.

Drop a table

Deletes a table from the database.

DROP TABLE <table_name>;

DROP TABLE users;

This will permanently remove the table and its data.

Querying Data

Commands for querying and manipulating data in PostgreSQL.

Select all table data

Retrieves all rows from a specific table.

SELECT * FROM <table_name>;

SELECT * FROM users;

Be cautious with large tables, as this may return a lot of data.

Select specific columns

Retrieves specific columns from a table.

SELECT <column_name> FROM <table_name>;

SELECT username, created_at FROM users;

Avoid using SELECT * unless necessary for performance reasons.

Insert table row

Inserts a new row of data into a table.

INSERT INTO <table_name> (column_name, created_at)
VALUES ('<value>', NOW());

INSERT INTO users (username, created_at)
VALUES ('john_doe', NOW());

Make sure values match the column data types.

Insert multiple rows

Inserts multiple rows in a single query.

INSERT INTO <table_name> (column_name, created_at)
VALUES
('<value_1>', NOW()),
('<value_2>', NOW());

INSERT INTO users (username, created_at)
VALUES
('john_doe', NOW()),
('jane_smith', NOW());

Efficient way to insert multiple records at once.

Update a table row

Modifies data in a specific row.

UPDATE <table_name> SET <column_name> = '<value>' WHERE id = <id_value>;

UPDATE users SET username = 'new_name' WHERE id = 1;

Ensure the WHERE clause is used to avoid updating all rows.

Indexing

Commands for creating and managing indexes.

Create an index

Creates an index on a specific column to speed up queries.

CREATE INDEX <index_name> ON <table_name> (<column_name>);

CREATE INDEX idx_users_username ON users (username);

Indexes improve query performance but may slow down inserts/updates.

Drop an index

Deletes an index.

DROP INDEX <index_name>;

DROP INDEX idx_users_username;