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

On Thu, May 6, 2021, at 13:41, Isaac Morland wrote:
> On Thu, 6 May 2021 at 02:21, Darafei "Komяpa" Praliaskouski <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?
>

If the file is on the server, you can use pg_read_binary_file() for that purpose.

/Joel