postgresql UPDATE error " ERROR: invalid input syntax for type boolean: "

Question

I'm trying to make a simple update query on postgresql. I don't really understand the error as there is no boolean value or column type. Here is the log :

cat=> UPDATE categories SET epekcategoryid='27af8b1e-c0c9-4084-8304-256b2ae0c8b2' and epekparentcategoryid='root' WHERE categoryId='281' and siteid='0' and categoryparentid='-1';
ERROR:  invalid input syntax for type boolean: "27af8b1e-c0c9-4084-8304-256b2ae0c8b2"
LINE 1: UPDATE categories SET epekcategoryid='27af8b1e-c0c9-4084-830...

The table config :

cat=> \d categories;
                Table "public.categories"
        Column        |         Type          | Modifiers 
----------------------+-----------------------+-----------
 categoryid           | character varying(32) | 
 categoryname         | text                  | 
 siteid               | integer               | 
 categoryparentid     | character varying(32) | 
 status               | integer               | default 0
 epekcategoryid       | text                  | 
 epekparentcategoryid | text                  | 
 categorylevel        | character varying(37) | 
 categoryidpath       | character varying(37) |

WHERE categoryId='281' and siteid=0 and categoryparentid='-1'; — wildplasser
– wildplasser, Commented Nov 21, 2012 at 14:59
UPDATE categories SET epekcategoryid='27af8b' and epekparentcategoryid='root' WHERE categoryId='281' and siteid=0 and categoryparentid='-1'; is not working either — themihai
– themihai, Commented Nov 21, 2012 at 15:02

Ihor Romanchenko · Accepted Answer · 2012-11-21 15:21:08Z

62

Try:

UPDATE categories 
SET epekcategoryid='27af8b1e-c0c9-4084-8304-256b2ae0c8b2',
    epekparentcategoryid='root' 
WHERE categoryId='281' 
  and siteid='0' 
  and categoryparentid='-1';

You must separate fields in SET part with "," , not with "AND"

answered Nov 21, 2012 at 15:21

Ihor Romanchenko

29k9 gold badges56 silver badges45 bronze badges

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

3 Comments

pyrocumulus Over a year ago

Common mistake indeed; using AND inside the SET part of an UPDATE query. Gives unusual errors like this.

themihai Over a year ago

mysql was the bad teacher :) though is a bit weird that we don't use "AND" after SET but we use after "WHERE"

Ihor Romanchenko Over a year ago

@mihai It isnt weird, if you think about it. AND is an operator. Like +, - or *. In WHERE clause it is true AND false like 3 + 5, but in SET it becomes weird var1 = true AND var2 = 3.

Collectives™ on Stack Overflow

postgresql UPDATE error " ERROR: invalid input syntax for type boolean: "

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related