DEV Community

Cover image for Power-Up Your Skills with this Apache AGE Cheat Sheet for PostgreSQL
Rahimullah Shaheen
Rahimullah Shaheen

Posted on

Power-Up Your Skills with this Apache AGE Cheat Sheet for PostgreSQL

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>,
    ...
);
Enter fullscreen mode Exit fullscreen mode

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>)
);
Enter fullscreen mode Exit fullscreen mode

Add a new column to an existing table:

ALTER TABLE <table_name>
ADD COLUMN <column_name> <data_type>;
Enter fullscreen mode Exit fullscreen mode

Drop a column:

ALTER TABLE <table_name>
DROP COLUMN <column_name>;
Enter fullscreen mode Exit fullscreen mode

Insert a new row into a table:

INSERT INTO <table_name> (<column1>, <column2>, ...)
VALUES (<value1>, <value2>, ...);
Enter fullscreen mode Exit fullscreen mode

Delete rows from a table:

DELETE FROM <table_name>
WHERE <condition>;
Enter fullscreen mode Exit fullscreen mode

Managing indexes:
Creating an index with the specified name on a table:

CREATE [UNIQUE] INDEX index_name
ON table (column,...)
Enter fullscreen mode Exit fullscreen mode

Removing a specified index from a table:

DROP INDEX index_name;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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>;
Enter fullscreen mode Exit fullscreen mode

Use GROUP BY to group data based on a column:

SELECT <column1>, <aggregate_function>(<column2>)
FROM <table_name>
GROUP BY <column1>;
Enter fullscreen mode Exit fullscreen mode

Top comments (0)