agora inbox for pgsql-hackers@postgresql.org
help / color / mirror / Atom feedFrom: David Fetter <david@fetter.org>
To: Corey Huinker <corey.huinker@gmail.com>
Cc: nasbyj@amazon.com
Cc: surafel3000@gmail.com
Cc: cmt@burggraben.net
Cc: pgsql-hackers@lists.postgresql.org
Subject: Re: COPY FROM WHEN condition
Date: Fri, 2 Nov 2018 15:21:32 +0100
Message-ID: <20181102142132.GJ12677@fetter.org> (raw)
In-Reply-To: <CADkLM=d94dynBhz0WXpg-8Wc2=aDCzO9J4AoikgXJ9d0uJyqAg@mail.gmail.com>
References: <CALAY4q_DdpWDuB5-Zyi-oTtO2uSk8pmy+dupiRe3AvAc++1imA@mail.gmail.com>
<20181011085925.5zopu7jvxzs5c4xq@squirrel.exwg.net>
<CALAY4q__47MCCgspPVETAeSpJYfaYGpwH2diDGp5KjfSxTma4A@mail.gmail.com>
<CADkLM=dmvGSpZ+h_CzFoGeuzH7mZT3TLttx-ikc+UYw_e6L2jg@mail.gmail.com>
<20181011153505.GL6157@fetter.org>
<5E61B32E-61CB-4EBB-BC8E-BE37DC6E79AB@amazon.com>
<20181101004155.GI12677@fetter.org>
<CADkLM=d94dynBhz0WXpg-8Wc2=aDCzO9J4AoikgXJ9d0uJyqAg@mail.gmail.com>
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, but what I was thinking of was that some
> > highly restricted subset of cases of SQL in general could lend
> > themselves to levels of optimization that would be impractical in
> > other contexts.
>
> If COPY (or a syntactical equivalent) can return a result set, then the
> whole of SQL is available to filter and aggregate the results and we don't
> have to invent new syntax, or endure confusion whenCOPY-WHEN syntax behaves
> subtly different from a similar FROM-WHERE.
That's an excellent point.
> Also, what would we be saving computationally? The whole file (or program
> output) has to be consumed no matter what, the columns have to be parsed no
> matter what. At least some of the columns have to be converted to their
> assigned datatypes enough to know whether or not to filter the row, but we
> might be able push that logic inside a copy. I'm thinking of something like
> this:
>
> 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 json) FROM '/path/to/foo.txt' WITH (FORMAT CSV, INLINE)
> WHERE x.d >= '2018-11-01'
>
>
> In this case, there is the *opportunity* to see the following optimizations:
> - columns c and e are never referenced, and need never be turned into a
> datum (though we might do so just to confirm that they conform to the data
> type)
That sounds like something that could go inside the WITH extension
I'm proposing above.
[STRICT_TYPE boolean DEFAULT true]?
This might not be something that has to be in version 1.
Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
view thread (90+ messages) latest in thread
Message-ID: <20181102142132.GJ12677@fetter.org>
Permalink: ../20181102142132.GJ12677@fetter.org/
Also on: postgresql.org/message-id/20181102142132.GJ12677@fetter.org
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: david@fetter.org, corey.huinker@gmail.com, nasbyj@amazon.com, surafel3000@gmail.com, cmt@burggraben.net, pgsql-hackers@lists.postgresql.org
Subject: Re: COPY FROM WHEN condition
In-Reply-To: <20181102142132.GJ12677@fetter.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox