Re: Emitting JSON to file using COPY TO

On 12/4/23 21:54, Joe Conway wrote:
> On 12/4/23 17:55, Davin Shearer wrote:
>> There are however a few characters that need to be escaped
>
>> 1. |"|(double quote)
>> 2. |\|(backslash)
>> 3. |/|(forward slash)
>> 4. |\b|(backspace)
>> 5. |\f|(form feed)
>> 6. |\n|(new line)
>> 7. |\r|(carriage return)
>> 8. |\t|(horizontal tab)
>>
>> These characters should be represented in the test cases to see how the
>> escaping behaves and to ensure that the escaping is done properly per
>> JSON requirements.
>
> I can look at adding these as test cases.
So I did a quick check:
8<--------------------------
with t(f1) as
(
values
(E'aaa\"bbb'::text),
(E'aaa\\bbb'::text),
(E'aaa\/bbb'::text),
(E'aaa\bbbb'::text),
(E'aaa\fbbb'::text),
(E'aaa\nbbb'::text),
(E'aaa\rbbb'::text),
(E'aaa\tbbb'::text)
)
select
length(t.f1),
t.f1,
row_to_json(t)
from t;
length | f1 | row_to_json
--------+-------------+-------------------
7 | aaa"bbb | {"f1":"aaa\"bbb"}
7 | aaa\bbb | {"f1":"aaa\\bbb"}
7 | aaa/bbb | {"f1":"aaa/bbb"}
7 | aaa\x08bbb | {"f1":"aaa\bbbb"}
7 | aaa\x0Cbbb | {"f1":"aaa\fbbb"}
7 | aaa +| {"f1":"aaa\nbbb"}
| bbb |
7 | aaa\rbbb | {"f1":"aaa\rbbb"}
7 | aaa bbb | {"f1":"aaa\tbbb"}
(8 rows)

8<--------------------------

This is all independent of my patch for COPY TO. If I am reading that
correctly, everything matches Davin's table *except* the forward slash
("/"). I defer to the experts on the thread to debate that...

--
Joe Conway
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com