PostgreSQL type casting issue

Question

I have imported a PostgreSQL database and I keep getting this error:

ERROR: operator does not exist: date >= integer LINE 1: ...tut.id_pf AND evidenta_info_statut.data_sch_statut>=2010-07-... ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

My query looks like this:

SELECT p_fiz.nr_certif, p_fiz.nume, p_fiz.prenume, localizari.id_jud, evidenta_info_statut.data_sch_statut,evidenta_info_statut.statut, rapoarte_anuale.data_depunere,rapoarte_anuale.angajamente, evidenta_asigurari.data_end,lista_statute.descriere, localizari.id_jud, p_fiz.nume, p_fiz.prenume, p_fiz.nr_certif,p_fiz.id_pf,p_fiz.nume,p_fiz.prenume,p_fiz.codificare from p_fiz INNER JOIN localizari USING (id_loc) INNER JOIN evidenta_contacte USING (id_contact) LEFT JOIN lista_statute USING (id_statut_existenta) LEFT JOIN evidenta_info_statut ON p_fiz.id_pf=evidenta_info_statut.id_pf AND evidenta_info_statut.data_sch_statut>=2010-07-12 LEFT JOIN rapoarte_anuale ON p_fiz.id_pf=rapoarte_anuale.id_pf AND rapoarte_anuale.an > 2010 LEFT JOIN evidenta_asigurari ON p_fiz.id_pf=evidenta_asigurari.id_pf AND evidenta_asigurari.data_start >= 2010-07-12 ORDER BY localizari.id_jud ASC, p_fiz.nume ASC, p_fiz.prenume ASC, p_fiz.nr_certif ASC;

As I understand it, it's those >= or > causing the eror.

Any ideea how to fix this?

P.S. I'm running PostgreSQL 8.4 on Fedora.

You've got enough rep to know that you're at the mercy of the people reading your question if you don't put any effort into formatting for readability. Help us, so we can help you. — OMG Ponies
– OMG Ponies, Commented Jul 12, 2011 at 16:57
Cleanup your query, there is a lot of garbage in it. Many columns are selected multiple times and the query is impossible to read and understand. — Frank Heikens
– Frank Heikens, Commented Jul 12, 2011 at 17:16

Frank Farmer · Accepted Answer · 2011-07-12 16:53:46Z

2

You need to quote your dates.

evidenta_info_statut.data_sch_statut >= '2010-07-12'
evidenta_asigurari.data_start >= '2010-07-12'

Without quotes, those are actually evaulated as integer math; 2010 - 7 - 12 = 1991

answered Jul 12, 2011 at 16:53

Frank Farmer

39.6k12 gold badges73 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Psyche Over a year ago

Hey Frank, I added the quotes but now I get an empty result set, which is not ok because the query runs fine on another machine.

Frank Farmer Over a year ago

Postgres versions older than 8.3 don't throw an error on implicit type casts. Are you running 8.2 or older on the other machine?

Clodoaldo Neto · Accepted Answer · 2011-07-12 16:54:35Z

0

Wrap the date in quotes

'2010-07-12'

answered Jul 12, 2011 at 16:54

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Collectives™ on Stack Overflow

PostgreSQL type casting issue

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related