Thread: COPY FROM in psql

Hi,

suppose for a moment I want to write a psql script that loads some data
into a database. I don't want to write an absolute path into my script,
but merely know where the data file will be relative to my script
location.

naiively, you might try:
\set pwd '\'' `pwd` '\''
COPY table FROM :pwd || '/relative/path/to/data' ;

but that doesn't work because the concatenation operator can't be used
there. How should I be doing this?

Thanks,

Matthew

--
Matthew Vernon
Quantitative Veterinary Epidemiologist
Epidemiology Research Unit, SAC Inverness

Matthew Vernon <matthew.vernon@sac.ac.uk> writes:

> naiively, you might try:
> \set pwd '\'' `pwd` '\''
> COPY table FROM :pwd || '/relative/path/to/data' ;

I should also note that I want to run a series of these commands, hence
setting pwd once and then wanting to use it multiple times.

Matthew

--
Matthew Vernon
Quantitative Veterinary Epidemiologist
Epidemiology Research Unit, SAC Inverness

On 11/20/2012 11:47 AM, Matthew Vernon wrote:
> Matthew Vernon <matthew.vernon@sac.ac.uk> writes:
>
>> naiively, you might try:
>> \set pwd '\'' `pwd` '\''
>> COPY table FROM :pwd || '/relative/path/to/data' ;
>
> I should also note that I want to run a series of these commands, hence
> setting pwd once and then wanting to use it multiple times.
>
> Matthew
>
Since you're resting the full path anyway, does this work?

\set pwd '\'' `pwd` '/rest/of/path\''

Matthew Vernon <matthew.vernon@sac.ac.uk> writes:
> naiively, you might try:
> \set pwd '\'' `pwd` '\''
> COPY table FROM :pwd || '/relative/path/to/data' ;

Umm ... why don't you just use a relative path as-is, with \copy
instead of COPY?

    \copy table from 'relative/path/to/data'

The server-side COPY is likely to give you a lot of permission
headaches, ie the server's userid will need to be able to read that
file and the directories above it.

If you really need to do this I'd suggest doing the path-stitching
in the \set:

    \set target '\'' `pwd` '/relative/path/to/data\''
    COPY table FROM :target ;

            regards, tom lane

Matthew Vernon <matthew.vernon@sac.ac.uk> writes:

> naiively, you might try:
> \set pwd '\'' `pwd` '\''
> COPY table FROM :pwd || '/relative/path/to/data' ;

so I could do:

\set path '\'' `pwd` '/path/to/data1' '\''
COPY table1 FROM :path;

\set path '\'' `pwd` '/path/to/data2' '\''
COPY table2 FROM :path;

...but surely there's a better way?

Matthew

--
Matthew Vernon
Quantitative Veterinary Epidemiologist
Epidemiology Research Unit, SAC Inverness

tgl@sss.pgh.pa.us (Tom Lane) writes:

> Matthew Vernon <matthew.vernon@sac.ac.uk> writes:
>> naiively, you might try:
>> \set pwd '\'' `pwd` '\''
>> COPY table FROM :pwd || '/relative/path/to/data' ;
>
> Umm ... why don't you just use a relative path as-is, with \copy
> instead of COPY?

Thanks for the suggestion, but I was avoiding \copy because the
quantities of data involved are large, and the documentation suggests
that COPY is better than \copy for large data volumes.

Thanks,

Matthew

--
Matthew Vernon
Quantitative Veterinary Epidemiologist
Epidemiology Research Unit, SAC Inverness

On 2012-11-21, Matthew Vernon <matthew.vernon@sac.ac.uk> wrote:
> tgl@sss.pgh.pa.us (Tom Lane) writes:
>
>> Matthew Vernon <matthew.vernon@sac.ac.uk> writes:
>>> naiively, you might try:
>>> \set pwd '\'' `pwd` '\''
>>> COPY table FROM :pwd || '/relative/path/to/data' ;
>>
>> Umm ... why don't you just use a relative path as-is, with \copy
>> instead of COPY?
>
> Thanks for the suggestion, but I was avoiding \copy because the
> quantities of data involved are large, and the documentation suggests
> that COPY is better than \copy for large data volumes.

\copy  is translated to "COPY .... FROM STDIN"  by psql
performance should be almost as fast, you loose a little in the
buffering and re-streaming, but not much.

--
⚂⚃ 100% natural