PostgreSQL Select Command

PostgreSQL Select Command is used to retrieve date(rows) from one or more tables or views.

Purpose of PostgreSQL Select Command:

1. All queries in the WITH list are computed. These effectively serve as temporary tables that can be referenced in the FROM list. A WITH query that is referenced more than once in FROM is computed only once.
2. All elements in the FROM list are computed. If more than one element is specified in the FROM list, they are cross-joined together.
3. If the WHERE clause is specified, all rows that do not satisfy the condition are eliminated from the output.
4. If the GROUP BY clause is specified, the output is combined into groups of rows that match on one or more values. If the HAVING clause is present, it eliminates groups that do not satisfy the given condition.
5. The actual output rows are computed using the SELECT output expressions for each selected row or row group.
6. SELECT DISTINCT eliminates duplicate rows from the result. SELECT DISTINCT ON eliminates rows that match on all the specified expressions. SELECT ALL will return all candidate rows, including duplicates.
7. If the ORDER BY clause is specified, the returned rows are sorted in the specified order. If ORDER BY is not given, the rows are returned in whatever order the system finds fastest to produce.
8. If the LIMIT or OFFSET clause is specified, the SELECT statement only returns
a subset of the result rows.
9. If FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE or FOR KEY SHARE is specified, the SELECT statement locks the selected rows against concurrent updates.

Syntax:

Basic Syntax:

select column(s) from table_name condition;


Create table:

CREATE TABLE student (
student_name varchar(80),
student_id int,
class int,
dob date
);

Insert data into table:

insert into student values(‘Karunakar’,1001,1,’12-12-2015′);
insert into student values(‘varshini’,1002,2,’12-12-2014′);

select all rows from student table

select * from student;

PostgreSQL select command

select the rows order by student name.

select * from student order by student_name;

postgresql-select-command1

Full select syntax:

[ WITH [ RECURSIVE ] with_query [, ...] ] SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ] * | expression [ [ AS ] output_name ] [, ...] [ FROM from_item [, ...] ] [ WHERE condition ] [ GROUP BY expression [, ...] ] [ HAVING condition [, ...] ] [ WINDOW window_name AS ( window_definition ) [, ...] ] [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ] [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] [ LIMIT { count | ALL } ] [ OFFSET start [ ROW | ROWS ] ] [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ] [ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF table_name [, ...] ] [ NOWAIT where from_item can be one of: [ ONLY ] table_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ] [ LATERAL ] ( select ) [ AS ] alias [ ( column_alias [, ...] ) ] with_query_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ] [ LATERAL ] function_name ( [ argument [, ...] ] ) [ AS ] alias [ ( column_alias [, ...] [ LATERAL ] function_name ( [ argument [, ...] ] ) AS ( column_definition [, ...] ) from_item [ NATURAL ] join_type from_item [ ON join_condition | USING ( join_column [, ...]

Powered by k2schools