Re: A better COPY?

From: Marti Raudsepp <marti(at)juffo(dot)org>
To: Tim Uckun <timuckun(at)gmail(dot)com>
Cc: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Re: A better COPY?
Date: 2012-02-27 09:57:50
Message-ID: CABRT9RAiMexxPCdUkYPC-K4GQZ+3APyht=g0Q54_BiEJgcOhTA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Chris Travers 2012-02-27 10:05:16 Re: Four issues why "old elephants" lack performance: Explanation sought Four issues why "old elephants" lack performance: Explanation sought
Previous Message Dmytrii Nagirniak 2012-02-26 23:57:07 Re: Optimise PostgreSQL for fast testing