segunda-feira, 21 de março de 2016

Function to generate a random date value in PostgreSQL


In order to populate a database with artificial but semantically valid contents, sometimes we need to obtain random values.



Considering PostgreSQL DBMS, in the case of generating DATE type values, we could create a customized function, named gen_date(), which receives the lower bound for the date to be created as argument:

CREATE OR REPLACE FUNCTION gen_date(min date) RETURNS date AS $$
  SELECT CURRENT_DATE - (random() * (CURRENT_DATE - $1))::int;
$$ LANGUAGE sql STRICT VOLATILE;

The following instruction is able to check the function results:

SELECT gen_date('1980-01-01'), gen_date('2015-12-31');

  gen_date  |  gen_date  
------------+------------
 2003-03-08 | 2016-01-20
(1 record)