pgsql -c in bash shell script quote problem

[Database Administrator]

Hi,

I want to automate a task to copy values to a table.

The shell script is this (simple form):
psql -U postgres -d sedait_db -c '
COPY dp_calc_temp
(
ano
)
FROM \'/usr/local/pgsql/home/dw/iva_dp/calculados/
dp_calculados_juntos.csv\' WITH CSV ; '

The problem is that I have to use ' to delimit the entire psql
command, but ' is also required to for the file reference.

I tried to escape ' with \', as in the example, but I get as a
response:
unexpected EOF while liiking for matching `''
syntax error: unexpected end of file

If I try no \ escaping, I get a syntax error near "/"

Also tried do use double ', but to no avail.

I even tried $/usr/...$, but didn't make it.

How can this be solved?


Luis

[Database Administrator]

On 2007-09-05, luislupe@gmail.com wrote:
> Hi,
>
> I want to automate a task to copy values to a table.
>
> The shell script is this (simple form):
> psql -U postgres -d sedait_db -c '
> COPY dp_calc_temp
> (
> ano
> )
> FROM \'/usr/local/pgsql/home/dw/iva_dp/calculados/
> dp_calculados_juntos.csv\' WITH CSV ; '
>
> The problem is that I have to use ' to delimit the entire psql
> command, but ' is also required to for the file reference.
>
> I tried to escape ' with \', as in the example, but I get as a
> response:
> unexpected EOF while liiking for matching `''
> syntax error: unexpected end of file
>
> If I try no \ escaping, I get a syntax error near "/"
>
> Also tried do use double ', but to no avail.
>
> I even tried $/usr/...$, but didn't make it.
>
> How can this be solved?
>
>
> Luis
>
Hi,

write your script in file and then psql .... -f file

rgds,
d.

[Database Administrator]

On Sep 5, 3:56 pm, sircco wrote:
> On 2007-09-05, luisl...@gmail.com wrote:
>
> > Hi,
>
> > I want to automate a task to copy values to a table.
>
> > The shell script is this (simple form):
> > psql -U postgres -d sedait_db -c '
> > COPY dp_calc_temp
> > (
> > ano
> > )
> > FROM \'/usr/local/pgsql/home/dw/iva_dp/calculados/
> > dp_calculados_juntos.csv\' WITH CSV ; '
>
> > The problem is that I have to use ' to delimit the entire psql
> > command, but ' is also required to for the file reference.
>
> > I tried to escape ' with \', as in the example, but I get as a
> > response:
> > unexpected EOF while liiking for matching `''
> > syntax error: unexpected end of file
>
> > If I try no \ escaping, I get a syntax error near "/"
>
> > Also tried do use double ', but to no avail.
>
> > I even tried $/usr/...$, but didn't make it.
>
> > How can this be solved?
>
> > Luis
>
> Hi,
>
> write your script in file and then psql .... -f file
>
> rgds,
> d.

Thank you! You saved my day, although this way there is some
flexibility lost, for example in assigning variable names to files.

Luis

[Database Administrator]

luislupe@gmail.com wrote:
> I want to automate a task to copy values to a table.
>
> The shell script is this (simple form):
> psql -U postgres -d sedait_db -c '
> COPY dp_calc_temp
> (
> ano
> )
> FROM \'/usr/local/pgsql/home/dw/iva_dp/calculados/
> dp_calculados_juntos.csv\' WITH CSV ; '
>
> The problem is that I have to use ' to delimit the entire psql
> command, but ' is also required to for the file reference.
>
> I tried to escape ' with \', as in the example, but I get as a
> response:
> unexpected EOF while liiking for matching `''
> syntax error: unexpected end of file
>
> If I try no \ escaping, I get a syntax error near "/"
>
> Also tried do use double ', but to no avail.
>
> I even tried $/usr/...$, but didn't make it.

What keeps you from using double quotes?

psql ... -c "COPY .... FROM 'filename'"

Yours,
Laurenz Albe

[Database Administrator]

luislupe@gmail.com wrote:

> I tried to escape ' with \', as in the example, but I get as a
> response:
> unexpected EOF while liiking for matching `''
> syntax error: unexpected end of file

You can only escape and use shell variables between double quotes, not
between single quotes. Best to use double quotes anyway,

[nutcrackers]

I was trying to use this on a shell script on ubuntu. my objective is to read all CSV file on a certain directory and load its contents on the specified table on the "Copy from " statement.

this raise an error Syntax error at or near ":"

psql -c "copy sometable from om _filename WITH HEADER CSV;" -e -t -v p_filename=$CSV_FILE -h xx.xx.xx.xx -p 54444 -U someuser dbname=somedb

can anyone suggestion why "_filename" cannot be use here

Or do i use it right? do we have alternate solutions here"