postgresql

A quick reference for common PostgreSQL commands and queries.

User Authentication

Commands for logging into PostgreSQL and managing sessions.

Login to PostgreSQL

Login using the `psql` cli.

psql -U username -d database_name

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';

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;

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;

This gives the user full control over the database.

Grant privileges on all tables in a schema

Grants specific privileges on all tables in a schema.

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

Replace `public` with the target schema if needed.

Grant role to another user

Assigns a role to a user.

GRANT role_name TO username;

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;

Drop a user

Deletes a user from PostgreSQL.

DROP USER username;

Database Management

Commands related to database creation, deletion and listing.

Create a database

Creates a new database in PostgreSQL.

CREATE DATABASE database_name;

Ensure the database name is unique.

Drop a database

Deletes a database from PostgreSQL.

DROP DATABASE database_name;

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;

Schemas help organize database objects.

Drop a schema

Removes a schema and all objects within it.

DROP SCHEMA schema_name 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 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 users;

This will permanently remove the table and its data.

Querying Data

Commands for querying and manipulating data in PostgreSQL.

Select all data from a table

Retrieves all rows from a specific table.

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 username, created_at FROM users;

Avoid using `SELECT *` unless necessary for performance reasons.

Insert data into a table

Inserts a new row of data into a table.

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 users (username, created_at)
VALUES
('john_doe', NOW()),
('jane_smith', NOW());

Efficient way to insert multiple records at once.

Update a row in a table

Modifies data in a specific row.

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);

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

Drop an index

Deletes an index.

DROP INDEX index_name;