1

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?

0

3 Answers 3

6

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';
2
  • Perfect answer and then some. As Colin pointed out, I was just concatenating two strings and passing that as a query. Sep 9, 2018 at 18:52
  • Proper solution, its working properly , thanks
    – pkachhia
    Mar 6 at 12:34
1

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.
0
1

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}

Your Answer

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

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