PostgreSQL Select : Different Ways To Retrieve Data Using Select Statement
In this post, we are going to learn about PostgreSQL Select statement. A SQL select statement retrieves information from the database. With a SELECT statement, you can use the following capabilities.
Projection: Select the columns in a table that are returned by a query. select as few or as many of the columns required.
Selection: Select the rows in a table that are returned by a query. Various criteria can be used to restrict the rows that are retrieved.
Joining: Bring together the data that is stored in different tables by specifying the link between them by Joins.
Table of Contents
PostgreSQL Select:
Prerequisites
For you to select data from a table or materialized view, the table or materialized view must be in your own schema or you must have the SELECT privilege on the table or materialized view.
For you to select rows from the base tables of a view:
- You must have the SELECT privilege on the view, and
- Whoever owns the schema containing the view must have the SELECT privilege on the base tables.
The SELECT ANY TABLE system privilege also allows you to select data from any table or any materialized view or the base table of any view.
General Guidelines
- In each SQL statement, PostgreSQL SELECT and FROM clauses are mandatory. Without both of them, SQL statements are not valid (there is no point in seeking to display data without indicating what should be retrieved and from where).
- The order of the PostgreSQL SELECT and FROM clauses cannot be changed, the PostgreSQL SELECT clause will always be listed first; you cannot write an Oracle SELECT statement that begins with a FROM clause.
- It is possible to specify a column’s name multiple times; the data of this column will simply appear again and again, according to the number of times the column was specified.
- To enhance readability – even though the PostgreSQL SQL syntax is neither case-sensitive nor sensitive to spaces or line breaks, ensure writing in an orderly manner: write the keywords in capital letters, names of columns/tables in small letters, insert a line break after each command and indents when required.
Below is the query which we use to select all the rows from a table “persons”
|
1 2 3 4 5 6 7 8 |
testdb=# select * from persons; personid | lastname | firstname | address | city ----------+----------+-----------+------------+-------- 12 | Jhonson | Mark | 3rd street | Dallas 13 | Jhonson | Nesty | 4th street | Dallas 14 | Jhonson | Neha | 4th street | CA (3 rows) |
postgreSQL Select statement with Projection:
syntax : select {column} from table;
|
1 2 3 4 5 6 7 8 9 10 |
testdb=# testdb=# select firstname from persons; firstname ----------- Mark Nesty Neha (3 rows) testdb=# |
Multi-column projection:
Example:
|
1 2 3 4 5 6 7 8 9 10 |
testdb=# select personid, firstname from persons; personid | firstname ----------+----------- 12 | Mark 13 | Nesty 14 | Neha (3 rows) testdb=# |
PostgreSQL describe table:
In postgreSQL, a table structure can be described by using
\d tablename
Example.,
|
1 2 3 4 5 6 7 8 9 10 |
testdb=# testdb=# \d persons Table "public.persons" Column | Type | Modifiers -----------+------------------------+----------- personid | integer | lastname | character varying(255) | firstname | character varying(255) | address | character varying(255) | city | character varying(255) | |
What is an alias in SQL Select statement?
Alias in SQL statement are of two types
1. table alias: is used to give an alias name for a table
2. column alias: is used to give an alias name for a column.
Example:
Consider the below table
|
1 2 3 4 5 6 7 |
testdb=# select firstname F_N from persons; f_n ------- Mark Nesty Neha (3 rows) |
Here, column firstname is aliased to F_N.
PostgreSQL select distinct :
Distinct is used to display unique records in SQL statement
The SELECT DISTINCT statement is used to return only distinct (different) values from a table.
consider the below example.,
|
1 2 3 4 5 6 7 |
testdb=# select * from persons; personid | lastname | firstname | address | city ----------+----------+-----------+------------+-------- 12 | Jhonson | Mark | 3rd street | Dallas 13 | Jhonson | Nesty | 4th street | Dallas 14 | Jhonson | Neha | 4th street | CA (3 rows) |
Here More than one person is having the same address, so in order to retrieve the unique address that the persons are living in we use DISTINCT.
|
1 2 3 4 5 6 7 8 9 |
testdb=# testdb=# select distinct address from persons; address ------------ 4th street 3rd street (2 rows) testdb=# |
Words from postgreshelp
Thank you for giving your valuable time to read the above information. I hope the content served your purpose in reaching out the blog.
Suggestions for improvement of the blog are highly appreciable. Please contact us for any information/suggestion/feedback.
If you want to be updated with all our articles
please follow us on Facebook | Twitter
Please subscribe to our newsletter.
