PostgreSQL: Auto generate a sample Dataset


Use of the GENERATE_SERIES function. As a simple example, we first use a Common Table Expression to generate a dataset which holds 10 successive dates and then in the main query we extract some date information:

WITH date_series AS (
SELECT
DATE(GENERATE_SERIES(DATE ‘2012-01-01’, DATE ‘2012-01-10′,’1 day’)) AS date
)
SELECT
date
, EXTRACT(DAY FROM date) AS day
, EXTRACT(MONTH FROM date) AS month
, EXTRACT(QUARTER FROM date) AS quarter
, EXTRACT(YEAR FROM date) AS year
FROM
date_series
;

Advertisements

One thought on “PostgreSQL: Auto generate a sample Dataset

  1. Pingback: PostgreSQL: Auto generate a sample Dataset « DbRunas – Noticias y Recursos sobre Bases de Datos

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s