Request-Response

The Full-Stack Blog

Deep

PostgreSQL Reference Guide

May 28, 2024

PostgreSQL is widely considered a the fastest growing and most developer-friendly open source relational database management system. It's known for its advanced features, reliability, and strong support for SQL (standard querying language). PostgreSQL powers a range of applications, from simple web apps to complex financial and scientific analysis software.

PostgreSQL has its own set of terminology and commands, which may seem challenging at first. You can use this reference guide to familiarize yourself with PostgreSQL commands, terminology, and syntax.

Basic Terminology

Before we get into PostgreSQL commands, let's review some basic terminology for relational databases:

SQL (Structured Query Language) is a standardized language used for managing and manipulating databases.
A database is an organized collection of data, typically stored and accessed electronically from a computer system.
A relational database structures data into tables, allowing relationships between different data types.
A table is a collection of related data entries within a database and can consist of one or more tables.
A column in a table represents a set of data values of a particular type.
A row is a single record in a table.
A primary key uniquely identifies each row in a table. A table can have only one primary key, which may consist of single or multiple columns.
A foreign key is a column or a group of columns in one table that uniquely identifies a row of another table or the same table.

Common PostgreSQL Commands

Now, let's look at some common PostgreSQL commands.

PostgreSQL Shell Commands

In the PostgreSQL Shell, you will commonly use:

psql -U [username] to access the PostgreSQL Shell. Replace [username] with your actual username (in default installations, the username is postgres). After this command, you'll be prompted to enter your password.
\c [name of database] to connect to a particular database.
\l to list all databases.
\dt to display all tables in the current database.
\d [name of table] to describe a table's structure.

Database Commands

These are some PostgreSQL commands for database operations:

CREATE DATABASE [name of database]; to create a new database.
DROP DATABASE [name of database]; to delete a database.
DROP DATABASE IF EXISTS [name of database]; to delete a database only if it exists, preventing errors if it doesn't.

Table Commands

For table operations, use:

CREATE TABLE [name of table] to create a new table.
DROP TABLE [name of table]; to delete a table.
DROP TABLE IF EXISTS [name of table]; to delete a table, only if it exists.
INSERT INTO [name of table] to add a new row to a table.
UPDATE [name of table] SET [column]; to modify records in a table.
DELETE FROM [name of table]; to remove records from a table.
ALTER TABLE [name of table]; to modify an existing table (e.g., adding a column).

Data Retrieval

To retrieve data in PostgreSQL, use:

SELECT to retrieve records from one or more tables.
FROM [name of table]; to specify the table to retrieve records from.
* to select all columns in a table. It can be combined with WHERE to select specific records.
WHERE; to specify conditions for selecting records.
LIMIT; to restrict the number of records returned.
COUNT(); to count the number of rows that match a specified condition.
GROUP BY; to group rows with the same values in specified columns, commonly used with aggregate functions like COUNT().
SELECT * FROM [name of table]; to select all records from a table.
SELECT COUNT([column]) FROM [name of table]; to count records in a table.

Resources

Category: postgresql

Tagged under: postgresql, reference, guide, sql, databases,