[review] Schemas
psql -d database -U username -p port
Also you can use -W
to prompt a password
psql -d database -U username -p port -W
Stop the postgresql service
systemctl stop postgresql.service
Set the new password
sudo passwd postgres
\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.
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.
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.
ALTER DATABASE your_database_name OWNER TO new_owner_name;
Changing the database owner
ALTER DATABASE db RENAME TO newdb;
Rename a database.
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.
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.
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.
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.
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.
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.
CREATE INDEX index_name ON table_name (column);
Create an index on a table column.DROP INDEX index_name;
Delete an index.
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.
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.
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.
DROP USER username;
Delete a user.DROP USER IF EXISTS username;
Delete a user if it exists.
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.
\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.
Thanks bro, just don't be mad if I steal this from you