Re: selective copy

--- Matthew Nuzum <cobalt(at)bearfruit(dot)org> wrote:
> Hey group,
> I know you can't do a selective copy but I was
> wondering if someone can
> suggest the most effective way to get the same
> result.
>
> What I have is a lot of text data with unknown
> characters. Quotes,
> apostrophes, carriage returns and etc. There could
> be hundreds or thousands
> of rows in the table but I only want one or two. I
> need to get the data
> into an external file so it can be transported to
> another database and
> re-inserted.
>
> What I've done in the past is dump the table's data
> using pg_dump -t table
> and using INSERTS instead of copy and then grep the
> results so that I only
> get the desired line. That only works when I'm
> using simple data that
> doesn't wrap to different lines.
>
> I'm doing this to restore data from a backup :`(
>

Why not just a select?
>From psql:
\o filename -- to send the select output to a file
\a -- get rid of output space padding
\f '^V^I' -- type that to get tab separators
\t -- output tuples only
select * from tablename where (criteria for finding
the rows you want)
\c otherdatabase
\copy othertablename from filename with null as ''

If you need to handle special characters then you can
try enumerating your select list as:
select quote_literal(fieldname1),
quote_literal(fieldname2)...
for all of your text-type fields (warning: I have not
made much use of "quote_literal()" myself, so I may be
making undue assumptions about what it will do: please
test!).
If you want to emulate exactly the output of "copy",
then you could do:
select coalesce(fieldname,'\N') from ...

__________________________________________________
Do you Yahoo!?
Yahoo! Tax Center - forms, calculators, tips, more
http://taxes.yahoo.com/