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.
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.
I want to join total leave, dates, and reasons for all employees?
Table Name — employees_leaves_data
Table Column id, code, leave_dates, reason
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.