PostgreSQL limiting results by year

Question

i have a working PostgreSQL query, column "code" is common in both tables and table test.a has date column and i want to limit search results on year, date format is like ( 2010-08-25 )

SELECT *
FROM test.a
WHERE form IN ('xyz')
AND code IN (
SELECT code
FROM test.city)

any help is appreciated

Answer 1

If by "limit" you mean "filter", then I can give you an option

SELECT
  *
FROM
  test_a
WHERE
  form IN ('xyz')
  AND code IN (
    SELECT code
    FROM test_city
  )
  AND EXTRACT(YEAR FROM dateColumn) = 2010;

db-fiddle for you to run and play with it: https://www.db-fiddle.com/f/5ELU6xinJrXiQJ6u6VH5/6

Answer 2

To return rows with date_col values in the year 2010:

SELECT *
FROM   test.a
WHERE  form = 'xyz'
AND    EXISTS (
   SELECT 1
   FROM   test.city
   WHERE  code = a.code
   )
AND    date_col >= '2010-01-01'
AND    date_col <  '2011-01-01';

This way, the query can use an index on date_col (or, ideally on (form, date_col) or (form, code, date_col) for this particular query). And the filter works correctly for data type date and timestamp alike (you did not disclose data types, the "date format" is irrelevant).

If performance is of any concern, do not use an expression like EXTRACT(YEAR FROM dateColumn) = 2010. While that seems clean and simple to the human eye it kills performance in a relational DB. The left-hand expression has to be evaluated for every row of the table before the filter can be tested. What's more, simple indexes cannot be used. (Only an expression index on (EXTRACT(YEAR FROM dateColumn)) would qualify.) Not important for small tables, crucial for big tables.

EXISTS can be faster than IN, except for simple cases where the query plan ends up being the same. The opposite NOT IN can be a trap if NULL values are involved, though:

• Select rows which are not present in other table