I have gathered some handy PostgreSQL commands and queries which will help you work with postgresql quickly and effectively.
Basic Commands:
Connect to a PostgreSQL server using the psql command:
psql -U [username];
List postgres databases:
\l
Exit the PostgreSQL shell:
\q
Connect to a specific database:
\c <database_name>
List all tables in the current database:
\dt
Managing databases:
Create a new database:
CREATE DATABASE <database_name>
Drop a database:
DROP DATABASE <database_name>;
Change the owner of a database:
ALTER DATABASE <database_name> OWNER TO <owner_name>;
Rename a database:
ALTER DATABASE <old_database_name> RENAME TO <new_database_name>;
Change the encoding of a database:
ALTER DATABASE <database_name> SET ENCODING <encoding>;
Grant privileges to a user on a database:
GRANT <privilege> ON DATABASE <database_name> TO <username>;
Managing tables:
Create a new table:
CREATE TABLE <table_name> (
<column1> <data_type1>,
<column2> <data_type2>,
...
);
Create a new table with primary key and foreign key constraints:
CREATE TABLE <table_name> (
<column1> <data_type1> PRIMARY KEY,
<column2> <data_type2>,
...
FOREIGN KEY (<column_name>) REFERENCES <referenced_table>(<referenced_column>)
);
Add a new column to an existing table:
ALTER TABLE <table_name>
ADD COLUMN <column_name> <data_type>;
Drop a column:
ALTER TABLE <table_name>
DROP COLUMN <column_name>;
Insert a new row into a table:
INSERT INTO <table_name> (<column1>, <column2>, ...)
VALUES (<value1>, <value2>, ...);
Delete rows from a table:
DELETE FROM <table_name>
WHERE <condition>;
Managing indexes:
Creating an index with the specified name on a table:
CREATE [UNIQUE] INDEX index_name
ON table (column,...)
Removing a specified index from a table:
DROP INDEX index_name;
Querying data from tables
Retrieve all columns from a table:
SELECT * FROM <table_name>;
Retrieve specific columns from a table:
SELECT <column1>, <column2>, ... FROM <table_name>;
Retrieve distinct values from a column:
SELECT DISTINCT <column_name> FROM <table_name>;
Sort data by one or more columns:
SELECT <column1>, <column2>, ...
FROM <table_name>
ORDER BY <column_name> ASC/DESC;
Retrieve data from multiple tables using INNER JOIN:
SELECT <table1.column1>, <table2.column2>, ...
FROM <table1>
INNER JOIN <table2> ON <table1.column_name> = <table2.column_name>;
Use GROUP BY to group data based on a column:
SELECT <column1>, <aggregate_function>(<column2>)
FROM <table_name>
GROUP BY <column1>;
Top comments (0)