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

Question

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;

zealous · Accepted Answer · 2020-05-07 21:20:46Z

2

Use a from clause:

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

edited May 7, 2020 at 21:20

zealous

7,5434 gold badges20 silver badges39 bronze badges

answered May 7, 2020 at 21:06

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Khubaib Ahmad Over a year ago

Can you please explain what is the purpose of gs(a)?

Gordon Linoff Over a year ago

@KhubaibAhmad . . . That names the return value from generate_series() as gs.a.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related