Re: proposal - support tsv output format for psql
st 23. 12. 2020 v 17:38 odesílatel Andreas Karlsson 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 > >
Re: proposal - support tsv output format for psql
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
Re: proposal - support tsv output format for psql
st 23. 12. 2020 v 16:54 odesílatel Bruce Momjian 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 napsal: > > > > Bruce Momjian 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 https://momjian.us > EnterpriseDB https://enterprisedb.com > > The usefulness of a cup is in its emptiness, Bruce Lee > >
Re: proposal - support tsv output format for psql
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
Re: proposal - support tsv output format for psql
On Wed, Dec 23, 2020 at 04:39:48PM +0100, Pavel Stehule wrote: > > > st 23. 12. 2020 v 16:24 odesílatel Tom Lane napsal: > > Bruce Momjian 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 https://momjian.us EnterpriseDB https://enterprisedb.com The usefulness of a cup is in its emptiness, Bruce Lee
Re: proposal - support tsv output format for psql
st 23. 12. 2020 v 16:24 odesílatel Tom Lane napsal: > Bruce Momjian 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 > regards, tom lane >
Re: proposal - support tsv output format for psql
Bruce Momjian 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
Re: proposal - support tsv output format for psql
st 23. 12. 2020 v 15:21 odesílatel Bruce Momjian 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 https://momjian.us > EnterpriseDB https://enterprisedb.com > > The usefulness of a cup is in its emptiness, Bruce Lee > >
Re: 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 https://momjian.us EnterpriseDB https://enterprisedb.com The usefulness of a cup is in its emptiness, Bruce Lee
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
