I'm squashing a datastructure to JSON objects using the PostgreSQL Json functions, however, there are unexpected newlines in the output.
Basically, it creates JSON objects and put them in a list.
This is the SQL:
copy (
select json_agg(Y.*) from (
select
table_logical_id as "logicalId",
max(table_actual_name) as "tableName",
max(table_display_name) as "displayName",
max(table_restriction_expression) as "restrictionExpression",
json_strip_nulls(json_agg(
json_build_object('field' , column_actual_name,
'type' , case column_type when 'DATE_TIME' then 'DATE' else column_type end,
'displayName' , column_display_name,
'displayLocation' , column_display_location,
) ORDER BY column_order ASC
)) as "columns",
coalesce(
json_agg(
json_build_object(
'field' , column_actual_name,
'direction' , case column_is_asc_sort when true then 'ASC' else 'DESC' end
) order by column_sort_order desc
) filter (where column_sort_order is not null)
, '[]') as "defaultSort"
from my_table_config
group by table_logical_id
) Y
)
TO STDOUT WITH (FORMAT TEXT, ENCODING 'UTF8');
The output contains a literal \n
(so 2 characters, not the line feed control character) between each json record, like this:
..."direction" : "ASC"}]}, \n {"logicalId ...
Is this a bug in PostgreSQL? Or, how can I prevent this?
UPDATE: Exporting a BINARY as suggested in the answer does not work, as that returns something that starts with:
00000000: 5047 434f 5059 0aff 0d0a 0000 0000 0000 PGCOPY..........
00000010: 0000 0000 0100 7480 cc5b 7b22 6c6f 6769 ......t..[{"logi
00000020: 6361 6c49 6422 3a22 4155 4449 545f 3230 calId":"AUDIT_20
COPY (select ...)
when you don't need or want the COPY format? Why not do the SELECT query directly without any unescaping of the results?psql -A -t
which are shortcuts to set the unaligned format (no ascii markers and padding around fields) and the "tuples_only" mode (no header/footer)client_encoding
setting. It's determined from the client-sideLC_CTYPE
or can be set explicitly at connection time or later withSET client_encoding=...
.