Обсуждение: Tabbed data in tab-separated output

I have a table which has a few VARCHAR columns whose first character
is a tab.  If I run pg_dump on this table, it outputs data like this:

    43158  \t555-1212    3

where the two embedded white spaces are actually tabs.

If I use psql to execute SQL to dump parts of the table, like this:

    psql -qtA -f '\t' -U xyzzy xyzzy -c 'select ... >/tmp/xxx

I get this:

    43158        310-319-1333, x1070    3

where that initial embeded white space represents two tabs.  When I
use psql to restore this data, it thinks the 2nd column is empty and
complains that the third column is the wrong type.

pg_dump apparently is smart enough to print embedded tabs as escaped
chars, but not psql.  Is there a fix for this?  I thought of reverting
to standard output, without the -t option, and analyzing the first two
lines to tell exactly how many spaces are assigned to each column, but
that gives me the shudders.

--
            ... _._. ._ ._. . _._. ._. ___ .__ ._. . .__. ._ .. ._.
     Felix Finch: scarecrow repairman & rocket surgeon / felix@crowfix.com
  GPG = E987 4493 C860 246C 3B1E  6477 7838 76E9 182E 8151 ITAR license #4933
I've found a solution to Fermat's Last Theorem but I see I've run out of room o

How are you attempting to restore the table after using psql?  Psql
insert statements? Pgdump? COPY FROM?

Mike
On Wed, 2007-01-03 at 17:59 -0800, felix@crowfix.com wrote:
> I have a table which has a few VARCHAR columns whose first character
> is a tab.  If I run pg_dump on this table, it outputs data like this:
>
>     43158  \t555-1212    3
>
> where the two embedded white spaces are actually tabs.
>
> If I use psql to execute SQL to dump parts of the table, like this:
>
>     psql -qtA -f '\t' -U xyzzy xyzzy -c 'select ... >/tmp/xxx
>
> I get this:
>
>     43158        310-319-1333, x1070    3
>
> where that initial embeded white space represents two tabs.  When I
> use psql to restore this data, it thinks the 2nd column is empty and
> complains that the third column is the wrong type.
>
> pg_dump apparently is smart enough to print embedded tabs as escaped
> chars, but not psql.  Is there a fix for this?  I thought of reverting
> to standard output, without the -t option, and analyzing the first two
> lines to tell exactly how many spaces are assigned to each column, but
> that gives me the shudders.
>

On Wed, Jan 03, 2007 at 10:31:46PM -0600, mike wrote:
> How are you attempting to restore the table after using psql?  Psql
> insert statements? Pgdump? COPY FROM?

Actually, right now I feed the dump file into a program which mangles
it and analyzes it in various ways.  It will eventually be fed into
psql for restoration elsewhere.  The problem isn't restoring it.  It's
not knowing how to tell which tabs are field separators and which are
part of the data.

> On Wed, 2007-01-03 at 17:59 -0800, felix@crowfix.com wrote:
> > pg_dump apparently is smart enough to print embedded tabs as escaped
> > chars, but not psql.  Is there a fix for this?  I thought of reverting
> > to standard output, without the -t option, and analyzing the first two
> > lines to tell exactly how many spaces are assigned to each column, but
> > that gives me the shudders.

--
            ... _._. ._ ._. . _._. ._. ___ .__ ._. . .__. ._ .. ._.
     Felix Finch: scarecrow repairman & rocket surgeon / felix@crowfix.com
  GPG = E987 4493 C860 246C 3B1E  6477 7838 76E9 182E 8151 ITAR license #4933
I've found a solution to Fermat's Last Theorem but I see I've run out of room o

On Thu, Jan 04, 2007 at 12:16:17AM -0800, felix@crowfix.com wrote:
> On Wed, Jan 03, 2007 at 10:31:46PM -0600, mike wrote:
> > How are you attempting to restore the table after using psql?  Psql
> > insert statements? Pgdump? COPY FROM?
>
> Actually, right now I feed the dump file into a program which mangles
> it and analyzes it in various ways.  It will eventually be fed into
> psql for restoration elsewhere.  The problem isn't restoring it.  It's
> not knowing how to tell which tabs are field separators and which are
> part of the data.

psql is prety dumb that way, being designed for people not programs. I
think recent versions will escape the tab.

I suggest you use \copy instead, which on recent versions will allow you
to copy from a query. Alternativly, use a delimiter that doesn't exist
in your data.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.