How do You concatenate a value to an array that was retrieved through a query in sql?

Question

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?

Answer 1

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

Answer 2

Building on the fantastic answer from a_horse_with_no_name:

Given that I am updating an array of strings, the proper format would be as follows:

set mkvword = mkvword || '{"there"}'

Otherwise, psql gets mad and admonishes you as follows:

DETAIL:  Array value must start with "{" or dimension information.

Answer 3

Try using array_append():

update mkvtable
  set mkvword = array_append(mkvword, 'there')
where word = 'hi';

It is cleaner than || '{"there"}' and it can handle NULLs in the field well:

SELECT array_append(NULL::character varying[], 'test');

array_append
------------
{test}