Stack Overflow
Stack Overflow

Questions tagged [generate-series]

Relating to the row generator function generate_series() in PostgreSQL

The PostgreSQL function generate_series() generates a set of rows with numbers or dates between upper and lower border passed as arguments.
It is classified as SRF (set-returning function) by the Postgres developers.

241 questions
145
votes
4 answers

Generating time series between two dates in PostgreSQL

I have a query like this that nicely generates a series of dates between 2 given dates: select date '2004-03-07' + j - i as AllDate from generate_series(0, extract(doy from date '2004-03-07')::int - 1) as i, generate_series(0, extract(doy from…
f.ashouri
  • 5,409
  • 13
  • 44
  • 52
41
votes
2 answers

How to insert data in postgresql using for loop?

I would like to know how to insert data in postgresql using for loop? I want to insert 1 to 1000 in row of id..
Pablo Job
  • 485
  • 1
  • 5
  • 8
39
votes
5 answers

How to perform a select query in a DO block?

I want to port the below SQL code from MS SQL-Server to PostgreSQL. DECLARE @iStartYear integer DECLARE @iStartMonth integer DECLARE @iEndYear integer DECLARE @iEndMonth integer SET @iStartYear = 2012 SET @iStartMonth = 4 SET @iEndYear = 2016 SET…
Stefan Steiger
  • 78,642
  • 66
  • 377
  • 442
35
votes
8 answers

How can I generate a series of repeating numbers in PostgreSQL?

In PostgreSQL, is it possible to generate a series of repeating numbers? For example, I want to generate the numbers 1 to 10, with each number repeated 3 times: 1 1 1 2 2 2 3 3 3 .. and so on.
oshongo
  • 403
  • 1
  • 6
  • 8
28
votes
1 answer

Best way to count rows by arbitrary time intervals

My app has a Events table with time-stamped events. I need to report the count of events during each of the most recent N time intervals. For different reports, the interval could be "each week" or "each day" or "each hour" or "each 15-minute…
jpw
  • 18,697
  • 25
  • 111
  • 187
26
votes
4 answers

generate_series() equivalent in MySQL

I need to do a query and join with all days of the year but in my db there isn't a calendar table. After google-ing I found generate_series() in PostgreSQL. Does MySQL have anything similar? My actual table has something like: date qty 1-1-11 …
stighy
  • 7,260
  • 25
  • 97
  • 157
26
votes
10 answers

Using sql function generate_series() in redshift

I'd like to use the generate series function in redshift, but have not been successful. The redshift documentation says it's not supported. The following code does work: select * from generate_series(1,10,1) outputs: 1 2 3 ... 10 I'd like to do…
Elm
  • 1,355
  • 6
  • 22
  • 33
25
votes
3 answers

How to return rows with 0 count for missing data?

I have unevenly distributed data (wrt date) for a few years (2003-2008). I want to query data for a given set of start and end date, grouping the data by any of the supported intervals (day, week, month, quarter, year) in PostgreSQL 8.3. The problem…
JV.
  • 2,658
  • 4
  • 24
  • 36
21
votes
5 answers

Postgresql generate_series of months

I'm trying to generate a series in PostgreSQL with the generate_series function. I need a series of months starting from Jan 2008 until current month + 12 (a year out). I'm using and restricted to PostgreSQL 8.3.14 (so I don't have the timestamp…
Pyrite
  • 277
  • 1
  • 2
  • 8
18
votes
3 answers

Calculate working hours between 2 dates in PostgreSQL

I am developing an algorithm with Postgres (PL/pgSQL) and I need to calculate the number of working hours between 2 timestamps, taking into account that weekends are not working and the rest of the days are counted only from 8am to…
albergali
  • 191
  • 1
  • 1
  • 4
15
votes
7 answers

generate_series() equivalent in snowflake

I'm trying to find the snowflake equivalent of generate_series() (the PostgreSQL syntax). SELECT generate_series(timestamp '2017-11-01', CURRENT_DATE, '1 day')
Tarik
  • 167
  • 1
  • 2
  • 6
14
votes
1 answer

Join a count query on generate_series() and retrieve Null values as '0'

I want to count ID's per month using generate_series(). This query works in PostgreSQL 9.1: SELECT (to_char(serie,'yyyy-mm')) AS year, sum(amount)::int AS eintraege FROM ( SELECT COUNT(mytable.id) as amount, …
zehpunktbarron
  • 1,193
  • 3
  • 15
  • 26
13
votes
2 answers

generate_series() - how to use it to populate multiple columns in a table?

I have a table with the following structure: widgetnumber - text dd - text refnumber - text widgetnumber and refnumber need to be populated with the same value... one that's generated using generate_series. dd will be a static value that's set…
Happydevdays
  • 1,982
  • 5
  • 31
  • 57
13
votes
2 answers

Generate series of week intervals for given month

In a Postgres 9.1 database, I am trying to generate a series of weeks for a given month but with some constraints. I need all weeks to start on Monday and get cut when they start or end in another month. Example: For February, 2013 I want to…
Eddie
  • 302
  • 1
  • 2
  • 8
12
votes
3 answers

What is the expected behaviour for multiple set-returning functions in SELECT clause?

I'm trying to get a "cross join" with the result of two set-returning functions, but in some cases I don't get the "cross join", see example Behaviour 1: When set lenghts are the same, it matches item by item from each set postgres=# SELECT…
jperelli
  • 6,988
  • 5
  • 50
  • 85
1
2 3
…
16 17