Обсуждение: A better COPY?

I have a situation where I am pulling CSV data from various sources
and putting them into a database after they are cleaned up and such.
Currently I am doing bulk of the work outside the database using code
but I think the work would go much faster if I was to use import the
data into temp tables using the COPY command and then cleaning up
there.

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
only way I could get around this would be to shell out psql or
something but I dont really want to do that.

The other reason I am not using COPY is because it is cumbersome to
create and maintain tables just for the import.

So I am looking for a solution like this.

1. COPY from a text field in a table like this COPY from (select
text_field from table where id =2) as text_data ...
2. The copy command creates a table after a cursory examination of the
data.  If the data has headers it uses those field names, if the data
does not have headers it uses col1, col2 etc.  Optionally fields and
types could be specified.


Any suggestions?

Cheers.

On 26 Feb 2012, at 23:54, Tim Uckun 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
> only way I could get around this would be to shell out psql or
> something but I dont really want to do that.

Use COPY from STDIN and supply the contents of the file after that.

Alban Hertroys

--
The scale of a problem often equals the size of an ego.

On Mon, Feb 27, 2012 at 00:54, Tim Uckun <timuckun@gmail.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

>
>> 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


Really? Wow, I would have never guessed that. That's awesome.

Thanks.