PostgreSQL

Hi Friends,In this blog You’ll get learn basic commands about postgreSQL.

• Postgres is the open source, object relational database system.
• Postgres uses SQL as its main query language.
• PostgreSQL offers more complex data types and allows objects to inherit properties.

TABLE COMMANDS:

Select from

⇒ To display the inserted records in the table

Syntax: select * from table_name

Example: select * from person

select first_name from person

Select email from person

Order by

⇒To display the records in ascending and descending order:

Syntax: select * from table_name order by column_name;

Example: select * from person order by first_name asc;

select * from person order by id,last_name desc;

Distinct

⇒ Used to eliminate the duplicate rows and columns

Syntax: select distinct column_name from person;

Example: select distinct first_name from person;

select distinct id from person order by id desc;

WHERE clause and AND

⇒Used to display the specific content of table

Syntax: select * from table_name WHERE column_name=’ ‘ ;

Example: select * from person WHERE gender=’female’ ;

LIMIT,OFFSET,FETCH

⇒ LIMIT — To limit the number of rows

Syntax: select * from table_name limit no_of_rows;

Example: select * from person limit 5;

⇒ OFFSET — To specify the beginning point for returning rows from result

Syntax: select * from table_name offset no_of_rows;

Example: select * from person offset 6;

⇒ FETCH — Limit and fetch have the same functionality.

Fetch is SQL standard.

Syntax: select * from table_name fetch no_of_rows;

Example: select * from person fetch first three rows only;

select * from person offset 6 fetch first two rows only;

⇒ IN — IN operator in the WHERE clause to check if a value matches any value in a list of values.

Syntax: select * from table_name WHERE column_name IN (‘value1’,’value2’….);

Example: select * from person WHERE country_of_birth IN (‘china’,’france’,’India’);

⇒BETWEEN — BETWEEN operator is used to match a value against a range of values.

Syntax: select * from table_name WHERE column_name BETWEEN DATE ‘yyyy-mm-dd’;

Example: select * from person WHERE date_of_birth BETWEEN DATE ‘2000–09–10’;

⇒LIKE and ILIKE — LIKE and ILIKE are used for pattern matching in PostgreSQL.

Example: select * from person WHERE country_of_birth LIKE ‘p%’;

select * from person WHERE country_of_birth ILIKE ‘p%’;

⇒GROUP BY — To arrange identical data into groups with the help of some functions.

Example: select country_of_birth, count(*) from person group by country_of_birth order by country_of_birth;

⇒GROUP BY HAVING — After Grouping the data, you can filter the grouped record using HAVING Clause. HAVING Clause returns the grouped records which match the given condition.

Example: select country_of_birth, count(*) from person group by country_of_birth having count(*) > 40 order by country_of_birth;

Conclusion:

I hope this post helps you to learn the basic postgresql commands for your needs.

Thank You!!