0

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';
UPDATE 0

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

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?

|improve this question
  • The operator ~ uses regular expressions to compare values. and '+' is an invalid regular expression (which is what the error message is telling you). – a_horse_with_no_name Jan 22 '14 at 22:39
  • ok, so how do I update the field? And what does the plus mean exactly? – some1 Jan 22 '14 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 – a_horse_with_no_name Jan 22 '14 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 '14 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. – Craig Ringer Jan 23 '14 at 3:30
1

This:

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);
CREATE TABLE
regress=> INSERT INTO t4(x) VALUES (E'JustAdoptedAdvice\n');
INSERT 0 1
regress=> SELECT * FROM t4;
         x         
-------------------
 JustAdoptedAdvice+

(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;
                encode                
--------------------------------------
 4a75737441646f707465644164766963650a
(1 row)

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

|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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