SELECT: Querying Data

PostgreSQL SELECT: Querying Data

PostgreSQL SELECT: Querying Data

The SELECT statement is the cornerstone of data retrieval in PostgreSQL. It allows you to fetch data from one or more tables in your database. In this tutorial, we'll focus exclusively on the SELECT statement itself, setting the foundation for more advanced queries in future lessons.

Basic SELECT Syntax

The simplest form of a SELECT statement retrieves data from specified columns in a table:

SELECT column1, column2, ...
FROM table_name;
  • SELECT: Specifies the columns you want to retrieve.
  • FROM: Specifies the table from which to retrieve the data.

Selecting All Columns

(Don't forget to run the seed schema if you want to test these queries in your shell)

To retrieve all columns from a table, use the asterisk (*) wildcard:

SELECT * FROM books;

This query fetches all columns and all rows from the books table.

Selecting Specific Columns

If you only need certain columns, list them explicitly:

SELECT title, author FROM books;

This retrieves the title and author columns from the books table.

Column Aliases

Column aliases temporarily rename a column in your result set, enhancing readability:

SELECT title AS book_title, author AS writer FROM books;
  • AS: Assigns an alias to a column.
  • The result will display columns labeled book_title and writer.

Performing Calculations

You can perform arithmetic operations directly in the SELECT clause:

SELECT title, price, price * 0.9 AS discounted_price FROM books;

This calculates a 10% discount on each book's price.

Concatenating Strings

Combine multiple columns or add text using the concatenation operator (||):

SELECT 'Title: ' || title || ', Author: ' || author AS book_info FROM books;

This creates a concatenated string with book titles and authors.

Using Built-in Functions

PostgreSQL offers various functions that can be used in the SELECT statement:

  • String Functions: Manipulate text data.
  • Date/Time Functions: Work with date and time values.
  • Mathematical Functions: Perform mathematical calculations.

Example:

SELECT UPPER(title) AS uppercase_title FROM books;

This converts all book titles to uppercase.

Selecting Literal Values

You can select literal values without referencing a table:

SELECT 'Hello, PostgreSQL!';

This returns a single row with the text Hello, PostgreSQL!.

Selecting Expressions

Evaluate expressions in the SELECT clause:

SELECT 7 * 3 AS result;

This returns a single row with the calculated value 21.

Ordering the Output Columns

The order of columns in the SELECT clause determines their order in the result set:

SELECT author, title FROM books;

The result will display author first, followed by title.

Selecting from Multiple Tables (Cartesian Product)

While we won't delve into joins here, you can select from multiple tables to get a Cartesian product:

SELECT * FROM books, authors;

Note: This will combine every row of books with every row of authors, which can result in a large number of rows.

Limiting Results with FETCH FIRST

Even without a WHERE clause, you can limit the number of rows returned:

SELECT * FROM books
FETCH FIRST 5 ROWS ONLY;

This retrieves the first five rows from the books table.

Summary

The SELECT statement is your primary tool for querying data in PostgreSQL. By mastering its basic usage, you lay the groundwork for constructing more complex queries.

Key points to remember:

  • Use SELECT * to retrieve all columns from a table.
  • Specify individual columns to fetch only the data you need.
  • Employ column aliases to make your result sets more readable.
  • Perform calculations and use functions within the SELECT clause.
  • Concatenate strings and include literal values in your queries.

In future lessons, we'll explore additional clauses like WHERE, JOIN, and ORDER BY to filter and manipulate your data further.

Continue practicing with the SELECT statement to become proficient in data retrieval using PostgreSQL.

Example Schema Code

-- Create the books table
CREATE TABLE books (
    title VARCHAR(255),
    author VARCHAR(255),
    price NUMERIC(10, 2),
    pages INTEGER
);
 
-- Insert sample data into the books table
INSERT INTO books (title, author, price, pages) VALUES
('The Great Gatsby', 'F. Scott Fitzgerald', 10.99, 180),
('To Kill a Mockingbird', 'Harper Lee', 7.99, 281),
('1984', 'George Orwell', 8.99, 328),
('Pride and Prejudice', 'Jane Austen', 6.99, 279),
('The Catcher in the Rye', 'J.D. Salinger', 9.99, 214),
('Moby-Dick', 'Herman Melville', 11.99, 635),
('War and Peace', 'Leo Tolstoy', 12.99, 1225);
 
-- Create the authors table
CREATE TABLE authors (
    name VARCHAR(255)
);
 
-- Insert sample data into the authors table
INSERT INTO authors (name) VALUES
('F. Scott Fitzgerald'),
('Harper Lee'),
('George Orwell'),
('Jane Austen'),
('J.D. Salinger'),
('Herman Melville'),
('Leo Tolstoy');
 
-- Create the movies table
CREATE TABLE movies (
    title VARCHAR(255)
);
 
-- Insert sample data into the movies table
INSERT INTO movies (title) VALUES
('The Shawshank Redemption'),
('The Godfather'),
('The Dark Knight'),
('Pulp Fiction'),
('The Lord of the Rings');
 
-- Optional: Verify the data insertion
SELECT * FROM books;
SELECT * FROM authors;
SELECT * FROM movies;
What is PostgreSQL?WHERE: Filtering Data