How to write SQL queries in PostgreSQL

Before getting started with PostgreSQL queries we should understand some basics of SQL-

1. What is SQL?

2. DataTypes in PostgreSQL

3. Common SQL queries Examples

What is SQL

SQL is short for Structured Query Language. SQL is used to perform all types of data operations in relational database management systems (RDBMS). SQL is a powerful language where we can perform a wide range of operations:

• execute queries

• fetch data

• insert, update, and delete records in a database (DML operations)

• create new objects in a database (DDL operations)

• set permissions on tables, procedures, functions, and views(DCL)

DataTypes in PostgreSQL

The following data types are supported by PostgreSQL:

1. Boolean : A boolean/bool data type is stored in the database according to the following:

   1. 1, yes, y, t, true values are converted to true

   2. 0, no, false, f values are converted to false

2. Character : It has three character data types : CHAR(n), VARCHAR(n), and TEXT.

   1. CHAR(n): is used for data(string) with a fixed-length of characters with padded spaces.

   2. VARCHAR(n) : is the variable-length character string. Similar to CHAR(n), it can store “n” length data. But unlike CHAR(n) no padding is done

   3. TEXT : is the variable-length character string. It can store data with unlimited length.

3. Numeric: It has 2 types of numbers namely, integers and floating-point numbers.

   1. Integer:

      1. Small Integer(SMALLINT) has size 2 bytes.

      2. Integer (INT) has size of 4 bytes.

      3. Serial(SERIAL) works similar to the integers except these are automatically generated in the columns by PostgreSQL

   2. Floating-point number:

      1. float(n) is used for floating-point numbers with n precision and can have a maximum of 8-bytes.

      2. float8 or real is used to represent 4-byte floating-point numbers.

      3. A real number N(d,p) meaning with d number of digits and p number of decimal points after, are part of numeric or numeric(d, p). These are generally very precise.

4. Temporal data type:This data type is used to store date-time data. PostgreSQL has 5 temporal data type:

1. DATE : stores the dates only.

2. TIME : stores the time of day values

3. TIMESTAMP : stores both date and time values.

4. TIMESTAMPTZ: stores a timezone-aware timestamp data type.

5. INTERVAL :stores periods of time.

5. Array :an array column can be used to store an array of strings or an array of integers etc

6. JSON supports 2 types of JSON types namely JSON and JSONB(Binary JSON).

1. The JSON data type is used to store plain JSON data that get parsed every time it’s called by a query.

2. JSONB data type is used to store JSON data in a binary format. It is one hand makes querying data faster whereas slows down the data insertion process as it supports indexing of table data.

7.UUID: The UUID data type allows you to store Universal Unique Identifiers

8.hstore [ stores key-value pair]

9.Special :supports some special data types that are related to network or geometric.

Common SQL queries Examples :Some examples of common and useful PostgreSQL queries:

1. CREATE TABLE query : will create a new, empty table in the database.

2. Insert query : The INSERT command is used to insert data into a table:

3. SELECT querywithout WHERE condition:

4. UPDATE query: is used to update the data or row(s) of a database table.

Here we are changing age of the student whose name is John:

5. DELETE query: is used to delete row(s) of a database table. This command can be used with/without Where condition, but if Where condition is missing, this command will delete all rows.

Here we are deleting one row whose age is 28:

6.COUNT query: Is used to count number of rows in a table

I hope this blog was insightful and that you will get inspired by these examples.