1

It's a simple SELECT, but the condition is only the year of a date. Ex.:

SELECT * FROM project WHERE project_reg = '2013';

project_reg is type timestamp. The error returned is "syntax is not valid".

ERROR: la sintaxis de entrada no es válida para tipo timestamp: «2013»

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
h3g0r_
  • 219
  • 1
  • 4
  • 15
  • Hint: to get English error messages for questions in forums or a search, run `SET lc_messages = 'C';` in your session. Doesn't effect other sessions. `RESET lc_messages` to reset. – Erwin Brandstetter Jan 11 '13 at 20:05

2 Answers2

4
SELECT * 
from project 
where extract(year from project_reg) = 2013
Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260
3

If the table is small or performance is not crucial, @Clodoaldo's answer works just fine.
Else, I would suggest this more sophisticated form:

SELECT * 
FROM   project 
WHERE  project_reg >= '2013-01-01 0:0'
AND    project_reg <  '2014-01-01 0:0'

Why?

When your condition checks on an expression (like extract(year from project_reg)), only the most basic forms can be rewritten by the query optimizer to be applied to the table column as is. This one can't.
Else, Postgres has to evaluate the expression for every single row in the table before the condition can be applied. What's more, basic indexes cannot be used, which results in a whole-table scan where an index scan would be much cheaper.
This can make queries on big tables very expensive. I am talking orders of magnitude here.

To avoid this you can:

  • Create an index on an expression

    CREATE INDEX project_project_reg_year_idx
ON project (extract(year FROM project_reg)::int);

    You probably don't have that, yet.

    Note how I cast the result to integer, which is better suited for "years" than the double precision returned by extract(). You would use the same expression in your query.

    The downside: such an index is rather specialized and can only be used for matching expressions. A basic index for many purposes is preferable in most cases.

  • Rewrite your condition, so that the table column can be checked directly. If you can.
    Luckily, you can. That's what my suggested query does. This way, a plain index can be utilized:

    CREATE INDEX project_project_reg_idx ON project (project_reg);

    You probably already have that. At least you probably should.

Test your queries with EXPLAIN ANALYZE with and without index to measure the difference.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • I appreciate very much your suggestion. Like you say is very sophisticated and truly is how i should doing. I will have in consideration your suggestion. Thank again. – h3g0r_ Jan 11 '13 at 20:42