PostgreSQL - SELECT
PostgreSQL - The SELECT Query
Section titled “PostgreSQL - The SELECT Query”The PostgreSQL SELECT statement is the cornerstone of data retrieval. It’s used to fetch data from one or more database tables. The result of a SELECT query is returned as a temporary table, known as a result-set.
Core Syntax
Section titled “Core Syntax”The fundamental syntax of a SELECT statement is as follows:
SELECT column1, column2, ...FROM table_name;Where:
column1, column2, ...are the names of the columns in the table from which you want to retrieve data.- To fetch all available columns without listing them individually, you can use the asterisk
*wildcard:
SELECT *FROM table_name;Best Practice: Avoid SELECT * in Application Code
Section titled “Best Practice: Avoid SELECT * in Application Code”While SELECT * is convenient for interactive exploration (e.g., using psql), it’s considered a bad practice in application code for several reasons:
- Performance: You may fetch more data than your application needs, increasing network traffic and memory usage.
- Stability: If the table schema changes (e.g., a column is added or removed), your code might break if it expects a specific number or order of columns.
- Clarity: Explicitly listing columns makes your queries self-documenting and easier for other developers to understand.
Example: Querying an employees Table
Section titled “Example: Querying an employees Table”Let’s assume we have an employees table with the following data:
| id | name | age | department | salary |
|---|---|---|---|---|
| 1 | Alice | 32 | Engineering | 90000 |
| 2 | Bob | 25 | Marketing | 65000 |
| 3 | Charlie | 28 | Engineering | 85000 |
| 4 | Diana | 35 | HR | 75000 |
| 5 | Eve | 29 | Marketing | 70000 |
To fetch only the id, name, and salary for all employees, we execute the following query:
dev_db=> SELECT id, name, salary FROM employees;This query produces the following result-set:
| id | name | salary |
|---|---|---|
| 1 | Alice | 90000 |
| 2 | Bob | 65000 |
| 3 | Charlie | 85000 |
| 4 | Diana | 75000 |
| 5 | Eve | 70000 |
If you wanted to fetch all columns for every employee, you would use:
dev_db=> SELECT * FROM employees;This would return all rows and all columns, identical to the table shown above.
Next Steps
Section titled “Next Steps”The SELECT statement is incredibly powerful. In upcoming sections, you will learn how to filter, sort, and group data using clauses like WHERE, ORDER BY, and GROUP BY.