Postgresql query returns string values between quotation marks " if it contains any space character or without quotation if it had no space char

Question

so I got this strange issue and I have no idea what's causing it. We are using Charts.js in PHP to plot some charts. We store the arguments needed to create a chart into a PSQL table to load when needed.

My issue lies with the title, group and label parameters which are all string arrays. They are stored in a text[] table. The issue is, when I query a graph's parameter, if a string in the array has any space, it is returned between quotation marks if not it is returned as the string itself.

I have an associative array $graph which stores the graph's parameters and is used to insert the values into the database:

$cursor = $this->query("
        INSERT INTO data.graphs(region,title,type,section,label,group,values,colors,id_sample)                   
        VALUES(
            '".$graph['local']."',
            '".$graph['title']."',
            '".$graph['type']."',
            '".$graph['section']."',
            '".$graph['label']."',
            '".$graph['group']."',
            '".$graph['values']."',
            '".$graph['colors']."',
            '".$_SESSION['id_sample']."'
        )  
    ");

The string arrays inserted into the DB are like this:

$graph1['label'] = "{Não há material presente,Material presente, mas crianças não usaram,Crianças usaram os materiais}";
$graph2['label'] = "{Boa vista, Rural, Urbana, Indigena}";
$graph3['label'] = "{Boa vista, Ru ral, Urb ana, Ind igena}";

If I query my labels in the database they look like this:

Now to query the data we do a 'SELECT *' for the parameters of each graph needed and store they values associative array again (used as parameter for the Charts.js plotter).

$aux = pg_fetch_array($cursor))
print_r($aux['label']);

This is how my labels look like for each of my 3 graphs:

{"Não há material presente","Material presente, mas crianças não usaram","Crianças usaram os materiais"}
{"Boa vista",Urbana,Rural,Indigena}
{"Boa vista","Urb ana","Ru ral","Ind igena"}

So in the DB the strings have no quotations and if a qstring has a space it is returned between quotation marks which isn't all bad because I want my string in quotation marks.

Is there any way to force each string returned in the array to be inside all quotation marks?

I already tried inserting into the DB an array like this:

$graph['label'] = "{
        \"Boa vista\",
        \"Urbana\",
        \"Rural\",
        \"Indigena\"
    }";

If I look into the DB the strings have no quotation, they are returned just like above yet again.

score 2 · Accepted Answer · answered May 10 '20 at 02:46

That's the default text output for type text[]. The manual:

The array output routine will put double quotes around element values if they are empty strings, contain curly braces, delimiter characters, double quotes, backslashes, or white space, or match the word NULL. Double quotes and backslashes embedded in element values will be backslash-escaped.
[...] for textual data types one should be prepared to cope with either the presence or absence of quotes.

If you prefer a text representation with all elements double quoted, you could use a simple expression like:

SELECT '"' || array_to_string('{"Boa vista",Urbana,Rural,Indigena}'::text[], '", "') || '"'

If you need it a lot, consider a (simplistic) function:

CREATE OR REPLACE FUNCTION f_array_to_string_with_quotes(_arr text[], _quote text = '"')
  RETURNS text LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
$func$
SELECT $2 || array_to_string($1, $2 || ', ' || $2) || $2
$func$;

Call:

SELECT f_array_to_string_with_quotes('{"Boa vista",Urbana,Rural,Indigena}', '"');

| f_array_to_string_with_quotes              |
| :----------------------------------------- |
| "Boa vista", "Urbana", "Rural", "Indigena" |

db<>fiddle here - with more examples

Note that the resulting type is now text, not text[]! And it does not properly escape anything, hence "simplistic".

Postgresql query returns string values between quotation marks " if it contains any space character or without quotation if it had no space char

1 Answers1