DEV Community

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

Posted on

3

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

Heroku

Build apps, not infrastructure.

Dealing with servers, hardware, and infrastructure can take up your valuable time. Discover the benefits of Heroku, the PaaS of choice for developers since 2007.

Visit Site

Top comments (0)

👋 Kindness is contagious

Please leave a ❤️ or a friendly comment on this post if you found it helpful!

Okay