SELECT with PostgreSQL-specific features

Introduction

The SELECT statement lets you get data from a database. PostgreSQL adds special features to make selecting data easier and more powerful.

When you want to get data with extra options like limiting rows or skipping some.

When you want to get data with special functions only in PostgreSQL.

When you want to sort data in a flexible way.

When you want to get unique rows or count rows easily.

When you want to combine results from multiple queries.

Syntax

PostgreSQL

SELECT [DISTINCT | ALL] column1, column2, ...
FROM table_name
[WHERE condition]
[ORDER BY column [ASC|DESC], ...]
[LIMIT number]
[OFFSET number]
[FETCH {FIRST | NEXT} [number] {ROW | ROWS} ONLY];

DISTINCT removes duplicate rows.

LIMIT and OFFSET help control how many rows you get and where to start.

Examples

Get unique cities from the customers table.

PostgreSQL

SELECT DISTINCT city FROM customers;

Get the 5 most recent orders.

PostgreSQL

SELECT * FROM orders ORDER BY order_date DESC LIMIT 5;

Skip first 10 expensive products and get next 5.

PostgreSQL

SELECT name, price FROM products WHERE price > 100 OFFSET 10 LIMIT 5;

Count how many employees are in the table.

PostgreSQL

SELECT COUNT(*) FROM employees;

Sample Program

This example creates a fruits table, adds some fruits, then shows how to get unique fruit names and how to get a few fruits sorted by color.

PostgreSQL

CREATE TABLE fruits (id SERIAL PRIMARY KEY, name TEXT, color TEXT);

INSERT INTO fruits (name, color) VALUES
('Apple', 'Red'),
('Banana', 'Yellow'),
('Grape', 'Purple'),
('Apple', 'Green'),
('Banana', 'Yellow');

SELECT DISTINCT name FROM fruits ORDER BY name ASC;

SELECT name, color FROM fruits ORDER BY color DESC LIMIT 3 OFFSET 1;

Important Notes

PostgreSQL supports many special functions you can use inside SELECT, like string or date functions.

Use LIMIT with OFFSET to paginate results, like pages in a book.

ORDER BY can sort by multiple columns, each ascending or descending.

Summary

SELECT gets data from tables with many options.

PostgreSQL adds features like DISTINCT, LIMIT, OFFSET, and ORDER BY for flexible queries.

Use these features to get exactly the data you want, in the order and amount you need.