selective copy

"Matthew Nuzum" <cobalt@bearfruit.org> writes:

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.

SELECT into a temp table, then copy the temp table?

regards, tom lane

--- Matthew Nuzum <cobalt@bearfruit.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/

select c1 || '\t' || c2 || '\t' || c3 from mytable where a = 3;

On Thu, 27 Feb 2003, Matthew Nuzum 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 :`(

--
Matthew Nuzum
www.bearfruit.org
cobalt@bearfruit.org

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

--
Nigel J. Andrews
Telephone: +44 (0) 208 941 1136

Just create a table with a select from the bad table and then dump the filtered table.

2/27/2003 5:58:14 AM, "Matthew Nuzum" <cobalt@bearfruit.org> wrote: