Horton

Re: COPY FROM WHEN condition

Daniel Verite <daniel@manitou-mail.org>

View thread View on postgr.es
From: "Daniel Verite" <daniel@manitou-mail.org>
To: "Pavel Stehule" <pavel.stehule@gmail.com>
Cc: "corey.huinker" <corey.huinker@gmail.com>,"David Fetter" <david@fetter.org>,nasbyj@amazon.com,"Surafel Temsgen" <surafel3000@gmail.com>,"Christoph Moench-Tegeder" <cmt@burggraben.net>,"PostgreSQL Hackers" <pgsql-hackers@lists.postgresql.org>
Date: 2018-11-02T11:58:12Z
Lists: pgsql-hackers 
	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 server that can use only super user with full rights.

And if superuser, one might use the file data wrapper [1]  to get
the same results without the need for the explicit COPY in the query.

BTW, this brings into question whether the [WHEN condition] clause
should be included in the options of file_fdw, as it supports pretty
much all COPY options.

Also, psql's \copy should gain the option too?


[1] https://www.postgresql.org/docs/current/static/file-fdw.html


Best regards,
-- 
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite

Commits

  1. Remove unused struct member, enforce multi_insert callback presence.

  2. Separate per-batch and per-tuple memory contexts in COPY

  3. Fix handling of volatile expressions in COPY FROM ... WHERE

  4. Allow COPY FROM to filter data using WHERE conditions

  5. Remove obsolete netbsd dynloader code