Denilson-Semedo/postgresql.md

## postgresql.md

      
    Raw
  

              postgresql.md
            
          
    [review] Schemas
PostgreSQL Cheatsheet

Connection

psql -d database -U username -p port 
Also you can use -W to prompt a password

psql -d database -U username -p port -W
Alter Password (Set new password)

Stop the postgresql service

systemctl stop postgresql.service
Set the new password

sudo passwd postgres
Basic Commands


\l List all databases.
\c database_name Connect to a specific database.
\dt List all tables in the current database.
\d table_name Show information about a specific table.
\du List all database users.
\q Quit psql.

Creating Databases and Tables


CREATE DATABASE database_name; Create a new database.
DROP DATABASE database_name; Delete a database.
CREATE TABLE table_name (column1 datatype, column2 datatype, ...); Create a new table.
DROP TABLE table_name; Delete a table.

Altering Tables


ALTER TABLE table_name ADD COLUMN column datatype; Add a new column to a table.
ALTER TABLE table_name DROP COLUMN column; Remove a column from a table.
ALTER TABLE table_name ALTER COLUMN column TYPE datatype; Change the data type of a column.
ALTER TABLE table_name RENAME COLUMN old_name TO new_name; Rename a column.
ALTER TABLE table_name RENAME TO new_table_name; Rename a table.

Altering Database Owner


ALTER DATABASE your_database_name OWNER TO new_owner_name; Changing the database owner

Altering Database Name


ALTER DATABASE db RENAME TO newdb; Rename a database.

Data Types


INTEGER Whole numbers.
NUMERIC(precision, scale) Fixed-point or floating-point numbers.
VARCHAR(length) Variable-length character strings.
DATE Dates (YYYY-MM-DD).
TIMESTAMP Dates and times.
BOOLEAN True or false values.
JSON JSON data.

Data Manipulation


INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...); Insert a new row into a table.
UPDATE table_name SET column = value WHERE condition; Update existing records in a table.
DELETE FROM table_name WHERE condition; Delete records from a table.

Querying Data


SELECT column1, column2, ... FROM table_name; Retrieve data from a table.
SELECT column1, column2, ... FROM table_name WHERE condition; Retrieve data based on a condition.
SELECT column1, column2, ... FROM table_name ORDER BY column ASC/DESC; Retrieve data sorted in ascending or descending order.
SELECT column1, column2, ... FROM table_name LIMIT count OFFSET offset; Retrieve a specific number of rows starting from an offset.

Filtering


WHERE Filters data based on a condition.
AND Combines multiple conditions, all of which must be true.
OR Combines multiple conditions, at least one of which must be true.
BETWEEN Matches a range of values.
IN Matches any value in a list.
LIKE Matches patterns using wildcards (% and _).
IS NULL Matches null values.
IS NOT NULL Matches non-null values.

Aggregation Functions


COUNT(column) Returns the number of rows.
SUM(column) Returns the sum of values in a column.
AVG(column) Returns the average value of a column.
MIN(column) Returns the minimum value in a column.
MAX(column) Returns the maximum value in a column.
GROUP BY column Groups rows based on a column.
HAVING condition Filters data after grouping.

Joining Tables


INNER JOIN Returns rows that have matching values in both tables.
LEFT JOIN Returns all rows from the left table and the matching rows from the right table.
RIGHT JOIN Returns all rows from the right table and the matching rows from the left table.
FULL JOIN Returns all rows when there is a match in either the left or right table.

Indexes


CREATE INDEX index_name ON table_name (column); Create an index on a table column.
DROP INDEX index_name; Delete an index.

Permissions and Privileges

In the GRANT command, the privilege(s) section refers to the specific privileges or permissions that you can grant to a user on a table. Here are some commonly used privileges in PostgreSQL:
SELECT: Allows the user to read/query data from the table.
INSERT: Allows the user to insert new rows into the table.
UPDATE: Allows the user to modify/update existing rows in the table.
DELETE: Allows the user to delete rows from the table.
REFERENCES: Allows the user to create foreign key constraints referencing the table.
TRIGGER: Allows the user to create triggers on the table.
ALL: Grants all available privileges on the table.


GRANT privilege(s) ON table_name TO user; Grant specific privileges on a table to a user.
REVOKE privilege(s) ON table_name FROM user; Revoke specific privileges on a table from a user.
GRANT privilege(s) ON ALL TABLES IN SCHEMA schema_name TO user; Grant specific privileges on all tables in a schema to a user.
REVOKE privilege(s) ON ALL TABLES IN SCHEMA schema_name FROM user; Revoke specific privileges on all tables in a schema from a user.
GRANT privilege(s) ON DATABASE database_name TO user; Grant specific privileges on a database to a user.
REVOKE privilege(s) ON DATABASE database_name FROM user; Revoke specific privileges on a database from a user.
GRANT role TO user; Grant a role to a user.
REVOKE role FROM user; Revoke a role from a user.
GRANT ALL PRIVILEGES ON DATABASE database_name TO user_name; Give a user all permissions on a database.
ALTER DATABASE database_name OWNER TO user_name; Set the owner of a database to a user.
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO user_name; Give a user all permissions on all tables in the public schema.

Creating Users


CREATE USER username WITH PASSWORD 'password'; Create a new user with a password.
CREATE USER username; Create a new user without a password (password will need to be set later).
CREATE USER username CREATEDB; Create a new user with the ability to create databases.
CREATE USER username WITH ENCRYPTED PASSWORD 'password'; Create a new user with an encrypted password.

Modifying Users


ALTER USER username WITH PASSWORD 'new_password'; Change the password for a user.
ALTER USER username WITH SUPERUSER; Grant superuser privileges to a user.
ALTER USER username WITH NOSUPERUSER; Revoke superuser privileges from a user.
ALTER USER username WITH CREATEDB; Grant the ability to create databases to a user.
ALTER USER username WITH NOCREATEDB; Revoke the ability to create databases from a user.
ALTER USER username WITH LOGIN; Allow a user to log in.
ALTER USER username WITH NOLOGIN; Prevent a user from logging in.
ALTER USER username RENAME TO new_username; Rename a user.

Deleting Users


DROP USER username; Delete a user.
DROP USER IF EXISTS username; Delete a user if it exists.

Granting and Revoking Permissions


GRANT permission_name ON table_name TO username; Grant a specific permission on a table to a user.
REVOKE permission_name ON table_name FROM username; Revoke a specific permission on a table from a user.
GRANT ALL PRIVILEGES ON table_name TO username; Grant all privileges on a table to a user.
REVOKE ALL PRIVILEGES ON table_name FROM username; Revoke all privileges on a table from a user.
GRANT role_name TO username; Grant a role to a user.
REVOKE role_name FROM username; Revoke a role from a user.

Listing Users and Permissions


\du List all database users.
\l List all databases and their owners.
\dp table_name Show the permissions of a specific table.

This cheatsheet provides an overview of the most commonly used PostgreSQL commands and syntax. For more detailed information, refer to the PostgreSQL documentation.