Skip to content
Advertisement

How to use PostgreSQL column value with some calculations as second column value

I am inserting a huge data set into the SQL table and I want to achieve something like this.

SELECT generate_series(1,10) AS a, mod(generate_series(11,20),3) as b,mod(generate_series(21,30),5) as c;

 a  | b | c 
----+---+---
  1 | 2 | 1
  2 | 0 | 2
  3 | 1 | 3
  4 | 2 | 4
  5 | 0 | 0
  6 | 1 | 1
  7 | 2 | 2
  8 | 0 | 3
  9 | 1 | 4
 10 | 2 | 0
(10 rows)

The problem is I don’t want to call generate_series function for b and c. I want to take the value of a and perform mod on it for b and c like below or even efficient way but I am unable to understand this how can I do it efficiently as I will be generating and saving 1 Million records.

SELECT generate_series(1,10) AS a, mod(a,3) as b,mod(a,5) as c;

Advertisement

Answer

Use a from clause:

SELECT a, mod(a, 3) as b, mod(a, 5) as c;
FROM generate_series(1,10) gs(a)
User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement