No, the interval type supports reduced precision but none of the other date/time types do.
Postgres allows you to roll your own with create type
but unfortunately wont allow contraints to be added to the type which limits it's usefulness in this scenario. The best I can come up with requires you to repeat check constraints on every field where the fuzzy
type is used:
create type preciseness as enum('day', 'month', 'year');
create type fuzzytimestamptz as (ts timestamptz, p preciseness);
create table t( id serial primary key,
fuzzy fuzzytimestamptz
check( (fuzzy).ts is not null
or ((fuzzy).ts is null and (fuzzy).p is not null) ),
check((fuzzy).ts=date_trunc('year', (fuzzy).ts) or (fuzzy).p<'year'),
check((fuzzy).ts=date_trunc('month', (fuzzy).ts) or (fuzzy).p<'month'),
check((fuzzy).ts=date_trunc('day', (fuzzy).ts) or (fuzzy).p<'day') );
insert into t(fuzzy) values (row(date_trunc('year', current_timestamp), 'year'));
insert into t(fuzzy) values (row(date_trunc('month', current_timestamp), 'month'));
insert into t(fuzzy) values (row(date_trunc('day', current_timestamp), 'day'));
select * from t;
id | fuzzy
----+----------------------------------
1 | ("2011-01-01 00:00:00+00",year)
2 | ("2011-09-01 00:00:00+01",month)
3 | ("2011-09-23 00:00:00+01",day)
--edit - an example equality operator:
create function fuzzytimestamptz_equality(fuzzytimestamptz, fuzzytimestamptz)
returns boolean language plpgsql immutable as $$
begin
return ($1.ts, $1.ts+coalesce('1 '||$1.p, '0')::interval)
overlaps ($2.ts, $2.ts+coalesce('1 '||$2.p, '0')::interval);
end;$$;
--
create operator = ( procedure=fuzzytimestamptz_equality,
leftarg=fuzzytimestamptz,
rightarg=fuzzytimestamptz );
sample query:
select *, fuzzy=row(statement_timestamp(), null)::fuzzytimestamptz as equals_now,
fuzzy=row(statement_timestamp()+'1 day'::interval, null)::fuzzytimestamptz as equals_tomorrow,
fuzzy=row(date_trunc('month', statement_timestamp()), 'month')::fuzzytimestamptz as equals_fuzzymonth,
fuzzy=row(date_trunc('month', statement_timestamp()+'1 month'::interval), 'month')::fuzzytimestamptz as equals_fuzzynextmonth
from t;
id | fuzzy | equals_now | equals_tomorrow | equals_fuzzymonth | equals_fuzzynextmonth
----+------------------------------------+------------+-----------------+-------------------+-----------------------
1 | ("2011-01-01 00:00:00+00",year) | t | t | t | t
2 | ("2011-09-01 00:00:00+01",month) | t | t | t | f
3 | ("2011-09-24 00:00:00+01",day) | t | f | t | f
4 | ("2011-09-24 11:45:23.810589+01",) | f | f | t | f
JSONB may be easy to read, but it's complicated and inefficient to write into.
See for example this question: PostgreSQL update and delete property from JSONB column, on how it looks like. It's an order of magnitude harder than an update/delete with classic EAV tables.
Possibly when you'll have written the parts to append/merge/delete key/value pairs, the elegance or simplicity of that JSON-based design are going to seem much less obvious. As for the performance, I'm betting on it being much worse.
At the storage level, any write into one property in a JSON structure will require a rewrite of the entire column (and in fact of the containing row), with the same cost as if all properties changed.
This is not optimal in terms of I/O size and pressure on vacuum.
This problem is mentioned on S.O in another question with some more links and references:
How to perform update operations on columns of type JSONB in Postgres 9.4
There's ongoing work to ease JSONB updates for the programmer. jsonbx provides functions and operators that can help with 9.4; presumably these will be integrated into PostgreSQL core in future versions. But the large I/O cost of a small update inside a large JSON object will remain.
jsonbx
was demonstrated in a recent PG conference (youtube links):
Update and Delete operations for jsonb (part 1 of 2)
Update and Delete operations for jsonb (part 2 of 2)
Best Answer
You need to combine two jsonb_array_elements() calls:
Online example