Thread: proposal - support tsv output format for psql
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?
Regards
Pavel
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> https://momjian.us
EnterpriseDB https://enterprisedb.com
The usefulness of a cup is in its emptiness, Bruce Lee
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
Regards
Pavel
regards, tom lane
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).
--
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 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
Andreas