Postgres

Truncate with reset sequence.

TRUNCATE TABLE table_name RESTART IDENTITY;

In postgres while creating a table having “serial” data type the data get autopopulated with increament order of values.

Like id -> 1,2,3,4,5..,n (datatype: serial)

But to clean this kind of table we require to use restart identity with truncate to restart the sequence. Otherwise it may show a weird behaviour of not starting the sequence from 1 as well after some time it may start the sequence from 1 which than throw a duplicate sequence error.

Reset Identity.

ALTER SEQUENCE product_id_seq RESTART WITH 1453sq

How to understand aggregation in postgres?

Aggregation means joining multiple rows on the basic of the condition.

For example:

I want to join total leave, dates, and reasons for all employees?

Table Name — employees_leaves_data
Table Column id, code, leave_dates, reason

Soln -

Club leave_dates, count of leave_dates and reason on the basis of employee code. Do this for all employees.

For implementing above use below query -

select e.code, array_agg(leave_dates), count(leave_dates), array_agg(reason) from employees_leaves_data e group_by e.code;

The above query grouping the data on the basis of employee code and the aggregation functions like array_agg, count to perform the assigned task.

array_agg function clubs the data from various rows in one row, and count will yeild the total count of rows which are clubbed together.