console.jay()

PostgreSQL Select

3 min read

Use ORDER BY to sort the result by one or more columns. By default ORDER BY uses ascending order (ASC), you can change it to descending order by specifying DESC.

SELECT * FROM person ORDER BY date_of_birth ASC;
SELECT * FROM person ORDER BY date_of_birth DESC;
SELECT * FROM person ORDER BY gender DESC, date_of_birth ASC;

Use DISTINCT to list all distinct values in a column or a combination of columns.

SELECT DISTINCT gender FROM person;
SELECT DISTINCT gender, last_name FROM person;

Use WHERE with or without the following keywords or operators to select rows with conditions.

  • Use AND, OR to find union or intersection of conditions
  • Use comparison operators like >, <, >=, <=, =, <>
  • Use IN to quickly select the matching ones in a pool
  • Use BETWEEN AND to select values between a range
  • Use LIKE to find values with a certain pattern (Both % and _ denote placeholder)
  • Use ILIKE to get the effect of LIKE but without the case sensitivity
  • Use IS NULL to find values that are null.
SELECT * FROM person WHERE gender = 'Agender';
SELECT * FROM person WHERE gender = 'Male' AND date_of_birth > '2024-01-01';
SELECT * FROM person WHERE gender = 'Male' OR gender = 'Female';
SELECT * FROM person WHERE gender IN ('Male', 'Female', 'Agender');
SELECT * FROM person WHERE id BETWEEN 50 AND 55;
SELECT * FROM person WHERE last_name LIKE '%han';
SELECT * FROM person WHERE last_name ILIKE '____lan';
SELECT * FROM person WHERE email IS NULL;

Use LIMIT to show a certain number of rows. Then use OFFSET to skip the first N rows.

SELECT * FROM person LIMIT 5 OFFSET 10;

Use GROUP BY to group rows with the same column value into summary rows. GROUP BY is often used with aggregate functions, such as COUNT(), SUM(), AVG(), MIN(), MAX(). In addition, you can use GROUP BY HAVING to add conditions.

SELECT gender, COUNT(*) FROM person GROUP BY gender;
SELECT gender, COUNT(*) FROM person GROUP BY gender HAVING COUNT(*) > 15;
SELECT gender, AVG(age) FROM person GROUP BY gender;
SELECT gender, MIN(age) FROM person GROUP BY gender;
SELECT gender, MAX(age) FROM person GROUP BY gender;

Use AS to assign aliases to columns that appear in the result.

SELECT date_of_birth AS birthday FROM person;

Use COALESCE as a useful tool for handling NULL values.

SELECT COALESCE(email, 'Not provided') FROM person;

References

Graph View

Backlinks