not null default date in postgresql

Question

I want to create a new table that following code below;

create table dwh.dim_produk (
sk_produk serial primary key,
kode_produk varchar(25),
nama_produk varchar(50),
kode_kategori varchar(50),
nama_kategori varchar(50),
date_from date not null default current_timestamp,
date_to date not null default '12/31/9999');

but i get result such as;

SQL Error [22008]: ERROR: date/time field value out of range: "12/31/9999"
Hint: Perhaps you need a different "datestyle" setting.
Position: 253

thank in advance

That is a strange default date. Why not use NULL? And the default date format is 9999-12-31 — juergen d
– juergen d, Commented Jun 21, 2021 at 3:42
If you really think that the 9999 year is better then the present one: make_date(year => 9999, month => 12, day => 31) — Abelisto
– Abelisto, Commented Jun 21, 2021 at 3:53
The issue is due to your datestyle setting. In psql do show datestyle. You have one that has a DMY ordering so the 31 in the string is out of range. Change the value to 31/12/999 and it will work. — Adrian Klaver
– Adrian Klaver, Commented Jun 21, 2021 at 14:43

user330315 · Accepted Answer · 2021-06-21 05:11:39Z

3

If you want a date that is "in the future", use infinity

date_from date not null default current_date,
date_to date not null default 'infinity'

Or if you really want a date in the year 9999, use a proper DATE literal formatted using ANSI style:

date_from date not null default current_date,
date_to date not null default DATE '9999-12-31'

Alternatively you can represent a range using the daterange data type:

valid_during daterange not null default daterange(current_date,null)

A null for the upper range means "infinity" as well.

answered Jun 21, 2021 at 5:11

user330315

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

Collectives™ on Stack Overflow

not null default date in postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related