COPY FROM WHEN condition

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

COPY FROM WHEN condition

Surafel Temesgen

Hello,

Currently we can not moves data from a file to a table based on some condition on a certain column but I think there are many use case for it that worth supporting. Attache is a patch for escaping a row that does not satisfy WHEN condition from inserting into a table and its work on the top of commit b68ff3ea672c06

and the syntax is

COPY table_name [ ( column_name [, ...] ) ]

FROM { 'filename' | PROGRAM 'command' | STDIN }

[ [ WITH ] ( option [, ...] ) ]

[ WHEN condition ]

comment ?

Regards

Surafel


copy_from_when_con_v1.patch (12K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: COPY FROM WHEN condition

Christoph Moench-Tegeder
## Surafel Temesgen ([hidden email]):

> 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 copy into a file just the countries whose names start with 'A':
 COPY (SELECT * FROM country WHERE country_name LIKE 'A%') TO '/usr1/proj/bray/sql/a_list_countries.copy';"

Regards,
Christoph

--
Spare Space.

Reply | Threaded
Open this post in threaded view
|

Re: COPY FROM WHEN condition

Surafel Temesgen


On Thu, Oct 11, 2018 at 12:00 PM Christoph Moench-Tegeder <[hidden email]> wrote:
You can:
  COPY ( query ) TO 'filename';
it is for COPY FROM

regards
Surafel
Reply | Threaded
Open this post in threaded view
|

Re: COPY FROM WHEN condition

Corey Huinker
On Thu, Oct 11, 2018 at 5:04 AM Surafel Temesgen <[hidden email]> wrote:


On Thu, Oct 11, 2018 at 12:00 PM Christoph Moench-Tegeder <[hidden email]> 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 look at a rejected patch for copy_srf() (set returning function)

Having a set returning function gives you the full expressiveness of SQL, at the cost of an extra materialization step.


 
Reply | Threaded
Open this post in threaded view
|

Re: COPY FROM WHEN condition

David Fetter
On Thu, Oct 11, 2018 at 05:12:48AM -0400, Corey Huinker wrote:

> On Thu, Oct 11, 2018 at 5:04 AM Surafel Temesgen <[hidden email]>
> wrote:
>
> >
> >
> > On Thu, Oct 11, 2018 at 12:00 PM Christoph Moench-Tegeder <
> > [hidden email]> 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 look at a rejected patch for
> copy_srf() (set returning function)
> https://www.postgresql.org/message-id/CADkLM%3DdoeiWQX4AGtDNG4PsWfSXz3ai7kY%3DPZm3sUhsUeev9Bg%40mail.gmail.com
> https://commitfest.postgresql.org/12/869/
>
> Having a set returning function gives you the full expressiveness of SQL,
> at the cost of an extra materialization step.

I wonder whether something JIT-like could elide this. A very
interesting subset of such WHEN clauses could be pretty
straight-forward to implement in a pretty efficient way.

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