Extract year from date within WHERE clause

Question

I need to include EXTRACT() function within WHERE clause as follow:

SELECT * FROM my_table WHERE EXTRACT(YEAR FROM date) = '2014';

I get a message like this:

pg_catalog.date_part(unknown, text) doesn't exist** 
SQL State 42883

Here is my_table content (gid INTEGER, date DATE):

  gid  |    date
-------+-------------
  1    | 2014-12-12
  2    | 2014-12-08
  3    | 2013-17-15

I have to do it this way because the query is sent from a form on a website that includes a 'Year' field where users enter the year on a 4-digits basis.

Answer 1

The problem is that your column is of data type text, while EXTRACT() only works for date / time types.

You should convert your column to the appropriate data type.

ALTER TABLE my_table ALTER COLUMN date TYPE date;

That's smaller (4 bytes instead of 11 for the text), faster and cleaner (disallows illegal dates and most typos).
If you have non-standard format add a USING clause with a conversion expression. Example:

• Alter character field to date

Also, for your queries to be fast with a plain index on date you should rather use sargable predicates. Like:

SELECT * FROM my_table
WHERE    date >= '2014-01-01'
AND      date <  '2015-01-01';

Or, to go with your 4-digit input for the year:

SELECT * FROM my_table
WHERE    date >= to_date('2014', 'YYYY')
AND      date <  to_date('2015', 'YYYY');

You could also be more explicit:

to_date('2014' || '0101', 'YYYYMMNDD')

Both produce the same date '2014-01-01'.

Aside: date is a reserved word in standard SQL and a basic type name in Postgres. Don't use it as identifier.

Answer 2

This happens because the column has a text or varchar type, as opposed to date or timestamp. This is easily reproducible:

SELECT 1 WHERE extract(year from '2014-01-01'::text)='2014';

yields this error:

ERROR: function pg_catalog.date_part(unknown, text) does not exist
LINE 1: SELECT 1 WHERE extract(year from '2014-01-01'::text)='2014';
^ HINT: No function matches the given name and argument types. You might need to add explicit type casts.

extract or is underlying function date_part does not exist for text-like datatypes, but they're not needed anyway. Extracting the year from this date format is equivalent to getting the 4 first characters, so your query would be:

SELECT * FROM my_table WHERE left(date,4)='2014';