Re: [GENERAL] COPY ... FROM stdin WITH FORMAT csv
Hi David, On Tue, Mar 21, 2017 at 8:21 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > > On Tuesday, March 21, 2017, Alexander Farber wrote: >> >> words=> COPY words_reviews (uid, author, nice, review, updated) FROM stdin FORMAT csv; > > > What did you read that lead you to think the above shoud work? > ok thanks, I had misunderstood your text - "The above means the entire "WITH" section is optional, as is the word WITH. However, if you want to add "with" options they must appear within parentheses, those are not optional. Multiple options can appear within the single set of parentheses." I think the root cause is that it is difficult to understand an english sentence trying to describe a keyword "with" :-) Regards Alex
Re: [GENERAL] COPY ... FROM stdin WITH FORMAT csv
On Tue, Mar 21, 2017 at 1:45 PM, Adrian Klaver wrote: > On 03/21/2017 12:11 PM, Alexander Farber wrote: > >> Thank you - this has worked: >> >> COPY words_reviews (uid, author, nice, review, updated) FROM stdin WITH >> (FORMAT csv); >> 1,2,1,'1 is nice by 2','2017-03-01' >> 1,3,1,'1 is nice by 3','2017-03-02' >> 1,4,1,'1 is nice by 4','2017-03-03' >> 2,1,1,'2 is nice by 1','2017-03-01' >> 2,3,1,'2 is nice by 3','2017-03-02' >> 2,4,0,'2 is not nice by 4','2017-03-03' >> \. >> >> but I am confused about the comments that I should use \copy and not >> just COPY and also that I could leave out WITH and brackets. >> > > The difference between COPY and \copy is explained here: > > https://www.postgresql.org/docs/9.6/static/app-psql.html > > \copy > > Basically COPY runs as the server user and so the files it uses have to be > accessible by the user the server runs as. \copy is a psql meta command > that runs as local user so it can access files the local user can see and > has privileges on. I take it, then, if one chooses not to use pre-existing files and instead inline the content as shown here, the choice between \copy and COPY becomes a matter of style and not correctness. As a matter of style using \copy makes it clear that everything that is needed to make the command work exists locally. The advantage to using "COPY FROM|TO stdin|stdout" is that the copy command itself could (not tested) be spread out over multiple lines - which especially for COPY TO can be advantageous. The entire \copy meta-command cannot be split up. David J.
Re: [GENERAL] COPY ... FROM stdin WITH FORMAT csv
On 03/21/2017 12:11 PM, Alexander Farber wrote: Thank you - this has worked: COPY words_reviews (uid, author, nice, review, updated) FROM stdin WITH (FORMAT csv); 1,2,1,'1 is nice by 2','2017-03-01' 1,3,1,'1 is nice by 3','2017-03-02' 1,4,1,'1 is nice by 4','2017-03-03' 2,1,1,'2 is nice by 1','2017-03-01' 2,3,1,'2 is nice by 3','2017-03-02' 2,4,0,'2 is not nice by 4','2017-03-03' \. but I am confused about the comments that I should use \copy and not just COPY and also that I could leave out WITH and brackets. The difference between COPY and \copy is explained here: https://www.postgresql.org/docs/9.6/static/app-psql.html \copy Basically COPY runs as the server user and so the files it uses have to be accessible by the user the server runs as. \copy is a psql meta command that runs as local user so it can access files the local user can see and has privileges on. Because please take a look at the 9.6.2 psql output (COPY works, and leaving out WITH brackets - not): words=> COPY words_reviews (uid, author, nice, review, updated) FROM stdin WITH (FORMAT csv); Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself. 1,2,1,'1 is nice by 2','2017-03-01' 1,3,1,'1 is nice by 3','2017-03-02' 1,4,1,'1 is nice by 4','2017-03-03' 2,1,1,'2 is nice by 1','2017-03-01' 2,3,1,'2 is nice by 3','2017-03-02' 2,4,0,'2 is not nice by 4','2017-03-03' \. >> >> >> >> >> COPY 6 words=> COPY words_reviews (uid, author, nice, review, updated) FROM stdin FORMAT csv; ERROR: syntax error at or near "FORMAT" LINE 1: ...s (uid, author, nice, review, updated) FROM stdin FORMAT csv... ^ -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY ... FROM stdin WITH FORMAT csv
On Tue, Mar 21, 2017 at 12:45 PM, Paul Jungwirth < p...@illuminatedcomputing.com> wrote: > On 03/21/2017 12:21 PM, David G. Johnston wrote: > >> > words=> COPY words_reviews (uid, author, nice, review, updated) FROM >> > stdin FORMAT csv; >> >> What did you read that lead you to think the above shoud work? >> > > I don't know about COPY FROM, but COPY TO works without parens (or > FORMAT), like this: > > db=> copy (select 1+1, 2+2) to stdout with csv; > 2,4 > > (tested on pg 9.5) > > I never use parens, so I was surprised to see in the docs and the replies > here that they are necessary. Am I just exploiting a bug in the parser? That's documented backward compatibility syntax: https://www.postgresql.org/docs/9.6/static/sql-copy.html bottom of the page. David J.
Re: [GENERAL] COPY ... FROM stdin WITH FORMAT csv
On 03/21/2017 12:21 PM, David G. Johnston wrote: > words=> COPY words_reviews (uid, author, nice, review, updated) FROM > stdin FORMAT csv; What did you read that lead you to think the above shoud work? I don't know about COPY FROM, but COPY TO works without parens (or FORMAT), like this: db=> copy (select 1+1, 2+2) to stdout with csv; 2,4 (tested on pg 9.5) I never use parens, so I was surprised to see in the docs and the replies here that they are necessary. Am I just exploiting a bug in the parser? Paul -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY ... FROM stdin WITH FORMAT csv
On Tuesday, March 21, 2017, Alexander Farber wrote: > > words=> COPY words_reviews (uid, author, nice, review, updated) FROM stdin > FORMAT csv; > What did you read that lead you to think the above shoud work? David J.
Re: [GENERAL] COPY ... FROM stdin WITH FORMAT csv
Thank you - this has worked: COPY words_reviews (uid, author, nice, review, updated) FROM stdin WITH (FORMAT csv); 1,2,1,'1 is nice by 2','2017-03-01' 1,3,1,'1 is nice by 3','2017-03-02' 1,4,1,'1 is nice by 4','2017-03-03' 2,1,1,'2 is nice by 1','2017-03-01' 2,3,1,'2 is nice by 3','2017-03-02' 2,4,0,'2 is not nice by 4','2017-03-03' \. but I am confused about the comments that I should use \copy and not just COPY and also that I could leave out WITH and brackets. Because please take a look at the 9.6.2 psql output (COPY works, and leaving out WITH brackets - not): words=> COPY words_reviews (uid, author, nice, review, updated) FROM stdin WITH (FORMAT csv); Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself. >> 1,2,1,'1 is nice by 2','2017-03-01' 1,3,1,'1 is nice by 3','2017-03-02' 1,4,1,'1 is nice by 4','2017-03-03' 2,1,1,'2 is nice by 1','2017-03-01' 2,3,1,'2 is nice by 3','2017-03-02' 2,4,0,'2 is not nice by 4','2017-03-03' \. >> >> >> >> >> >> COPY 6 words=> COPY words_reviews (uid, author, nice, review, updated) FROM stdin FORMAT csv; ERROR: syntax error at or near "FORMAT" LINE 1: ...s (uid, author, nice, review, updated) FROM stdin FORMAT csv... ^
Re: [GENERAL] COPY ... FROM stdin WITH FORMAT csv
Alexander: On Tue, Mar 21, 2017 at 6:31 PM, Alexander Farber wrote: > I keep rereading https://www.postgresql.org/docs/9.6/static/sql-copy.html > but just can't figure the proper syntax to put some records into the table: It's not that complex, let's see > words=> COPY words_reviews (uid, author, nice, review, updated) FROM stdin > WITH FORMAT 'csv'; > ERROR: syntax error at or near "FORMAT" > LINE 1: ...d, author, nice, review, updated) FROM stdin WITH FORMAT 'cs... > ^ Here you have an error ( more on this later ) so the next lines are going to be interpreted as a new command > words=> 1,2,1,'1 is nice by 2','2017-03-01', Which you can clearly see because the prompt is => , initial, not ->, continuation. > words-> 1,3,1,'1 is nice by 3','2017-03-02', > words-> 1,4,1,'1 is nice by 4','2017-03-03', > words-> 2,1,1,'2 is nice by 1','2017-03-01', > words-> 2,3,1,'2 is nice by 3','2017-03-02', Also, you are putting an extra , at the end of the lines. This means you have an empty string at the end, one extra fields. I do not remember now if it hurts, but better omit it. > words-> 2,4,0,'2 is not nice by 4','2017-03-03' > words-> \. > Invalid command \.. Try \? for help. All the lines up to here are considered part of the previous sql ( remember => vs -> ) command. You are not in copy mode, so psql tries to interpret '\.' as a meta command ( like \d ) but fails. > words-> ; > ERROR: syntax error at or near "1" > LINE 1: 1,2,1,'1 is nice by 2','2017-03-01', > ^ And here you terminate the SQL command, so it fails ( note it referes to the first error, the initial line with => ). > I am not sure if FORMAT csv or FORMAT 'csv' should be used. That is easy, try both. BUT! if you read the manual with care you will notive it is "with ( options )", not "with options", so you are missing parenthesis: web_tarotpagos_staging=# create temporary table t(a varchar, b varchar); CREATE TABLE -- NO parens, no luck: web_tarotpagos_staging=# copy t(a,b) from stdin with format csv; ERROR: syntax error at or near "format" LINE 1: copy t(a,b) from stdin with format csv; ^ web_tarotpagos_staging=# copy t(a,b) from stdin with format 'csv'; ERROR: syntax error at or near "format" LINE 1: copy t(a,b) from stdin with format 'csv'; BUT, as soon as I put them: ^ web_tarotpagos_staging=# copy t(a,b) from stdin with (format csv); Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself. >> \. Note the enter data approach. Also note copy is safe to try as you can just abort it. > And I have tried adding/removing commas at the end of lines too. That is commented above. > I have looked at pg_dump output, but it does not use csv. pg_dump uses the default text format, a little more difficult but vastly superior ( IMNSHO ) to CSV. It ( by default ) separates records with newlines and fields with tab, and escapes newlines, tabs and backslashes in data with backslash, so the transformation is contextless, much easier than csv: Copy out: Replace NULL with '\N', newline with '\n', tab with '\t', backslash with '\\', join fields with tab, print with newline at end. Copy In: Read till newline, split on tabs, replace '\n' with newline, '\t' with tab, '\\' with backslash. Much easier to get right than CSV ( how do you encode the C string ",; \n\"\n\t; \t\"\'" ? ) Francisco Olarte. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY ... FROM stdin WITH FORMAT csv
On 3/21/2017 10:31 AM, Alexander Farber wrote: words=> COPY words_reviews (uid, author, nice, review, updated) FROM stdin WITH FORMAT 'csv'; ERROR: syntax error at or near "FORMAT" LINE 1: ...d, author, nice, review, updated) FROM stdin WITH FORMAT 'cs... its just csv, not 'csv' ... And I have tried adding/removing commas at the end of lines too. if its getting a syntax error on the COPY command, its not yet read the data... note that COPY FROM STDIN doesn't actually read from stdin, it requires the data to be passed through to it with a special API (PQputCopyData() in libpq, or similar in other APIs). you can use \copy in psql to stream the data from the same input. -- john r pierce, recycling bits in santa cruz -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY ... FROM stdin WITH FORMAT csv
On Tue, Mar 21, 2017 at 10:31 AM, Alexander Farber < alexander.far...@gmail.com> wrote: > Good evening, > > I keep rereading https://www.postgresql.org/docs/9.6/static/sql-copy.html > but just can't figure the proper syntax to put some records into the table: > [...] > > words=> COPY words_reviews (uid, author, nice, review, updated) FROM stdin > WITH FORMAT 'csv'; > ERROR: syntax error at or near "FORMAT" > LINE 1: ...d, author, nice, review, updated) FROM stdin WITH FORMAT 'cs... > > [ [ WITH ] ( option [, ...] ) ] The above means the entire "WITH" section is optional, as is the word WITH. However, if you want to add "with" options they must appear within parentheses, those are not optional. Multiple options can appear within the single set of parentheses. """ FORMAT Selects the data format to be read or written: text, csv (Comma Separated Values), or binary. The default is text. """ Valid values for format are as listed, no single quote required (not sure about if they are allowed) Therefore: WITH (FORMAT csv) David J.
[GENERAL] COPY ... FROM stdin WITH FORMAT csv
Good evening, I keep rereading https://www.postgresql.org/docs/9.6/static/sql-copy.html but just can't figure the proper syntax to put some records into the table: words=> \d words_reviews; Table "public.words_reviews" Column | Type | Modifiers -+--+--- uid | integer | not null author | integer | not null nice| integer | not null review | character varying(255) | updated | timestamp with time zone | not null Indexes: "words_reviews_pkey" PRIMARY KEY, btree (uid, author) Check constraints: "words_reviews_check" CHECK (uid <> author) "words_reviews_nice_check" CHECK (nice = 0 OR nice = 1) Foreign-key constraints: "words_reviews_author_fkey" FOREIGN KEY (author) REFERENCES words_users(uid) ON DELETE CASCADE "words_reviews_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON DELETE CASCADE words=> COPY words_reviews (uid, author, nice, review, updated) FROM stdin WITH FORMAT 'csv'; ERROR: syntax error at or near "FORMAT" LINE 1: ...d, author, nice, review, updated) FROM stdin WITH FORMAT 'cs... ^ words=> 1,2,1,'1 is nice by 2','2017-03-01', words-> 1,3,1,'1 is nice by 3','2017-03-02', words-> 1,4,1,'1 is nice by 4','2017-03-03', words-> 2,1,1,'2 is nice by 1','2017-03-01', words-> 2,3,1,'2 is nice by 3','2017-03-02', words-> 2,4,0,'2 is not nice by 4','2017-03-03' words-> \. Invalid command \.. Try \? for help. words-> ; ERROR: syntax error at or near "1" LINE 1: 1,2,1,'1 is nice by 2','2017-03-01', ^ I am not sure if FORMAT csv or FORMAT 'csv' should be used. And I have tried adding/removing commas at the end of lines too. I have looked at pg_dump output, but it does not use csv. Regards Alex
Re: [GENERAL] COPY FROM STDIN
On 1/8/16 10:37 AM, Luke Coldiron wrote: On 1/6/16 9:45 PM, Luke Coldiron wrote: In the example above I'm not sure if I can use some sub struct of the SPIPlanPtr and hand it off to the DoCopy function as the CopyStmt or if I need to go about this entirely different. Any advice on the matter would be much appreciated. I don't know off-hand. I suggest you look at what psql does to implement \copy (note the \). -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com I took a look at the \copy but as far as I can tell this is using the frontend (client side) of postgresql and I need to be able to do this on the Oops. backend (server side). I don't see a way with this either to attach to the connection that called the c function and execute the copy statement. The DoCopy in commands/copy.h appears to me to be the server side copy command. And I think I can get it to work if I can figure out how to attach to the connection of the calling function and pass in the CopyStmt variable. That sounds... bad. I think the way to handle this is to add a new type to CopyDest and modify CopyGetData() and CopySendEndOfRow() accordingly. It might be tempting to use CopyState->filename as the pointer to a StringInfoData (StringInfo), but I'm not sure that's a great idea. I think it'd be better to add a new field to CopyStateData. -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY FROM STDIN
> On 1/6/16 9:45 PM, Luke Coldiron wrote: > > In the example above I'm not sure if I can use some sub struct of the > > SPIPlanPtr and hand it off to the DoCopy function as the CopyStmt or > > if I need to go about this entirely different. Any advice on the > > matter would be much appreciated. > > I don't know off-hand. I suggest you look at what psql does to implement \copy (note the \). > -- > Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com I took a look at the \copy but as far as I can tell this is using the frontend (client side) of postgresql and I need to be able to do this on the backend (server side). I don't see a way with this either to attach to the connection that called the c function and execute the copy statement. The DoCopy in commands/copy.h appears to me to be the server side copy command. And I think I can get it to work if I can figure out how to attach to the connection of the calling function and pass in the CopyStmt variable. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY FROM STDIN
On 1/6/16 9:45 PM, Luke Coldiron wrote: In the example above I'm not sure if I can use some sub struct of the SPIPlanPtr and hand it off to the DoCopy function as the CopyStmt or if I need to go about this entirely different. Any advice on the matter would be much appreciated. I don't know off-hand. I suggest you look at what psql does to implement \copy (note the \). -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY FROM STDIN
> On 1/4/16 12:18 PM, Luke Coldiron wrote:
> > Is there a way to achieve the performance of the COPY FROM STDIN command
> > within a C extension function connected to the db connection that called
> > the C function? I have text that I would like to receive as input to a C
> > function that contains many COPY command statements in the file that
> > would be parsed similar to how psql would handle the file but I don't
> > want to shell out to psql as I need to do all of this work on the db
> > connection that the function was called from as there are other commands
> > that I need to perform as well after before and after handling the COPY
> > commands on this same connection. I would like the unit of work to be
> > all or nothing and have the performance of the COPY FROM STDIN command
> > and not break things out into SELECT INTO or INSERT statements for
> > performance.
> >
> > Ideally I would like to be able to attach to the calling db connection
> > via SPI_connect() and then use the libpq library to issue the copy
> > commands via PQputCopyData, PQputCopyEnd.
>
> C functions can use SPI, so I'm not sure what the issue is?
>
> http://www.postgresql.org/docs/9.5/static/spi.html
>
> (BTW, you'll want to scroll to the bottom of that page...)
> --
> Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
> Experts in Analytics, Data Architecture and PostgreSQL
> Data in Trouble? Get it in Treble! http://bluetreble.com/
I should probably back the boat up. I'm not too familiar with the
inter-workings of postgres. I have a general understanding of the SPI API
and realize that other c functions can be called. Part of the problem is I'm
not sure which ones. The first thing I am trying to figure out is if I can
perform a COPY FROM STDIN command via a C extension function using the SPI.
>From what I read in the documentation it seemed to indicate that this may
not be possible
(http://www.postgresql.org/docs/9.3/static/spi-spi-execute.html).
Here is what I have tried thus far.
#include "postgres.h"
#include "fmgr.h"
#include "funcapi.h"
#include "executor/spi.h"
#include "catalog/pg_type.h"
#include "commands/copy.h"
// Attempt #1
Datum copy_test(PG_FUNCTION_ARGS)
{
SPI_connect();
int spi_status = SPI_execute("COPY public.test_table(val1, val2) FROM
stdin;" /* command */,
false, /* read_only */
0 /* count */);
if (spi_status != SPI_OK_SELECT)
{
ereport(ERROR, (errcode(ERRCODE_INTERNAL_ERROR),
errmsg_internal("Failed: %s.", SPI_result_code_string(spi_status;
}
SPI_finish();
PG_RETURN_VOID();
}
When I run the function above I get this error: ERROR: Failed:
SPI_ERROR_COPY. Which is what I would expect from the documentation.
However if I try something like this:
// Attempt #2
Datum copy_test(PG_FUNCTION_ARGS)
{
SPI_connect();
SPIPlanPtr plan = SPI_prepare("COPY public.test_table(val1, val2) FROM
stdin;" /* command */, 0 /* nargs */, NULL /* argtypes*/);
if (plan == NULL)
{
ereport(ERROR, (errcode(ERRCODE_INTERNAL_ERROR),
errmsg_internal("Failed: %s.", SPI_result_code_string(SPI_result;
}
SPI_finish();
PG_RETURN_VOID();
}
It works, although I really haven't got to the point of doing anything. It
looked like the "commands/copy.h" has the interface that I want to use but
I'm not sure if I am even going about it the correct way. Here roughly what
I am thinking I need to do.
// Attempt #3
Datum copy_test(PG_FUNCTION_ARGS)
{
SPI_connect();
SPIPlanPtr plan = SPI_prepare("COPY public.test_table(val1, val2) FROM
stdin;" /* command */, 0 /* nargs */, NULL /* argtypes*/);
if (plan == NULL)
{
ereport(ERROR, (errcode(ERRCODE_INTERNAL_ERROR),
errmsg_internal("Failed: %s.", SPI_result_code_string(SPI_result;
}
uint64 processed;
Oid table_oid = DoCopy(const CopyStmt *stmt,
"COPY public.test_table(val1, val2) FROM stdin;"
/* queryString */,
&processed /* processed */);
//CopyState cstate = BeginCopyFrom(table_oid /* rel */,
NULL
/* filename */,
false /* is_program */,
List
*attnamelist,
List
*options);
// Somehow
// End the copy command
// EndCopyFrom(cstate);
// TODO: Make use of the callback
// extern void CopyFromErrorCallback(void *arg);
// extern DestReceiver *CreateCopyDestReceiver(void);
SPI_finish();
PG_RETURN_VOID();
}
In the example above I'm not sure if I can use some sub struct of the
SPIPlanPtr and hand it off to the DoCopy function as the CopyStmt or if I
need to go about this entirely different. Any advice on the matter would be
much appreciated.
--
Sent via pgsql-general mailing list (p
Re: [GENERAL] COPY FROM STDIN
On 1/4/16 12:18 PM, Luke Coldiron wrote: Is there a way to achieve the performance of the COPY FROM STDIN command within a C extension function connected to the db connection that called the C function? I have text that I would like to receive as input to a C function that contains many COPY command statements in the file that would be parsed similar to how psql would handle the file but I don't want to shell out to psql as I need to do all of this work on the db connection that the function was called from as there are other commands that I need to perform as well after before and after handling the COPY commands on this same connection. I would like the unit of work to be all or nothing and have the performance of the COPY FROM STDIN command and not break things out into SELECT INTO or INSERT statements for performance. Ideally I would like to be able to attach to the calling db connection via SPI_connect() and then use the libpq library to issue the copy commands via PQputCopyData, PQputCopyEnd. C functions can use SPI, so I'm not sure what the issue is? http://www.postgresql.org/docs/9.5/static/spi.html (BTW, you'll want to scroll to the bottom of that page...) -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] COPY FROM STDIN
Is there a way to achieve the performance of the COPY FROM STDIN command within a C extension function connected to the db connection that called the C function? I have text that I would like to receive as input to a C function that contains many COPY command statements in the file that would be parsed similar to how psql would handle the file but I don't want to shell out to psql as I need to do all of this work on the db connection that the function was called from as there are other commands that I need to perform as well after before and after handling the COPY commands on this same connection. I would like the unit of work to be all or nothing and have the performance of the COPY FROM STDIN command and not break things out into SELECT INTO or INSERT statements for performance. Ideally I would like to be able to attach to the calling db connection via SPI_connect() and then use the libpq library to issue the copy commands via PQputCopyData, PQputCopyEnd.
Re: [GENERAL] copy ... from stdin csv; and bytea
Tom Lane wrote: Klint Gore <[EMAIL PROTECTED]> writes: > David Wilson wrote: >> I'm not certain how to check the actual byte width of a column within a >> row, > select length(bytea_field) from table If you want the actual on-disk footprint, use pg_column_size() Size on disk would have the compression from the default storage = extended wouldn't it? I verified it for myself manually anyway. copy (select * from original limit 5) to stdout with csv; create table foo (like original); alter table foo alter column bytea_field set storage external; copy foo from stdin with csv; select |reltoastrelid from pg_class where relanem = 'original' found the file for it and looked at it with a hex viewer. | klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] copy ... from stdin csv; and bytea
Klint Gore <[EMAIL PROTECTED]> writes: > David Wilson wrote: >> I'm not certain how to check the actual byte width of a column within a >> row, > select length(bytea_field) from table If you want the actual on-disk footprint, use pg_column_size() regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] copy ... from stdin csv; and bytea
David Wilson wrote: On Mon, Jul 28, 2008 at 1:24 AM, Klint Gore <[EMAIL PROTECTED]> wrote: > Try just a single \ > > e.g. > "ge.xls","application/vnd.ms-excel",71168,"\320\317\021\340\241[snip] Thanks- I did try that, and it at least gave the expected output from select, but is there a way to verify that it's actually handling it correctly rather than simply storing the sequence of characters? I'm not certain how to check the actual byte width of a column within a row, and I'd *really* rather not be storing 4 bytes for every 1 in the binary if I can avoid it- this column is already going to be doubling field width; quadrupling it would give me space headaches I really don't want to deal with. :) select length(bytea_field) from table You could use ||pg_relation_size|(|text|)| or ||pg_total_relation_size|(|text|) |to see how much disk space it takes up. You can play with the storage settings for the column if you want to try and handle the space better. see alter table set storage. klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] copy ... from stdin csv; and bytea
On Mon, Jul 28, 2008 at 1:24 AM, Klint Gore <[EMAIL PROTECTED]> wrote: > Try just a single \ > > e.g. > "ge.xls","application/vnd.ms-excel",71168,"\320\317\021\340\241[snip] Thanks- I did try that, and it at least gave the expected output from select, but is there a way to verify that it's actually handling it correctly rather than simply storing the sequence of characters? I'm not certain how to check the actual byte width of a column within a row, and I'd *really* rather not be storing 4 bytes for every 1 in the binary if I can avoid it- this column is already going to be doubling field width; quadrupling it would give me space headaches I really don't want to deal with. :) -- - David T. Wilson [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] copy ... from stdin csv; and bytea
David Wilson wrote: My application is adding a bytea column to a table into which data is dumped in approximately 4k row batches, one batch approximately every 10 seconds. To this point, those dumps have used copy from stdin; however, I'm having some difficulty getting bytea encodings to work with it. Specifically, I can't seem to get the parser to recognize that what I'm handing it is an escaped string that needs to be parsed back into individual bytes rather than stored as-is. The encoding is very straightforward for INSERT, of course, but the COPY ... FROM STDIN CSV doesn't seem to want to work no matter what I've tried: \\000 "\\000" "E'\\000'" etc. Is there a trick to this that I just didn't see in the documentation, or is this some limitation of CSV copy-in? If the latter, are there suggestions for workarounds other than to fallback on the inserts? Try just a single \ e.g. "ge.xls","application/vnd.ms-excel",71168,"\320\317\021\340\241[snip] klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] copy ... from stdin csv; and bytea
My application is adding a bytea column to a table into which data is dumped in approximately 4k row batches, one batch approximately every 10 seconds. To this point, those dumps have used copy from stdin; however, I'm having some difficulty getting bytea encodings to work with it. Specifically, I can't seem to get the parser to recognize that what I'm handing it is an escaped string that needs to be parsed back into individual bytes rather than stored as-is. The encoding is very straightforward for INSERT, of course, but the COPY ... FROM STDIN CSV doesn't seem to want to work no matter what I've tried: \\000 "\\000" "E'\\000'" etc. Is there a trick to this that I just didn't see in the documentation, or is this some limitation of CSV copy-in? If the latter, are there suggestions for workarounds other than to fallback on the inserts? Using 8.3.3, and this is specifically via libpq, if that makes a difference. Thanks much. -- - David T. Wilson [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY FROM STDIN instead of INSERT
>> Is it Ok to use COPY BINARY FROM STDIN instead of multiple INSERTs? >I don't think I would use BINARY, it seems likely to be susceptible >to changes in the underlying data type storage. From the docs: >"To determine the appropriate binary format for the actual tuple data >you should consult the PostgreSQL source [skipped] Yes, it's a problem, though it's solved already. I use BINARY for, say, initial data loading. >AFIAK, the locking semantics are the same as INSERT, i.e., it >does not lock the entire table or anything like that. Since it would >allow transactions that insert data to finish faster, it should >actually work better under high concurrency. Do hope that's right. Thanks. -- Best regards Ilja Golshtein ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] COPY FROM STDIN instead of INSERT
On Oct 18, 2006, at 5:20 AM, Ilja Golshtein wrote: When starting a database from scratch it is much faster to import the data and then create the indexes. The time to create index on a full table is less than the extra time from each index update from the inserts. The more indexes to update the more time updating indexes takes. The problem with a live database is removing the indexes slows down current users and if you are adding 2,000 rows to a table that already has 5,000,000 rows in it then you will loose the benefit. I am 100% agree with you. What you are describing is a very good and useful technique for some maintenance operations. My current goal is to increase performance in normal [almost ;)] OLTP mode of my application, so removing indexes for some time is not an option here. And my question remains. Is it Ok to use COPY BINARY FROM STDIN instead of multiple INSERTs? I don't think I would use BINARY, it seems likely to be susceptible to changes in the underlying data type storage. From the docs: "To determine the appropriate binary format for the actual tuple data you should consult the PostgreSQL source, in particular the *send and *recv functions for each column's data type (typically these functions are found in the src/backend/utils/adt/ directory of the source distribution)." Regular text COPY is quite a big performance win over INSERTs, and doesn't require your application to know the vagaries of the data storage. Also, if you have many indices, time to update them will probably dominate anyhow, making the difference between binary and text copy negligible. A was mentioned, COPY can only insert static data, and does not support rules (that you might use to support constraint exclusion, etc). AFIAK, the locking semantics are the same as INSERT, i.e., it does not lock the entire table or anything like that. Since it would allow transactions that insert data to finish faster, it should actually work better under high concurrency. -Casey ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] COPY FROM STDIN instead of INSERT
On 10/18/06, Ron Johnson <[EMAIL PROTECTED]> wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 10/18/06 09:47, Merlin Moncure wrote: > On 10/18/06, Ron Johnson <[EMAIL PROTECTED]> wrote: >> > I tested binary quite a bit and only found it to be a win if moving >> > blobs in and out of the database. On 'normal' tables of mixed fields >> > types of small size, it can actually be slower. Binary is a bit >> > faster for native types and bytea, and slower for character types. >> >> "native types"? > > types operated on directly by the processor. int2, int4, int8, float4, > and float8, and their various aliases :). > > in short, i think using binary for anything other than bytea is a > waste of effort/time, except for bytea. That's counter-intuitive, since you'd (well, I'd) think that doing a binary copy would be faster since the code would bypass the int-to- ascii conversion. you missed the point: binary copy is (very marginally) faster for 'native types' aka ints, etc. however text fields are slower according to my testing. however, the speed differences are extremely marginal overall. only exception to this is binary (bytea) fields...you get a 2x speed improvement easily due to skipping the encoding. merlin ---(end of broadcast)--- TIP 1: 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: [GENERAL] COPY FROM STDIN instead of INSERT
Ron Johnson wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 10/18/06 09:47, Merlin Moncure wrote: > > On 10/18/06, Ron Johnson <[EMAIL PROTECTED]> wrote: > >> > I tested binary quite a bit and only found it to be a win if moving > >> > blobs in and out of the database. On 'normal' tables of mixed fields > >> > types of small size, it can actually be slower. Binary is a bit > >> > faster for native types and bytea, and slower for character types. > >> > >> "native types"? > > > > types operated on directly by the processor. int2, int4, int8, float4, > > and float8, and their various aliases :). > > > > in short, i think using binary for anything other than bytea is a > > waste of effort/time, except for bytea. > > That's counter-intuitive, since you'd (well, I'd) think that doing a > binary copy would be faster since the code would bypass the int-to- > ascii conversion. Yeah, but on the other hand it has to do the htonl/ntohl conversion. (I'd guess that should be faster than the text-to-int anyway ...) -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 1: 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: [GENERAL] COPY FROM STDIN instead of INSERT
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 10/18/06 09:47, Merlin Moncure wrote: > On 10/18/06, Ron Johnson <[EMAIL PROTECTED]> wrote: >> > I tested binary quite a bit and only found it to be a win if moving >> > blobs in and out of the database. On 'normal' tables of mixed fields >> > types of small size, it can actually be slower. Binary is a bit >> > faster for native types and bytea, and slower for character types. >> >> "native types"? > > types operated on directly by the processor. int2, int4, int8, float4, > and float8, and their various aliases :). > > in short, i think using binary for anything other than bytea is a > waste of effort/time, except for bytea. That's counter-intuitive, since you'd (well, I'd) think that doing a binary copy would be faster since the code would bypass the int-to- ascii conversion. - -- Ron Johnson, Jr. Jefferson LA USA Is "common sense" really valid? For example, it is "common sense" to white-power racists that whites are superior to blacks, and that those with brown skins are mud people. However, that "common sense" is obviously wrong. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.5 (GNU/Linux) iD8DBQFFNkRyS9HxQb37XmcRAn4tAJ4xRFmA/T82/iFi4O+sfvBGk4Y+EgCfVjn0 CFs2nT9w6RxTj8dV5C4kBUk= =2RRX -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] COPY FROM STDIN instead of INSERT
On 10/18/06, Ron Johnson <[EMAIL PROTECTED]> wrote: > I tested binary quite a bit and only found it to be a win if moving > blobs in and out of the database. On 'normal' tables of mixed fields > types of small size, it can actually be slower. Binary is a bit > faster for native types and bytea, and slower for character types. "native types"? types operated on directly by the processor. int2, int4, int8, float4, and float8, and their various aliases :). in short, i think using binary for anything other than bytea is a waste of effort/time, except for bytea. merlin ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] COPY FROM STDIN instead of INSERT
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 10/18/06 08:03, Merlin Moncure wrote: > On 10/18/06, Martijn van Oosterhout wrote: >> Binary may be slightly faster because the datum parsing can be >> partially skipped, but that's hardly much benefit over a text copy. > > I tested binary quite a bit and only found it to be a win if moving > blobs in and out of the database. On 'normal' tables of mixed fields > types of small size, it can actually be slower. Binary is a bit > faster for native types and bytea, and slower for character types. "native types"? - -- Ron Johnson, Jr. Jefferson LA USA Is "common sense" really valid? For example, it is "common sense" to white-power racists that whites are superior to blacks, and that those with brown skins are mud people. However, that "common sense" is obviously wrong. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.5 (GNU/Linux) iD8DBQFFNjVcS9HxQb37XmcRAvuIAJ4jHzT3dqwTCs6jrQMrPabz6yDo3gCZAaRz smE6g1Yig973CLUhUX8CEc8= =lFRM -END PGP SIGNATURE- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] COPY FROM STDIN instead of INSERT
Ilja Golshtein wrote: And my question remains. Is it Ok to use COPY BINARY FROM STDIN instead of multiple INSERTs? If it does what you want then it is OK to use it. -- Shane Ambler [EMAIL PROTECTED] Get Sheeky @ http://Sheeky.Biz ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] COPY FROM STDIN instead of INSERT
On 10/18/06, Ilja Golshtein <[EMAIL PROTECTED]> wrote: I've tried to play with batches and with peculiar constructions like INSERT (SELECT .. UNION ALL SELECT ..) to improve performance, but not satisfied with the result I've got. postgresql 8.2 (beta) supports the 'multiple insert' syntax, so you can insert multiple rows in an insert statement without using 'union all'. it's pretty fast, although not as fast as copy. btw, if you have a lot of indexes on your table, the input method is not so important. aside: new insert syntax has one very nice side effect. assuming the table(s) are already defined, I can transfer data from mysql to postgresql via: mysqldump --compatible=postgresql esilo | grep INSERT | psql without any extra processing for most cases. gotta give some points to mysql for adding postgresql compatibility which sorta works. merlin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] COPY FROM STDIN instead of INSERT
On 10/18/06, Martijn van Oosterhout wrote: Binary may be slightly faster because the datum parsing can be partially skipped, but that's hardly much benefit over a text copy. I tested binary quite a bit and only found it to be a win if moving blobs in and out of the database. On 'normal' tables of mixed fields types of small size, it can actually be slower. Binary is a bit faster for native types and bytea, and slower for character types. merlin ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] COPY FROM STDIN instead of INSERT
>The reason why copy is faster is because it doesn't have to >parse/plan/execute all the queries. In exchange you can't use >expressions or joins to fill the table, only raw data. In other words, COPY has no hidden catches, and I should go with it and don't worry. Correct interpretation? ;) >Binary may be slightly faster because the datum parsing can be >partially skipped, but that's hardly much benefit over a text copy. I know example where it is up to three times faster. It depends on data. -- Best regards Ilja Golshtein ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] COPY FROM STDIN instead of INSERT
On Wed, Oct 18, 2006 at 04:20:41PM +0400, Ilja Golshtein wrote: > And my question remains. > Is it Ok to use COPY BINARY FROM STDIN instead of multiple INSERTs? The reason why copy is faster is because it doesn't have to parse/plan/execute all the queries. In exchange you can't use expressions or joins to fill the table, only raw data. Binary may be slightly faster because the datum parsing can be partially skipped, but that's hardly much benefit over a text copy. Hope this helps, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] COPY FROM STDIN instead of INSERT
>When starting a database from scratch it is much faster to import the >data and then create the indexes. The time to create index on a full >table is less than the extra time from each index update from the >inserts. The more indexes to update the more time updating indexes takes. > >The problem with a live database is removing the indexes slows down >current users and if you are adding 2,000 rows to a table that already >has 5,000,000 rows in it then you will loose the benefit. I am 100% agree with you. What you are describing is a very good and useful technique for some maintenance operations. My current goal is to increase performance in normal [almost ;)] OLTP mode of my application, so removing indexes for some time is not an option here. And my question remains. Is it Ok to use COPY BINARY FROM STDIN instead of multiple INSERTs? -- Best regards Ilja Golshtein ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] COPY FROM STDIN instead of INSERT
Ilja Golshtein wrote: Sounds like your working with an existing database - if you are starting from scratch (inserting data into an empty database) then there are other things that can help too. I am working with existing database, though I am interested what "other things" you mean. Basically when adding data to a table some of the time spent inserting is spent updating indexes. When starting a database from scratch it is much faster to import the data and then create the indexes. The time to create index on a full table is less than the extra time from each index update from the inserts. The more indexes to update the more time updating indexes takes. The problem with a live database is removing the indexes slows down current users and if you are adding 2,000 rows to a table that already has 5,000,000 rows in it then you will loose the benefit. -- Shane Ambler [EMAIL PROTECTED] Get Sheeky @ http://Sheeky.Biz ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] COPY FROM STDIN instead of INSERT
Hello! >Using COPY FROM STDIN is much faster than INSERT's (I am sure some out >there have test times to compare, I don't have any on hand) Yes, I know it is much faster. The question is about possible pay for this quickness. What if COPY, say, locks index until end of transaction (it is just an example, of course)? Such things are not so easy to discover during design or even test time. >Sounds like your working with an existing database - if you are starting >from scratch (inserting data into an empty database) then there are >other things that can help too. I am working with existing database, though I am interested what "other things" you mean. Many thanks. -- Best regards Ilja Golshtein ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] COPY FROM STDIN instead of INSERT
Ilja Golshtein wrote: Hello! One important use case in my libpq based application (PostgreSQL 8.1.4) is a sort of massive data loading. Currently it is implemented as a series of plain normal INSERTs (binary form of PQexecParams is used) and the problem here it is pretty slow. I've tried to play with batches and with peculiar constructions like INSERT (SELECT .. UNION ALL SELECT ..) to improve performance, but not satisfied with the result I've got. Now I try to figure out if it is possible to use COPY FROM STDIN instead of INSERT if I have to insert, say, more then 100 records at once. Hints are highly appreciated. The only limitaion mentioned in Manual is about Rules and I don't care about this since I don't use Rules. Am I going to come across with any other problems (concurrency, reliability, compatibility, whatever) on this way? Many thanks. Using COPY FROM STDIN is much faster than INSERT's (I am sure some out there have test times to compare, I don't have any on hand) Sounds like your working with an existing database - if you are starting from scratch (inserting data into an empty database) then there are other things that can help too. -- Shane Ambler [EMAIL PROTECTED] Get Sheeky @ http://Sheeky.Biz ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] COPY FROM STDIN instead of INSERT
Hello! One important use case in my libpq based application (PostgreSQL 8.1.4) is a sort of massive data loading. Currently it is implemented as a series of plain normal INSERTs (binary form of PQexecParams is used) and the problem here it is pretty slow. I've tried to play with batches and with peculiar constructions like INSERT (SELECT .. UNION ALL SELECT ..) to improve performance, but not satisfied with the result I've got. Now I try to figure out if it is possible to use COPY FROM STDIN instead of INSERT if I have to insert, say, more then 100 records at once. Hints are highly appreciated. The only limitaion mentioned in Manual is about Rules and I don't care about this since I don't use Rules. Am I going to come across with any other problems (concurrency, reliability, compatibility, whatever) on this way? Many thanks. -- Best regards Ilja Golshtein ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] COPY FROM STDIN not in local file
On Wed, 29 Sep 2004 14:40:34 -0500, Josh Close <[EMAIL PROTECTED]> wrote: > Is there a way to do COPY FROM STDIN from sql? So, remotely I could > run the copy command and somehow push the info over instead of having > it on the server. For those who are curious, I found a php implementation of this. It uses the postgre put_line function. -Josh ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] COPY FROM STDIN not in local file
You have to use psql's \copy. --- Josh Close wrote: > Is there a way to do COPY FROM STDIN from sql? So, remotely I could > run the copy command and somehow push the info over instead of having > it on the server. > > -Josh > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] COPY FROM STDIN not in local file
Is there a way to do COPY FROM STDIN from sql? So, remotely I could run the copy command and somehow push the info over instead of having it on the server. -Josh ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
