I have a scenario where i need to split rows in a table and assign certain values to a column for each group. The table name is customers(contains around 300K rows) and i need to split the customers in this table into 9 groups and update each group with a specific value in the "Value" column.

Anyone have any idea on how to do this using PostgreSQL?

This is the table structure(only a part)

Create table customers
(Customer_id  integer,
Customer_email TEXT,
Value TEXT

Insert into customers Values(1,'[email protected]',now(),'')
Insert into customers Values(2,'[email protected]',now(),'')
Insert into customers Values(3,'[email protected]',now(),'')
Insert into customers Values(4,'[email protected]',now(),'')
Insert into customers Values(5,'[email protected]',now(),'')
Insert into customers Values(6,'[email protected]',now(),'')
Insert into customers Values(7,'[email protected]',now(),'')
Insert into customers Values(8,'[email protected]',now(),'')
Insert into customers Values(9,'[email protected]',now(),'')
Insert into customers Values(10,'[email protected]',now(),'')
Insert into customers Values(11,'[email protected]',now(),'')
Insert into customers Values(12,'[email protected]',now(),'')

The above is the sample table structure and values and now i want the customers in this table to be split into 4 groups(in real i have to split into more groups say like 9 or 10) and update the Value column with a specific text for the split groups.

so the first 4 rows will be assigned with a value 'dos-1-1'
second set of 4 rows with a value 'dos-2-2'
Third set of 4 rows with a value 'dos-3-3'
Fourth set of 4 rows with a value 'dos-4-4'

Below is the desired result,

ID        Email              Date         Value
1   "[email protected]"     "2019-06-14"    "dos-1-1"
2   "[email protected]"     "2019-06-14"    "dos-1-1"
3   "[email protected]"   "2019-06-14"    "dos-1-1"
4   "[email protected]"     "2019-06-14"    "dos-2-2"
5   "[email protected]"  "2019-06-14"    "dos-2-2"
6   "[email protected]"      "2019-06-14"    "dos-2-2"
7   "[email protected]"    "2019-06-14"    "dos-3-3"
8   "[email protected]"    "2019-06-14"    "dos-3-3"
9   "[email protected]"   "2019-06-14"    "dos-3-3"
10  "[email protected]"   "2019-06-14"    "dos-4-4"
11  "[email protected]"     "2019-06-14"    "dos-4-4"
12  "[email protected]"     "2019-06-14"    "dos-4-4"

Above is the situation and in real the table consists of around 300k rows and need to split it into 9 groups and update each group with a specific text.


1 Answer 1

WITH cte AS ( SELECT Customer_id, NTILE(4) OVER ()::TEXT "Value" FROM customers )
UPDATE customers
SET "Value" = 'dos-' ||  cte."Value" || '-' ||  cte."Value"
FROM cte
WHERE customers.Customer_id = cte.Customer_id;


PS. It's recommended to use some ORDER BY in OVER clause (for example by Customer_id) - without it the result is not deterministic...


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.