Re: COPY FROM WHEN condition

Hi, On 2019-04-05 12:59:06 +1300, David Rowley wrote: > I read through the final commit to see what had changed and I noticed > that I had forgotten to remove nbuffers from CopyMultiInsertInfo when > changing from a hash table to a List. > > Patch to fix is attached. Pushed, thanks.

Re: COPY FROM WHEN condition

On Fri, 5 Apr 2019 at 12:32, Andres Freund wrote: > I've pushed this now. Besides those naming changes, I'd to re-add the > zero initialization (I did end up seing compiler warnings when compiling > with optimizations). Also some comment fixes. Thanks for pushing. > I added one more flush

Re: COPY FROM WHEN condition

Hi, On 2019-04-04 12:04:28 -0700, Andres Freund wrote: > On 2019-04-03 22:20:00 -0700, Andres Freund wrote: > > On 2019-04-03 20:00:09 +1300, David Rowley wrote: > > > Oops, I forgot about that one. v4 attached. > > > > I'm pretty happy with this. I'm doing some minor changes (e.g. don't > >

Re: COPY FROM WHEN condition

On Thu, 4 Apr 2019 at 18:20, Andres Freund wrote: > I'm pretty happy with this. I'm doing some minor changes (e.g. don't > like the function comment formatting that much, the tableam callback > needs docs, stuff like that), and then I'm going to push it tomorrow. > > I'm planning to attribute it

Re: COPY FROM WHEN condition

Hi, On 2019-04-03 20:00:09 +1300, David Rowley wrote: > Oops, I forgot about that one. v4 attached. I'm pretty happy with this. I'm doing some minor changes (e.g. don't like the function comment formatting that much, the tableam callback needs docs, stuff like that), and then I'm going to push

Re: COPY FROM WHEN condition

On Wed, 3 Apr 2019 at 18:56, Andres Freund wrote: > > +/* Class the buffer full if there are >= this many bytes of tuples stored > > */ > > +#define MAX_BUFFERED_BYTES 65535 > > Random aside: This seems pretty small (but should be changed separately. Yeah, my fingers were hovering

Re: COPY FROM WHEN condition

On 2019-04-03 18:44:32 +1300, David Rowley wrote: > (Fixed all of what you mentioned) > > On Wed, 3 Apr 2019 at 06:57, Andres Freund wrote: > > > +/* > > > + * CopyMultiInsertInfo_Flush > > > + * Write out all stored tuples in all buffers out to the > > > tables. > > > + * > > > + *

Re: COPY FROM WHEN condition

(Fixed all of what you mentioned) On Wed, 3 Apr 2019 at 06:57, Andres Freund wrote: > > +/* > > + * CopyMultiInsertInfo_Flush > > + * Write out all stored tuples in all buffers out to the tables. > > + * > > + * To save us from ending up with buffers for 1000s of partitions we remove >

Re: COPY FROM WHEN condition

Gah, ignore this mail - I've somehow accidentally sent this early. I'm not quite sure, but I miskeyed, and emacs sent it early, without going through mutt...

Re: COPY FROM WHEN condition

On 2019-04-03 06:41:49 +1300, David Rowley wrote: > However, I've ended up not doing it that way as the patch requires > more than just an array of TupleTableSlots to be stored in the > ResultRelInfo, it'll pretty much need all of what I have in > CopyMultiInsertBuffer, which includes line numbers

Re: COPY FROM WHEN condition

On 2019-04-03 06:41:49 +1300, David Rowley wrote: > However, I've ended up not doing it that way as the patch requires > more than just an array of TupleTableSlots to be stored in the > ResultRelInfo, it'll pretty much need all of what I have in > CopyMultiInsertBuffer, which includes line numbers

Re: COPY FROM WHEN condition

On Tue, 2 Apr 2019 at 14:11, Andres Freund wrote: > Why do we need that list membership check? If you append the > ResultRelInfo to the list when creating the ResultRelInfo's slots array, > you don't need to touch the list after a partition lookup - you know > it's a member if the ResultRelInfo

Re: COPY FROM WHEN condition

Hi, On 2019-04-02 14:06:52 +1300, David Rowley wrote: > On Tue, 2 Apr 2019 at 13:59, Andres Freund wrote: > > > > On 2019-04-02 13:41:57 +1300, David Rowley wrote: > > > On Tue, 2 Apr 2019 at 05:19, Andres Freund wrote: > > > > Thanks! I'm not quite clear whether you planning to continue

Re: COPY FROM WHEN condition

On Tue, 2 Apr 2019 at 13:59, Andres Freund wrote: > > On 2019-04-02 13:41:57 +1300, David Rowley wrote: > > On Tue, 2 Apr 2019 at 05:19, Andres Freund wrote: > > > Thanks! I'm not quite clear whether you planning to continue working on > > > this, or whether this is a handoff? Either is fine

Re: COPY FROM WHEN condition

Hi, On 2019-04-02 13:41:57 +1300, David Rowley wrote: > On Tue, 2 Apr 2019 at 05:19, Andres Freund wrote: > > > > On 2019-04-02 04:23:20 +1300, David Rowley wrote: > > > On Mon, 1 Apr 2019 at 08:05, Andres Freund wrote: > > > > I'll work on pushing all the other pending tableam patches today -

Re: COPY FROM WHEN condition

On Tue, 2 Apr 2019 at 05:19, Andres Freund wrote: > > On 2019-04-02 04:23:20 +1300, David Rowley wrote: > > On Mon, 1 Apr 2019 at 08:05, Andres Freund wrote: > > > I'll work on pushing all the other pending tableam patches today - > > > leaving COPY the last non pluggable part. You'd written in

Re: COPY FROM WHEN condition

Hi, On 2019-04-02 04:23:20 +1300, David Rowley wrote: > On Mon, 1 Apr 2019 at 08:05, Andres Freund wrote: > > I'll work on pushing all the other pending tableam patches today - > > leaving COPY the last non pluggable part. You'd written in a private > > email that you might try to work on this

Re: COPY FROM WHEN condition

On Mon, 1 Apr 2019 at 08:05, Andres Freund wrote: > I'll work on pushing all the other pending tableam patches today - > leaving COPY the last non pluggable part. You'd written in a private > email that you might try to work on this on Monday, so I think I'll give > this a shot on Tuesday if

Re: COPY FROM WHEN condition

Hi, On 2019-04-01 02:00:26 +1300, David Rowley wrote: > On Fri, 29 Mar 2019 at 01:15, Andres Freund wrote: > > On 2019-03-28 20:48:47 +1300, David Rowley wrote: > > > I had a look at this and performance has improved again, thanks. > > > However, I'm not sure if the patch is exactly what we

Re: COPY FROM WHEN condition

On Fri, 29 Mar 2019 at 01:15, Andres Freund wrote: > On 2019-03-28 20:48:47 +1300, David Rowley wrote: > > I had a look at this and performance has improved again, thanks. > > However, I'm not sure if the patch is exactly what we need, let me > > explain. > > I'm not entirely sure either, I just

Re: COPY FROM WHEN condition

Hi, On 2019-03-28 20:48:47 +1300, David Rowley wrote: > I had a look at this and performance has improved again, thanks. > However, I'm not sure if the patch is exactly what we need, let me > explain. I'm not entirely sure either, I just haven't really seen an alternative that's convincing. >

Re: COPY FROM WHEN condition

On Wed, 27 Mar 2019 at 18:49, Andres Freund wrote: > Here's a version that applies onto HEAD. It needs a bit more cleanup > (I'm not sure I like the addition of ri_batchInsertSlots to store slots > for each partition, there's some duplicated code around slot array and > slot creation, docs for

Re: COPY FROM WHEN condition

Hi, On 2019-01-29 07:22:16 -0800, Andres Freund wrote: > On January 29, 2019 6:25:59 AM PST, Tomas Vondra > wrote: > >On 1/29/19 8:18 AM, David Rowley wrote: > >> ... > >> Here are my performance tests of with and without your change to the > >> memory contexts (I missed where you posted your

Re: COPY FROM WHEN condition

Hi, On 2019-01-30 10:33:30 +1300, David Rowley wrote: > On Wed, 30 Jan 2019 at 10:12, Andres Freund wrote: > > > > On 2019-01-30 10:05:35 +1300, David Rowley wrote: > > > On Wed, 30 Jan 2019 at 04:22, Andres Freund wrote: > > > > I think I might have a patch addressing the problem incidentally.

Re: COPY FROM WHEN condition

On Wed, 30 Jan 2019 at 10:12, Andres Freund wrote: > > On 2019-01-30 10:05:35 +1300, David Rowley wrote: > > On Wed, 30 Jan 2019 at 04:22, Andres Freund wrote: > > > I think I might have a patch addressing the problem incidentally. For > > > pluggable storage I slotified copy.c, which also

Re: COPY FROM WHEN condition

Hi, On 2019-01-30 10:05:35 +1300, David Rowley wrote: > On Wed, 30 Jan 2019 at 04:22, Andres Freund wrote: > > I think I might have a patch addressing the problem incidentally. For > > pluggable storage I slotified copy.c, which also removes the first > > heap_form_tuple. Quite possible that

Re: COPY FROM WHEN condition

On Tue, 29 Jan 2019 at 22:51, Tomas Vondra wrote: > > On 1/29/19 8:18 AM, David Rowley wrote: > > So looks like your change slows this code down 4% for this test case. > > That's about twice as bad as the 2.2% regression that I had to solve > > for the multi-insert partition patch (of which

Re: COPY FROM WHEN condition

On Wed, 30 Jan 2019 at 04:22, Andres Freund wrote: > I think I might have a patch addressing the problem incidentally. For > pluggable storage I slotified copy.c, which also removes the first > heap_form_tuple. Quite possible that nothing more is needed. I've removed the > batch context

Re: COPY FROM WHEN condition

On Wed, 30 Jan 2019 at 03:26, Tomas Vondra wrote: > > On 1/29/19 8:18 AM, David Rowley wrote: > > (details about performance regression) > > How do I reproduce this? I don't see this test in the thread you linked, > so I'm not sure how many partitions you were using, what's the structure > of the

Re: COPY FROM WHEN condition

Hi, On January 29, 2019 6:25:59 AM PST, Tomas Vondra wrote: >On 1/29/19 8:18 AM, David Rowley wrote: >> ... >> Here are my performance tests of with and without your change to the >> memory contexts (I missed where you posted your results). >> >> $ cat bench.pl >> for (my $i=0; $i < 8912891;

Re: COPY FROM WHEN condition

On 1/29/19 8:18 AM, David Rowley wrote: > ... > Here are my performance tests of with and without your change to the > memory contexts (I missed where you posted your results). > > $ cat bench.pl > for (my $i=0; $i < 8912891; $i++) { > print "1\n1\n2\n2\n"; > } > 36a1281f86c: (with your change) >

Re: COPY FROM WHEN condition

On 1/29/19 8:18 AM, David Rowley wrote: > On Wed, 23 Jan 2019 at 06:35, Tomas Vondra > wrote: >> It turned out to be a tad more complex due to partitioning, because when >> we find the partitions do not match, the tuple is already allocated in >> the "current" context (be it per-tuple or

Re: COPY FROM WHEN condition

On Wed, 23 Jan 2019 at 06:35, Tomas Vondra wrote: > It turned out to be a tad more complex due to partitioning, because when > we find the partitions do not match, the tuple is already allocated in > the "current" context (be it per-tuple or batch). So we can't just free > the whole context at

Re: COPY FROM WHEN condition

On 1/22/19 10:00 AM, Surafel Temesgen wrote: > > > On Mon, Jan 21, 2019 at 6:22 PM Tomas Vondra > mailto:tomas.von...@2ndquadrant.com>> wrote: > > > I think the condition can be just > >     if (contain_volatile_functions(cstate->whereClause)) { ... } > > I've pushed a fix for

Re: COPY FROM WHEN condition

Hi, On 2019-01-22 18:35:21 +0100, Tomas Vondra wrote: > On 1/21/19 11:15 PM, Tomas Vondra wrote: > > On 1/21/19 7:51 PM, Andres Freund wrote: > >> I'm *not* convinced by this. I think it's bad enough that we do this for > >> normal COPY, but for WHEN, we could end up *never* resetting before the

Re: COPY FROM WHEN condition

On 1/21/19 11:15 PM, Tomas Vondra wrote: > > > On 1/21/19 7:51 PM, Andres Freund wrote: >> Hi, >> >> On 2019-01-21 16:22:11 +0100, Tomas Vondra wrote: >>> >>> >>> On 1/21/19 4:33 AM, Tomas Vondra wrote: On 1/21/19 3:12 AM, Andres Freund wrote: > On 2019-01-20 18:08:05 -0800,

Re: COPY FROM WHEN condition

On Mon, Jan 21, 2019 at 6:22 PM Tomas Vondra wrote: > > I think the condition can be just > > if (contain_volatile_functions(cstate->whereClause)) { ... } > > > yes it can be. regards Surafel

Re: COPY FROM WHEN condition

On 1/21/19 7:51 PM, Andres Freund wrote: > Hi, > > On 2019-01-21 16:22:11 +0100, Tomas Vondra wrote: >> >> >> On 1/21/19 4:33 AM, Tomas Vondra wrote: >>> >>> >>> On 1/21/19 3:12 AM, Andres Freund wrote: On 2019-01-20 18:08:05 -0800, Andres Freund wrote: > On 2019-01-20 21:00:21 -0500,

Re: COPY FROM WHEN condition

Hi, On 2019-01-21 16:22:11 +0100, Tomas Vondra wrote: > > > On 1/21/19 4:33 AM, Tomas Vondra wrote: > > > > > > On 1/21/19 3:12 AM, Andres Freund wrote: > >> On 2019-01-20 18:08:05 -0800, Andres Freund wrote: > >>> On 2019-01-20 21:00:21 -0500, Tomas Vondra wrote: > > > On

Re: COPY FROM WHEN condition

On 1/21/19 4:33 AM, Tomas Vondra wrote: > > > On 1/21/19 3:12 AM, Andres Freund wrote: >> On 2019-01-20 18:08:05 -0800, Andres Freund wrote: >>> On 2019-01-20 21:00:21 -0500, Tomas Vondra wrote: On 1/20/19 8:24 PM, Andres Freund wrote: > Hi, > > On 2019-01-20

Re: COPY FROM WHEN condition

On 1/21/19 3:12 AM, Andres Freund wrote: > On 2019-01-20 18:08:05 -0800, Andres Freund wrote: >> On 2019-01-20 21:00:21 -0500, Tomas Vondra wrote: >>> >>> >>> On 1/20/19 8:24 PM, Andres Freund wrote: Hi, On 2019-01-20 00:24:05 +0100, Tomas Vondra wrote: > On 1/14/19 10:25 PM,

Re: COPY FROM WHEN condition

On 1/21/19 3:12 AM, Andres Freund wrote: > On 2019-01-20 18:08:05 -0800, Andres Freund wrote: >> On 2019-01-20 21:00:21 -0500, Tomas Vondra wrote: >>> >>> >>> On 1/20/19 8:24 PM, Andres Freund wrote: Hi, On 2019-01-20 00:24:05 +0100, Tomas Vondra wrote: > On 1/14/19 10:25 PM,

Re: COPY FROM WHEN condition

On 2019-01-20 18:08:05 -0800, Andres Freund wrote: > On 2019-01-20 21:00:21 -0500, Tomas Vondra wrote: > > > > > > On 1/20/19 8:24 PM, Andres Freund wrote: > > > Hi, > > > > > > On 2019-01-20 00:24:05 +0100, Tomas Vondra wrote: > > > > On 1/14/19 10:25 PM, Tomas Vondra wrote: > > > > > On

Re: COPY FROM WHEN condition

On 2019-01-20 21:00:21 -0500, Tomas Vondra wrote: > > > On 1/20/19 8:24 PM, Andres Freund wrote: > > Hi, > > > > On 2019-01-20 00:24:05 +0100, Tomas Vondra wrote: > > > On 1/14/19 10:25 PM, Tomas Vondra wrote: > > > > On 12/13/18 8:09 AM, Surafel Temesgen wrote: > > > > > > > > > > > > > > >

Re: COPY FROM WHEN condition

On 1/20/19 8:24 PM, Andres Freund wrote: Hi, On 2019-01-20 00:24:05 +0100, Tomas Vondra wrote: On 1/14/19 10:25 PM, Tomas Vondra wrote: On 12/13/18 8:09 AM, Surafel Temesgen wrote: On Wed, Dec 12, 2018 at 9:28 PM Tomas Vondra mailto:tomas.von...@2ndquadrant.com>> wrote: Can you

Re: COPY FROM WHEN condition

Hi, On 2019-01-20 00:24:05 +0100, Tomas Vondra wrote: > On 1/14/19 10:25 PM, Tomas Vondra wrote: > > On 12/13/18 8:09 AM, Surafel Temesgen wrote: > >> > >> > >> On Wed, Dec 12, 2018 at 9:28 PM Tomas Vondra > >> mailto:tomas.von...@2ndquadrant.com>> wrote: > >> > >> > >> Can you also update

Re: COPY FROM WHEN condition

On Sun, Jan 20, 2019 at 2:24 AM Tomas Vondra wrote: > > Pushed, thanks for the patch. > > cheers > > > Thank you regards Surafel

Re: COPY FROM WHEN condition

On 1/14/19 10:25 PM, Tomas Vondra wrote: > On 12/13/18 8:09 AM, Surafel Temesgen wrote: >> >> >> On Wed, Dec 12, 2018 at 9:28 PM Tomas Vondra >> mailto:tomas.von...@2ndquadrant.com>> wrote: >> >> >> Can you also update the docs to mention that the functions called from >> the WHERE

Re: COPY FROM WHEN condition

On 12/13/18 8:09 AM, Surafel Temesgen wrote: > > > On Wed, Dec 12, 2018 at 9:28 PM Tomas Vondra > mailto:tomas.von...@2ndquadrant.com>> wrote: > > > Can you also update the docs to mention that the functions called from > the WHERE clause does not see effects of the COPY itself? > >

Re: COPY FROM WHEN condition

On Wed, Dec 12, 2018 at 9:28 PM Tomas Vondra wrote: > > Can you also update the docs to mention that the functions called from > the WHERE clause does not see effects of the COPY itself? > > *Of course, i also add same comment to insertion method selection* *regards * *Surafel* diff --git

Re: COPY FROM WHEN condition

On 12/12/18 7:05 AM, Surafel Temesgen wrote: > > > On Tue, Dec 4, 2018 at 12:44 PM Alvaro Herrera > wrote: > > After reading this thread, I think I like WHERE better than FILTER. > Tally: > > WHERE: Adam Berlin, Lim Myungkyu, Dean Rasheed, yours

Re: COPY FROM WHEN condition

On Tue, Dec 4, 2018 at 12:44 PM Alvaro Herrera wrote: > After reading this thread, I think I like WHERE better than FILTER. > Tally: > > WHERE: Adam Berlin, Lim Myungkyu, Dean Rasheed, yours truly > FILTER: Tomas Vondra, Surafel Temesgen > > > accepting tally result i change the keyword to

Re: COPY FROM WHEN condition

On 12/6/18 4:52 PM, Robert Haas wrote: > On Wed, Nov 28, 2018 at 6:17 PM Tomas Vondra > wrote: >>> Comparing with overhead of setting snapshot before evaluating every row >>> and considering this >>> >>> kind of usage is not frequent it seems to me the behavior is acceptable >> >> I'm not

Re: COPY FROM WHEN condition

On Wed, Nov 28, 2018 at 6:17 PM Tomas Vondra wrote: > > Comparing with overhead of setting snapshot before evaluating every row > > and considering this > > > > kind of usage is not frequent it seems to me the behavior is acceptable > > I'm not really buying the argument that this behavior is

Re: COPY FROM WHEN condition

On 12/4/18 10:44 AM, Alvaro Herrera wrote: > After reading this thread, I think I like WHERE better than FILTER. > Tally: > > WHERE: Adam Berlin, Lim Myungkyu, Dean Rasheed, yours truly > FILTER: Tomas Vondra, Surafel Temesgen > > Couldn't find others expressing an opinion in this regard. >

Re: COPY FROM WHEN condition

After reading this thread, I think I like WHERE better than FILTER. Tally: WHERE: Adam Berlin, Lim Myungkyu, Dean Rasheed, yours truly FILTER: Tomas Vondra, Surafel Temesgen Couldn't find others expressing an opinion in this regard. On 2018-Nov-30, Tomas Vondra wrote: > I think it should be

Re: COPY FROM WHEN condition

On 11/30/18 2:00 PM, Surafel Temesgen wrote: On Thu, Nov 29, 2018 at 2:17 AM Tomas Vondra mailto:tomas.von...@2ndquadrant.com>> wrote: (c) allow VOLATILE functions in the FILTER clause, but change the behavior to make the behavior sane  Did changing the behavior means getting

Re: COPY FROM WHEN condition

On Thu, Nov 29, 2018 at 2:17 AM Tomas Vondra wrote: (c) allow VOLATILE functions in the FILTER clause, but change the > behavior to make the behavior sane > Did changing the behavior means getting new snapshot before evaluating a tuple to ensure the function sees results of any previously

Re: COPY FROM WHEN condition

On 11/26/18 2:25 PM, Surafel Temesgen wrote: > > > On Sat, Nov 24, 2018 at 5:09 AM Tomas Vondra > mailto:tomas.von...@2ndquadrant.com>> wrote: > > > 1) While comparing this to the FILTER clause we already have for > aggregates, I've noticed the aggregate version is > >     FILTER

Re: COPY FROM WHEN condition

On Sat, Nov 24, 2018 at 5:09 AM Tomas Vondra wrote: > > 1) While comparing this to the FILTER clause we already have for > aggregates, I've noticed the aggregate version is > > FILTER '(' WHERE a_expr ')' > > while here we have > > FILTER '(' a_expr ')' > > For a while I was thinking

Re: COPY FROM WHEN condition

On Sat, Nov 24, 2018 at 12:02 PM Dean Rasheed wrote: > Right now we have 2 syntaxes for filtering rows in queries, both of > which use WHERE immediately before the condition: > > 1). SELECT ... FROM ... WHERE condition > > 2). SELECT agg_fn FILTER (WHERE condition) FROM ... > > I'm not a huge

Re: COPY FROM WHEN condition

On Sat, 24 Nov 2018 at 02:09, Tomas Vondra wrote: > On 11/23/18 12:14 PM, Surafel Temesgen wrote: > > On Sun, Nov 11, 2018 at 11:59 PM Tomas Vondra > > mailto:tomas.von...@2ndquadrant.com>> wrote: > > So, what about using FILTER here? We already use it for aggregates when > > filtering

Re: COPY FROM WHEN condition

On 11/23/18 12:14 PM, Surafel Temesgen wrote: > > > On Sun, Nov 11, 2018 at 11:59 PM Tomas Vondra > mailto:tomas.von...@2ndquadrant.com>> wrote: > > > So, what about using FILTER here? We already use it for aggregates when > filtering rows to process. > > i think its good idea and

Re: COPY FROM WHEN condition

On Sun, Nov 11, 2018 at 11:59 PM Tomas Vondra wrote: > > So, what about using FILTER here? We already use it for aggregates when > filtering rows to process. > > i think its good idea and describe its purpose more. Attache is a patch that use FILTER instead regards Surafel diff --git

RE: COPY FROM WHEN condition

>> COPY table_name WHERE (some_condition) >> >> Users should already be familiar with the idea that WHERE performs a filter. >> > So, what about using FILTER here? We already use it for aggregates when > filtering rows to process. > That being said, I have no strong feelings either way. I'd be

Re: COPY FROM WHEN condition

On 11/9/18 4:51 PM, Adam Berlin wrote: > As a newcomer to this patch, when I read this example: > > COPY table_name WHEN (some_condition) > > .. I expect COPY to only be run when the condition is true, and I do not expect the WHEN clause to filter rows. I'm curious what you think about: >

Re: COPY FROM WHEN condition

As a newcomer to this patch, when I read this example: COPY table_name WHEN (some_condition) .. I expect COPY to only be run when the condition is true, and I do not expect the WHEN clause to filter rows. I'm curious what you think about: COPY table_name WHERE (some_condition) Users should

Re: COPY FROM WHEN condition

David Fetter wrote: > It also seems like a violation of separation of concerns to couple > FEBE to grammar, so there'd need to be some way to do those things > separately, too. After re-reading psql/copy.c, I withdraw what I said upthread: it doesn't appear necessary to add anything to

Re: COPY FROM WHEN condition

> > > > SELECT x.a, sum(x.b) > > FROM ( COPY INLINE '/path/to/foo.txt' FORMAT CSV ) as x( a integer, b > numeric, c text, d date, e json) ) > > Apologies for bike-shedding, but wouldn't the following be a better > fit with the current COPY? > > COPY t(a integer, b numeric, c text, d date, e

Re: COPY FROM WHEN condition

On Fri, Nov 02, 2018 at 12:58:12PM +0100, Daniel Verite wrote: > Pavel Stehule wrote: > > > > SELECT x.a, sum(x.b) > > > FROM ( COPY INLINE '/path/to/foo.txt' FORMAT CSV ) as x( a integer, b > > > numeric, c text, d date, e json) ) > > > WHERE x.d >= '2018-11-01' > > > > > > > > Without

Re: COPY FROM WHEN condition

On Thu, Nov 01, 2018 at 10:57:25PM -0400, Corey Huinker wrote: > > > > > Are you thinking something like having a COPY command that provides > > > results in such a way that they could be referenced in a FROM clause > > > (perhaps a COPY that defines a cursor…)? > > > > That would also be nice,

Re: COPY FROM WHEN condition

On 11/02/2018 03:57 AM, Corey Huinker wrote: > > Are you thinking something like having a COPY command that provides > > results in such a way that they could be referenced in a FROM clause > > (perhaps a COPY that defines a cursor…)? > > That would also be nice, but what I was

Re: COPY FROM WHEN condition

Pavel Stehule wrote: > > SELECT x.a, sum(x.b) > > FROM ( COPY INLINE '/path/to/foo.txt' FORMAT CSV ) as x( a integer, b > > numeric, c text, d date, e json) ) > > WHERE x.d >= '2018-11-01' > > > > > Without some special feature this example is not extra useful. It is based > on copy on

Re: COPY FROM WHEN condition

hi, On Wed, Oct 31, 2018 at 10:54 AM Masahiko Sawada wrote: > On Tue, Oct 30, 2018 at 11:47 PM Surafel Temesgen > wrote: > > I've looked at this patch and tested. > > When I use a function returning string in WHEN clause I got the following > error: > > =# copy test from '/tmp/aaa.csv' (format

Re: COPY FROM WHEN condition

pá 2. 11. 2018 v 3:57 odesílatel Corey Huinker napsal: > > Are you thinking something like having a COPY command that provides >> > results in such a way that they could be referenced in a FROM clause >> > (perhaps a COPY that defines a cursor…)? >> >> That would also be nice, but what I was

Re: COPY FROM WHEN condition

> > > Are you thinking something like having a COPY command that provides > > results in such a way that they could be referenced in a FROM clause > > (perhaps a COPY that defines a cursor…)? > > That would also be nice, but what I was thinking of was that some > highly restricted subset of cases

Re: COPY FROM WHEN condition

On Wed, Oct 31, 2018 at 11:21:33PM +, Nasby, Jim wrote: > On Oct 11, 2018, at 10:35 AM, David Fetter wrote: > > > >> It didn't get far, but you may want to take a look at a rejected patch for > >> copy_srf() (set returning function) > >>

Re: COPY FROM WHEN condition

On Oct 11, 2018, at 10:35 AM, David Fetter wrote: > >> It didn't get far, but you may want to take a look at a rejected patch for >> copy_srf() (set returning function) >> https://www.postgresql.org/message-id/CADkLM%3DdoeiWQX4AGtDNG4PsWfSXz3ai7kY%3DPZm3sUhsUeev9Bg%40mail.gmail.com >>

Re: COPY FROM WHEN condition

On Tue, Oct 30, 2018 at 11:47 PM Surafel Temesgen wrote: > > > Hi, > Thank you for looking at it . > On Sun, Oct 28, 2018 at 7:19 PM Tomas Vondra > wrote: >> >> >> 1) I think this deserves at least some regression tests. Plenty of tests >> already use COPY, but there's no coverage for the new

Re: COPY FROM WHEN condition

Hi, Thank you for looking at it . On Sun, Oct 28, 2018 at 7:19 PM Tomas Vondra wrote: > > 1) I think this deserves at least some regression tests. Plenty of tests > already use COPY, but there's no coverage for the new piece. So let's > add a new test suite, or maybe add a couple of tests into

RE: COPY FROM WHEN condition

Hello, Basically, this patch worked very well in my tests. > 3) For COPY TO, the WHEN clause is accepted but ignored, leading to confusing > cases like this: I found same issue. postgres=# copy t1 to '/home/lmk/t1.data' when c1 < 1; In the 'COPY TO' statement, 'WHEN clause' does not do

Re: COPY FROM WHEN condition

Hi, I've taken a quick look at this on the way back from pgconf.eu, and it seems like a nice COPY improvement in a fairly good shape. Firstly, I think it's a valuable because it allows efficiently importing a subset of data. Currently, we either have to create an intermediate table, copy all the

Re: COPY FROM WHEN condition

On Fri, Oct 26, 2018 at 9:09 AM 임명규 wrote: > Hello, > > I want test this patch, but can't apply it. > > (master)$ git apply copy_from_when_con_v1.patch > i use patch and it work try (master)$ patch -p1 < copy_from_when_con_v1.patch regards Surafel

RE: COPY FROM WHEN condition

Hello, I want test this patch, but can't apply it. (master)$ git apply copy_from_when_con_v1.patch error: patch failed: src/backend/commands/copy.c:849 error: src/backend/commands/copy.c: patch does not apply fix or another way, let me know. Best regards, Myungkyu, Lim

Re: COPY FROM WHEN condition

On Thu, Oct 11, 2018 at 05:12:48AM -0400, Corey Huinker wrote: > On Thu, Oct 11, 2018 at 5:04 AM Surafel Temesgen > wrote: > > > > > > > On Thu, Oct 11, 2018 at 12:00 PM Christoph Moench-Tegeder < > > c...@burggraben.net> wrote: > > > >> You can: > >> COPY ( query ) TO 'filename'; > >> > > it

Re: COPY FROM WHEN condition

On Thu, Oct 11, 2018 at 5:04 AM Surafel Temesgen wrote: > > > On Thu, Oct 11, 2018 at 12:00 PM Christoph Moench-Tegeder < > c...@burggraben.net> wrote: > >> You can: >> COPY ( query ) TO 'filename'; >> > it is for COPY FROM > > regards > Surafel > It didn't get far, but you may want to take a

Re: COPY FROM WHEN condition

On Thu, Oct 11, 2018 at 12:00 PM Christoph Moench-Tegeder < c...@burggraben.net> wrote: > You can: > COPY ( query ) TO 'filename'; > it is for COPY FROM regards Surafel

Re: COPY FROM WHEN condition

## Surafel Temesgen (surafel3...@gmail.com): > Currently we can not moves data from a file to a table based on some > condition on a certain column You can: COPY ( query ) TO 'filename'; There's even an example in the documentation: https://www.postgresql.org/docs/10/static/sql-copy.html "To