Simple but frustrating issue... I'm trying to do an update command to correct a misspelling with this field:

grouper=> select * from master_list WHERE group ~ 'JustAdopted';
       group       | activity | usefulness 
 JustAdoptedAdvice+|          |           
                   |          | 
(1 row)

But postgres won't give me a straight answer on the field name...

grouper=> UPDATE master_list SET group = 'JustAdoptingAdvice' WHERE group = 'JustAdoptedAdvice';

grouper=> UPDATE master_list SET group = 'JustAdoptingAdvice' WHERE group = 'JustAdoptedAdvice+';

grouper=> select * from master_list WHERE group ~ '+';
ERROR:  invalid regular expression: quantifier operand invalid

What is going on with that field? Why is that plus symbol there and what does it represent?

And how do I reference it to change it, if the field won't update with or without the symbol being there?

  • The operator ~ uses regular expressions to compare values. and '+' is an invalid regular expression (which is what the error message is telling you).
    – user1822
    Jan 22, 2014 at 22:39
  • ok, so how do I update the field? And what does the plus mean exactly?
    – some1
    Jan 22, 2014 at 22:40
  • What exactly do you want to achieve? Remove the + sign? Details about regular expressions can be found here: postgresql.org/docs/current/static/… and here: regular-expressions.info
    – user1822
    Jan 22, 2014 at 22:42
  • This is pretty clear in multiple places in the original post. I'm trying to update the field, to correct a misspelling.
    – some1
    Jan 22, 2014 at 22:46
  • @some1 I think you need to study regular expressions though - if you want to say "ends with a plus" you don't write ~ '+', but ~ '\+$'. In this case simpler to use an SQL LIKE pattern, e.g. LIKE '%+'. But actually the problem has nothing to do with ending with a plus, you're being thrown by how psql is displaying the value. See my answer. Jan 23, 2014 at 3:30

1 Answer 1



UPDATE master_list
SET group = 'JustAdoptingAdvice' 
WHERE group = 'JustAdoptedAdvice+';

should work perfectly fine if the field was really JustAdoptedAdvice+, but I don't think it is. psql is showing that + to mean continuation ... I suspect there's actually a newline there.

UPDATE master_list
SET group = 'JustAdoptingAdvice' 
WHERE group = E'JustAdoptedAdvice\n';

There could be whitespace after the newline too, so maybe even:

UPDATE master_list
SET group = 'JustAdoptingAdvice' 
WHERE group ~ 'JustAdoptedAdvice[[:blank:]\n]+';

which says "JustAdoptedAdvice followed by one or more of tab/space/newline".

Demo showing it's likely to be a newline:

regress=> CREATE TABLE t4(x text);
regress=> INSERT INTO t4(x) VALUES (E'JustAdoptedAdvice\n');
regress=> SELECT * FROM t4;

(1 row)

To be sure, look at the hex representation of the utf-8 encoding of the string:

regress=> SELECT encode( convert_to(x, 'utf-8'), 'hex') FROM t4;
(1 row)

You'll see that it ends with 0x0a, which is a newline.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.