proposal - support tsv output format for psql

On Wed, Dec 23, 2020 at 07:18:24AM +0100, Pavel Stehule wrote:

Hi

I am playing with clipboard on Linux, and I found a way, how to redirect psql
output to clipboard via wl-copy or xclip and then to Libre Office. Now it looks
so best format is tsv

select * from pg_database \g (format=tsv) | wl-paste -t application/
x-libreoffice-tsvc

Implementation of tsv format should not be hard.

What do you think about this?

How would you handle tabs in the data?

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com

The usefulness of a cup is in its emptiness, Bruce Lee

st 23. 12. 2020 v 15:21 odesílatel Bruce Momjian <bruce@momjian.us> napsal:

On Wed, Dec 23, 2020 at 07:18:24AM +0100, Pavel Stehule wrote:

Hi

I am playing with clipboard on Linux, and I found a way, how to redirect

psql

output to clipboard via wl-copy or xclip and then to Libre Office. Now

it looks

so best format is tsv

select * from pg_database \g (format=tsv) | wl-paste -t application/
x-libreoffice-tsvc

Implementation of tsv format should not be hard.

What do you think about this?

How would you handle tabs in the data?

This is a hard question. tabs in data in tsv are handled by backslash
escaping.

Unfortunately, after some check, the LibreOffice Calc has its own format.
It is not true tsv. Looks much more like CSV with a tabelator as a
separator. And the current implementation is buggy. It is working well only
when data doesn't hold some special chars :-/. The special chars are
deleted when data is exported from LO, and when data are imported with some
special chars, then the imported line can be deformated. Looks so LO should
be fixed first.

This command is working well - comma, quotes, or double quotes are working

select * from foo \g (format=csv tuples_only=on csv_fieldsep='\t') |
wl-copy -t application/x-libreoffice-tsvc

But \n or \t does problems due bug in LO side

After this second check, I think the new format is not necessary, because
tsv in LO is not tsv, but it is cracked csv

Pavel

Bruce Momjian <bruce@momjian.us> writes:

On Wed, Dec 23, 2020 at 07:18:24AM +0100, Pavel Stehule wrote:

Implementation of tsv format should not be hard.
What do you think about this?

How would you handle tabs in the data?

The main problem with csv is the severe lack of standardization
around what to do with corner cases (delimiters in data, empty
fields, etc). Seems like "tsv" would be even worse. Is there
an actual standard anywhere?

regards, tom lane

st 23. 12. 2020 v 16:24 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:

Bruce Momjian <bruce@momjian.us> writes:

On Wed, Dec 23, 2020 at 07:18:24AM +0100, Pavel Stehule wrote:

Implementation of tsv format should not be hard.
What do you think about this?

How would you handle tabs in the data?

The main problem with csv is the severe lack of standardization
around what to do with corner cases (delimiters in data, empty
fields, etc). Seems like "tsv" would be even worse. Is there
an actual standard anywhere?

Probably not - there are just common conventions.

standard disallows tab chars inside

https://www.iana.org/assignments/media-types/text/tab-separated-values

Regards

Pavel

On Wed, Dec 23, 2020 at 04:39:48PM +0100, Pavel Stehule wrote:

st 23. 12. 2020 v�16:24 odes�latel Tom Lane <tgl@sss.pgh.pa.us> napsal:

Bruce Momjian <bruce@momjian.us> writes:

On Wed, Dec 23, 2020 at 07:18:24AM +0100, Pavel Stehule wrote:

Implementation of tsv format should not be hard.
What do you think about this?

How would you handle tabs in the data?

The main problem with csv is the severe lack of standardization
around what to do with corner cases (delimiters in data, empty
fields, etc).� Seems like "tsv" would be even worse.� Is there
an actual standard anywhere?

Probably not - there are just common conventions.

standard disallows tab chars inside

https://www.iana.org/assignments/media-types/text/tab-separated-values

Is it too awkward to throw an error if there is a tab?

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com

The usefulness of a cup is in its emptiness, Bruce Lee

On 12/23/20 10:13 AM, Pavel Stehule wrote:

After this second check, I think the new format is not necessary,
because tsv in LO is not tsv, but it is cracked csv

I agree, I don't think this is a format we need to spent much time on.

If you set the quote to an unlikely character like ^A, the escape
character to '\', and the delimiter to TAB, COPY in CSV mode will give
you something pretty close in many cases. Embedded newlines will still
give you trouble.

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com

st 23. 12. 2020 v 16:54 odesílatel Bruce Momjian <bruce@momjian.us> napsal:

On Wed, Dec 23, 2020 at 04:39:48PM +0100, Pavel Stehule wrote:

st 23. 12. 2020 v 16:24 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:

Bruce Momjian <bruce@momjian.us> writes:

On Wed, Dec 23, 2020 at 07:18:24AM +0100, Pavel Stehule wrote:

Implementation of tsv format should not be hard.
What do you think about this?

How would you handle tabs in the data?

The main problem with csv is the severe lack of standardization
around what to do with corner cases (delimiters in data, empty
fields, etc). Seems like "tsv" would be even worse. Is there
an actual standard anywhere?

Probably not - there are just common conventions.

standard disallows tab chars inside

https://www.iana.org/assignments/media-types/text/tab-separated-values

Is it too awkward to throw an error if there is a tab?

This is a question. Maybe more practical can be some form of escaping (in
conformance with COPY tsv format) or using CSV rules for special chars (it
does LO). But raising an error can be correct too. It is true, so typical
data should not contain tabs. For example LO Calc can hold tabs in cell,
can export data in cell, but the tabs in cells are not visible, and import
data with tabs are broken (and then probably, the data with tabs are not
common).

On 12/23/20 7:18 AM, Pavel Stehule wrote:> I am playing with clipboard
on Linux, and I found a way, how to redirect

psql output to clipboard via wl-copy or xclip and then to Libre Office.
Now it looks so best format is tsv

select * from pg_database \g (format=tsv) | wl-paste -t
application/x-libreoffice-tsvc

Implementation of tsv format should not be hard.

What do you think about this?

I wonder if it would not make more sense to add support for text/csv to
LibreOffice's clipboard code rather than implementing tsv in PostgreSQL.
The tsv format is even less clearly defined than the csv format which at
least has one RFC even if many homebrew their own versions of it.

Andreas

st 23. 12. 2020 v 17:38 odesílatel Andreas Karlsson <andreas@proxel.se>
napsal:

On 12/23/20 7:18 AM, Pavel Stehule wrote:> I am playing with clipboard
on Linux, and I found a way, how to redirect

psql output to clipboard via wl-copy or xclip and then to Libre Office.
Now it looks so best format is tsv

select * from pg_database \g (format=tsv) | wl-paste -t
application/x-libreoffice-tsvc

Implementation of tsv format should not be hard.

What do you think about this?

I wonder if it would not make more sense to add support for text/csv to
LibreOffice's clipboard code rather than implementing tsv in PostgreSQL.
The tsv format is even less clearly defined than the csv format which at
least has one RFC even if many homebrew their own versions of it.

yes, but it is out of my competitions. But after some research, I don't
think this support is necessary, because the tsv implemented by LO is much
more like CSV with \t as delimiter.

Regards

Pavel