Automatical quoting of array elements in Postgres

Question

I'm having some troubles with PostreSQL arrays and html forms. Let's say I have a text field in a webpage where users can write a comma-separated list of strings, like for example a list of names. This list is stored in a Postgres db.

When the page is requested,

1) My php code fills the textfield with SELECT name_list FROM names; and what I get is something like:

|  name_list         |
|____________________|
| {alice,bob,carl}   |
| ....               |
|                    |

2) Remove the first and the last char (the brackets) in order to get a printable string:

..
$arrayWithoutBrackets = substr($row['name_list'], 1,-1);
echo($arrayWithoutBrackets) ---->  alice,bob,carl
..

That string is what the users can read and, eventually, modify and send back to the server using a simple html form.

But what if the user inserts a name containing blank spaces (like alice,bob,carl,john doe)? That's what happens after updating the table:

|  name_list                    |
|_______________________________|
| {alice,bob,carl,"john doe"}   |
| ....                          |
|                               |

Postgres is auto-quoting my string and when the users will reload the page they will see

alice,bob,carl,"john doe"

NOT

alice,bob,carl,john doe

Manually searching for quote characters and removing them using str_replace() is not an option since I have many of those array-based text fields in my application (hundreds of them).

Is there any way to solve this?

What is an 'array-based textfield' supposed to be? Is the column an array or text? If it is an array how are you parsing the array contents? If it is text why do you store an array in it? — Richard Huxton
– Richard Huxton, Commented Nov 24, 2014 at 12:40
Always include your version of Postgres - and ideally the definition of the table. — Erwin Brandstetter
– Erwin Brandstetter, Commented Nov 24, 2014 at 12:40
@ Richard Huxton A 'array-based textfield' is my way to define a textfield filled with the value of an array of strings. name_list is an array of varchars and i parse it is removing the brackets. — Yata
– Yata, Commented Nov 24, 2014 at 13:04
@ Erwin Brandstetter I'm running PostgreSQL 9.3 and the definition is too long and irrelevant i think. Btw, i have many other columns in the same table. — Yata
– Yata, Commented Nov 24, 2014 at 13:09

Erwin Brandstetter · Accepted Answer · 2014-11-24 12:59:13Z

2

The double-quotes you see are just decorators for unambiguous input / output added by Postgres. Your method of removing leading and trailing curly braces is pretty hackish.

To get proper output, use array_to_string():

SELECT array_to_string(namelist, ', ') AS namelist_raw
FROM   names

SQL Fiddle.

Be aware, however, that Postgres uses the quotes for a reason. If the separator-string (', ' in the example) is included in any element of the array, the output text is ambiguous.

edited Nov 24, 2014 at 12:59

answered Nov 24, 2014 at 12:43

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Yata Over a year ago

Thanks for your answer. It's very useful. What if i have multiple columns and i need to select *?

Erwin Brandstetter Over a year ago

@user3067250: You can just combine that with any number of columns.

Collectives™ on Stack Overflow

Automatical quoting of array elements in Postgres

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related