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

API Trace View

How I Cut 22.3 Seconds Off an API Call with Sentry

Struggling with slow API calls? Dan Mindru walks through how he used Sentry's new Trace View feature to shave off 22.3 seconds from an API call.

Get a practical walkthrough of how to identify bottlenecks, split tasks into multiple parallel tasks, identify slow AI model calls, and more.

Read more →

Top comments (0)

A Workflow Copilot. Tailored to You.

Pieces.app image

Our desktop app, with its intelligent copilot, streamlines coding by generating snippets, extracting code from screenshots, and accelerating problem-solving.

Read the docs

👋 Kindness is contagious

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

Okay