Re: A better COPY?

On Mon, Feb 27, 2012 at 00:54, Tim Uckun <timuckun(at)gmail(dot)com> wrote:
> The main reason I am not using COPY right now is because postgres will
> not allow unprivileged users to issue the COPY from FILENAME. The

The reason for that is a good one -- that would allow these users to
read any file from the disk, under PostgreSQL's credentials.

As Alban already suggested, you can use COPY FROM STDIN (badly named
perhaps) command and feed the data over a network connection; this is
allowed whenever INSERT is allowed. Libraries usually have a separate
API for sending this data, PQputCopyData in libpq for example.

Alternatively you can use the "file" FDW in PostgreSQL 9.1+. The
foreign table has to be created by a superuser, but can be SELECTed
from as normal users:
http://www.postgresql.org/docs/9.1/static/file-fdw.html

> 1. COPY from a text field in a table like this COPY from (select
> text_field from table where id =2) as text_data ...

The syntax is a bit different:
CREATE TABLE text_data AS select text_field from table where id=2

> 2. The copy command creates a table after a cursory examination of the
> data. If the data has headers it uses those field names

Sounds like a good idea, if anyone is interested in working on it.

Regards,
Marti