Extract year from date within WHERE clause
Solution 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:
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.
Solution 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';
![wiltomap](https://i.stack.imgur.com/3q208.jpg?s=256&g=1)
wiltomap
I work in the field of Networks & Systems with a previous professional practise of geomatic (GIS). I'm currently part of a public organization dealing with water supply and sewerage systems. I'm also a passionate birdwatcher and nature enthousiast, as an alternative to computer work...
Updated on July 09, 2022Comments
-
wiltomap almost 2 years
I need to include
EXTRACT()
function withinWHERE
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.