Now that you have connected to your PostgreSQL database you will probably want to read so of the data. Reading of selecting data is a fairly straightforward process, but can also get very complex very quickly. Let’s start with the basic sytax.
PostgreSQL SELECT statement syntax
In its simplest form, a select statement starts with “select column_name” followed by “from table_name”. Something like this:
SELECT company_name FROM customers;
Here we have asked the database to tell the name of every company in our customers table. This may produce a large list. There are several ways to narrow down that list of customers and one way to do that uses the WHERE clause.
SELECT company_name FROM customers WHERE customer_id = 1234;
Here we only want to see the customer name where the customer ID is 1234.
A SELECT statement can have the following sections or clauses:
- Select distinct rows using DISTINCT operator
- Sort rows using ORDER BY clause
- Filter rows using WHERE clause
- Select a subset of rows with LIMIT or FETCH clause
- Group rows into groups using GROUP BY clause
- Filter groups using the HAVING clause
- Join with other tables using joins such as INNER JOIN, LEFT JOIN, FULL OUTER JOIN, CROSS JOIN clauses
- Perform set operations using UNION, INTERSECT, and EXCEPT
Now let’s say we want to see the 10 newest customers sing our customers tables.
SELECT company_name FROM customers ORDER BY create_date DESC LIMIT 10;
Again we are asking the database for the company name from the customers table, but this time we are asking the database to sort by the create date in descending order and only return the first 10 rows.