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

Question

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.

Having do update parts of a column value smells like a bad design. Given the name of the column (if my french serves me well), this looks like you are misusing a text column to store something like a time or an interval. If that was a time column, simply adding 12 hours would be as simple as heure_debut + interval '12 hour' - or it might not be necessary at all given your condition like '%PM' which sounds as if you are fixing a bad user input that could not have happened if you had chosen the correct data type — user330315
– user330315, Commented Jun 27, 2018 at 14:26
In fact, data from csv file come from a format like "03:40 PM" so if we could have use TIME datatype, we would have do it. Your french serves you well :) — B3n Sp1ndl3r
– B3n Sp1ndl3r, Commented Jun 28, 2018 at 7:18

Tim Biegeleisen · Accepted Answer · 2018-06-27 13:58:34Z

1

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.

edited Jun 27, 2018 at 13:58

answered Jun 27, 2018 at 13:26

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related