Обсуждение: date ranges
Hello, I would like to know if there's a simple way to customize the range for dates, to avoid people insert dates before 1900 and after 2020, for example. Thank you for your time, jo
On 31/07/2007 17:26, jo wrote: > I would like to know if there's a simple way to customize the range for > dates, > to avoid people insert dates before 1900 and after 2020, for example. How about a check constraint on the date column? Something like - create table the_table ( the_date date, ....etc... check (the_date >= '1900-01-01' and the_date <= '2020-12-31') ); Then you just need to handle in your application the error raised when someone enters an incorrect date. HTH, Ray. --------------------------------------------------------------- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland rod@iol.ie ---------------------------------------------------------------
jo wrote: > Hello, > > I would like to know if there's a simple way to customize the range for > dates, > to avoid people insert dates before 1900 and after 2020, for example. > test=# CREATE TABLE foo ( id SERIAL PRIMARY KEY, dt DATE, CHECK ( EXTRACT('year' FROM dt) >= 1900 AND EXTRACT('year' FROM dt) < 2020 ) ); test=# INSERT INTO foo (dt) VALUES ('1984-03-02'); INSERT 0 1 test=# INSERT INTO foo (dt) VALUES ('1884-03-02'); ERROR: new row for relation "foo" violates check constraint "foo_dt_check" test=# INSERT INTO foo (dt) VALUES ('2024-03-02'); ERROR: new row for relation "foo" violates check constraint "foo_dt_check" There's probably a more elegant way to do this. brian
On Jul 31, 2007, at 11:26 , jo wrote: > I would like to know if there's a simple way to customize the range > for dates, > to avoid people insert dates before 1900 and after 2020, for example. You can use a check constraint on the date column, e.g., CREATE TABLE dates ( a_date date PRIMARY KEY CHECK(a_date BETWEEN '1900-01-01'::date AND '2020-12-31') ); Michael Glaesemann grzm seespotcode net