PostgreSQL 9.4 – Strange Upper() Behavior for Dateranges

postgresqlpostgresql-9.4range-types

I'm running PostgreSQL 9.4.5 and I've just got an unexpected result from this query:

select  upper(('[2005-12-01,2005-12-04]')::daterange)

It returns 2005-12-05 instead of 2005-12-04.

The following query excludes the upper bound by specifying ) instead of ]:

select  upper(('[2005-12-01,2005-12-04)')::daterange)

It returns 2005-12-04.

Isn't the upper() function supposed to return the upper bound limit of a given range? If so, by specifying [2005-12-01,2005-12-04] as the range, it should return the upper bound which is 2005-12-04. It returns 2005-12-05 instead, which is very odd to me.

Any explanation on why this happens? I only know that '[2005-12-01,2005-12-04]'::daterange is translated by PostgreSQL to '[2005-12-01,2005-12-05)'::daterange, but this doesn't explain why 2005-12-05 should be the upper bound when I have explicitly specified 2005-12-04 as the upper bound.

Best Answer

'[2005-11-01,2005-12-04]' is just another string literal for a date range to represent the canonical form '[2005-11-01,2005-12-05)'. The documentation:

The built-in range types int4range, int8range, and daterange all use a canonical form that includes the lower bound and excludes the upper bound; that is, [).

Bold emphasis mine.

Else you would have two equal values and upper() would still return a different result, which wouldn't be acceptable.

Related Solutions

Postgresql – Why is postgresql still consuming space on the other drive

I do not have all information for pointing to the source of your problem, but I think you should create your postgresql cluster in a different way. Instead of using the postgresql generic command pg_ctl, you should use the Debian/Ubuntu provided command pg_createcluster. This way you will have a working setup that survives reboot, you may specify your data directory and your log directory, you may also specify the start policy in order to activate it automatically on boot. And you may use all cluster commands: pg_lsclusters, pg_upgradecluster, and so on.

Every cluster uses three directories:

/etc/postgresql/8.4/CLUSTERNAME, for its configuration
your data directory, for all your data and WAL (default /var/lib/postgres/8.4/CLUSTERNAME)
your log directory, for your logs (default /var/log/postgresql)

Postgresql – Optimising a query to get rows where x is between pairs of values from another table

I'd suggest using a CTE in this instance. Something that looks like this:

with exons (genename, exonnumber, crom, exonstart, exonend, padding, direction) as
    (select genename, exonnumber, crom, exonstart, exonend, padding, direction
     from exons
     where enename = 'NM_001037501')
select *
from vcfentries vcf
join exons exo
using (chrom)
where vcf.pos between exo.exonstart and exo.exonend;

Best Answer

Related Solutions

Postgresql – Why is postgresql still consuming space on the other drive

Postgresql – Optimising a query to get rows where x is between pairs of values from another table

Related Question