Re: COPY table_name (single_column) FROM 'unknown.txt' DELIMITER E'\n'

On 5/6/21 7:41 AM, Isaac Morland wrote:
> On Thu, 6 May 2021 at 02:21, Darafei "Komяpa" Praliaskouski
> <me(at)komzpa(dot)net <mailto:me(at)komzpa(dot)net>> wrote:
>  
>
> What I would prefer is some new COPY mode like RAW that will just
> push whatever it gets on the stdin/input into the cell on the
> server side. This way it can be proxied by psql, utilize existing
> infra for passing streams and be used in shell scripting.
>
>
> Yes! A significant missing feature is “take this arbitrary bucket of
> bits and move it to/from the database from/to this file without
> modification of any kind”. There are all sorts of tutorials on the Web
> about how to almost do it or fake it or convert to hex or whatever but
> I’ve never seen a nice simple explanation of “here is the command:”.
>
> Of course there is a lot to think about. Coming out of the database,
> the query result must be exactly one row containing exactly one
> column; how do we handle other database results? Coming into the
> database, only one data value can come from a single file; so how do
> we populate the other columns of whatever table we copy to?
>

What I'd like is something a bit more general. We could extend the FDW
API to allow for a CopyStdin handler or some such. The input could be
sent unfiltered to the handler, which would do whatever it liked with
it. That way FDWs like file_fdw and file_text_array_fdw could read from
stdin, for example. I'm pretty sure it would handle the OP's use case.
The downside is you'd need to write an FDW handler, but that's not too
hard, and there are lots of examples.

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com