0
317
ViewsInconsistent output format of Postgres query
I'm querying a Postgres database for 3 varchar(n) fields like follows:
Select ("Title","Author","Isbn") from public."Book" Where 1=1
The output of the query is a tuple with 3 strings. However the formatting of output is sometimes inconsistent between records. Note the output of the query:
Notice the difference in the first element between row 40 and row 41/42. The first element of row 40 is "Revolutionary Road" (wrapped in double quotes). The first element of row 41 is Neuromancer (not wrapped in quotes). The same phenomena occurs elsewhere in the query set as well.
I'm not sure why this first element is sometimes inconsistent in how it's returned by the query. It's not an issue with the individual records themselves. What else might be wrong?
1 answers
Answer question0
The result is correct. In this output format, Postgres use quotes when string has spaces or some special chars:
postgres=# select row('ahoj', 'ahoj svete', 'ahoj,svete', '"ahoj svete"');
┌───────────────────────────────────────────────────┐
│ row │
╞═══════════════════════════════════════════════════╡
│ (ahoj,"ahoj svete","ahoj,svete","""ahoj svete""") │
└───────────────────────────────────────────────────┘
(1 row)
If you don't want output format of composite value, don't use a row constructor:
postgres=# select 'ahoj', 'ahoj svete', 'ahoj,svete', '"ahoj svete"';
┌──────────┬────────────┬────────────┬──────────────┐
│ ?column? │ ?column? │ ?column? │ ?column? │
╞══════════╪════════════╪════════════╪══════════════╡
│ ahoj │ ahoj svete │ ahoj,svete │ "ahoj svete" │
└──────────┴────────────┴────────────┴──────────────┘
(1 row)