CODEWITHSUNDEEP
postgresql-9.5
B3n Sp1ndl3r
B3n Sp1ndl3r

Reputation: 35

Operator does not exist: text + integer [Postgres 9.5]

i have an issue with this piece of code coming from SQL :

UPDATE resultats_du_jour
SET Heure_debut =  CONCAT(SUBSTRING(Heure_debut,1,2) +
    12,SUBSTRING(Heure_debut,3,3))
WHERE Heure_debut LIKE '%PM';

This gives me the following output : 

sql:53: ER ROR: operator does not exist: text + integer LINE 1: ...ET Heure_debut = CONCAT(SUBSTRING(Heure_debut,1,2)+12,SUBSTR...

I understand that you cannot add text+integer, but how can i proceed to do that?

Many thanks.

Upvotes: 0

Views: 1328

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522017

If you want to add 12 to the numerical equivalent of the first substring you take, and then concatenate it again back to text, then you may use casts:

UPDATE resultats_du_jour
SET Heure_debut =  CONCAT((SUBSTRING(Heure_debut, 1, 2)::int + 12)::text,
    SUBSTRING(Heure_debut, 3, 3))
WHERE Heure_debut LIKE '%PM';

This feels a bit hackish, and in general as a matter of good design you should decide whether a certain type of data is text or a number. Here, if you had things stored as numbers, you might not need to cast at all.

Upvotes: 1

Related Questions