[SQL] Help with COPY command

[Tony Reina]

I'm attempting to use the COPY command through the libpq C functions.
In the psql command line editor, I'd have the following commands:

COPY testtable FROM stdin WITH DELIMITER ',';
1, 2, 'a', 3, 4
5, 6, 'b', 7, 8
9, 10, 'c', 11, 12
\.

My question is: 

Does this all have to be passed as one big command through libpq? For
example,

res = PQexec(conn, "COPY testtable FROM stdin WITH DELIMITER ',';\n",
  "1, 2, 'a', 3, 4\n5, 6, 'b', 7, 8\n9, 10, 'c', 11, 12\n\.");


Or, can it be broken up into separate lines? For example,

res = PQexec(conn, "COPY testtable FROM stdin WITH DELIMITER ',';");
res = PQexec(conn, "1, 2, 'a', 3, 4");
res = PQexec(conn, "5, 6, 'b', 7, 8");
res = PQexec(conn, "9, 10, 'c', 11, 12");
res = PQexec(conn, "\.");

-Tony

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [SQL] Help with COPY command

[Tony Reina]

Ok. I found the libpq syntax for COPY in the Programmer's manual. I've
got a working version, but wanted to verify something.

PQexec(conn, "COPY foo FROM STDIN");
PQputline(conn, "3\thello world\t4.5\n");
PQputline(conn,"4\tgoodbye world\t7.11\n");
...
PQputline(conn,"\\.\n");
PQendcopy(conn);

1. I'm assuming that I can put in as many PQputline statements as I
want to between the PQexec("COPY ... FROM ...") and the terminator
line. Is that correct? No limit?

2. Do any of these lines need to be followed by a PQclear(res)? What
about the first PQexec?

-Tony

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[SQL] SELECTing part of a matrix

[Tony Reina]

I've got a 3D matrix stored in a table. I'd like to pull back just 2
of the dimensions (specifying a constant value for the 3rd dimension).

e.g. 
table1
==
matrix1[5][10][20]

I've like to get matrix[1][all][all] and have it pulled back as a 2D
matrix (new_matrix1[all][all]).

Any way to do this?

-Tony

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [SQL] best method to copy data across databases

[Tony Reina]

[EMAIL PROTECTED] (ctrl) wrote in message news:<[EMAIL PROTECTED]>...
> I need to copy data that I have on a table in one Postgres database
> into another table, on a different database. The destination table is
> not identical with the source table so I need to do some mapping
> between the 2 tables.
> What would be the best (and quickest) way to transfer the data? (there
> are over 500k rows)
> 
> thanks!

If the 2 tables have different arrangements, then I'm not sure if
there is a quick way. The safest way is probably to do a pg_dump
--attribute-inserts.

>From the pg_dump manpage,

--attribute-inserts

  Dump  data as INSERT commands with explicit column names
(INSERT
  INTO table (column, ...) VALUES ...). This will make
restoration
  very  slow,  but  it is necessary if you desire to
rearrange the
  column ordering.

HTH,
-Tony

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