PostgreSQL "gimmicks"

postgresql_gimmicks.sql

-- PostgreSQL Gimmicks
-- Copyright (C) 2021 Matous Jan Fialka, <https://mjf.cz/>
-- Released under the terms of "The MIT License"

-- 1. BEWARE USING BETWEEN OPERATOR WITH DATE AND TIME TYPES

-- 1.1 INCORRECT

-- Note: intervals are written in ISO 8601 format

  WITH dates (date) AS (VALUES
             (date '2017-02-01'),
             (date '2017-02-28'), 
             (date '2017-03-01'))
SELECT date
  FROM dates
 WHERE date BETWEEN date '2017-02-01' AND
                    date '2017-02-01' + interval 'P1M';

--     date    
-- ------------
--  2017-02-01
--  2017-02-28
--  2017-03-01
-- (3 rows)

-- 1.2 CORRECT

  WITH dates (date) AS (VALUES
             (date '2017-02-01'),
             (date '2017-02-28'), 
             (date '2017-03-01'))
SELECT date
  FROM dates
 WHERE date >= date '2017-02-01' AND
       date <  date '2017-02-01' + interval 'P1M';

--     date    
-- ------------
--  2017-02-01
--  2017-02-28
-- (2 rows)

-- 1. USING INDEXES

-- 1.1. INCORRECT

-- Note: this won't use an index on column value!

SELECT *
  FROM relation
 WHERE value + 1 = 10;
 
 -- 1.2. CORRECT
 
 -- Note: this will use the index.
 
 SELECT *
   FROM relation
  WHERE value = 9;