Thread: selective copy

selective copy

From

"Matthew Nuzum"

Date:

27 February 2003, 12:01:02

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

Re: selective copy

From

Tom Lane

Date:

27 February 2003, 13:19:54

"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

Re: selective copy

From

"Nigel J. Andrews"

Date:

27 February 2003, 13:43:53

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

Re: selective copy

From

Jeff Eckermann

Date:

27 February 2003, 13:44:46

--- 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/

Re: selective copy

From

Dennis Gearon

Date:

27 February 2003, 14:59:32

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:

>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
>