How do You concatenate a value to an array that was retrieved through a query in sql? I have a table named "mkvtable" that establishes a correspondence between individual strings and arrays of strings:

  word   |     mkvword
---------+-----------------
 hello   | {world,friend}
 goodbye | {home,forever}
 hi      | {fellas,ladies}

According to the documentation provided for PostgreSQL, appending a value to an array of values should be a simple matter of using the concatenation operator: "||".   

However, if, I attempt to add the string there, to the array of strings {fellas,ladies} which correspond to the word hi, nothing appears to happen:

SELECT mkvword FROM mkvtable WHERE word = 'hi' || 'there';


PostgresSQL appears to be saying as much:

 mkvword
---------
(0 rows)


Since there are no errors thrown, I assume things are syntactically okay. 

What am I doing wrong? My best guess is that psql is getting upset that I'm trying to access the array by using it's relationship to a value from the adjacent column, but that's just an uneducated guess. Perhaps this sort of query doesn't jell well with the concatenation operator? 

TL;DR: How do you properly concatenate values to arrays stored in table rows?
However, if, for example, I attempt to add the string 'there', to the array of strings '{fellas,ladies}' which correspond to the word 'hi', nothing appears to happen:


You are comparing the value of the column word with the string constant 'hithere' - you are concatenating two string constants, not a string to an array column. 

 where word = 'hi' || 'there';


is the same as

 where word = 'hithere';




It sounds as if you really want: 

SELECT mkvword || 'there' 
FROM mkvtable 
WHERE word = 'hi';


If you want to change the value that is stored in the table rather than just displaying it, you need an update:

update mkvtable
  set mkvword = mkvword || 'there' 
where word = 'hi';