Thread: copy command - something not found

copy command - something not found

From
Susan Hurst
Date:
I am trying to use the copy command from a csv files using a UNIX shell 
script but something is 'not found'...I can't figure out what is 'not 
found'. Below is my command from the shell script, the executed command, 
the content of the csv file and the output from trying to execute the 
command.

Clearly, the file is being read but I can't figure out what is not 
found. BTW, the column names in the stg.bar_active table match the names 
and order in the first row of the csv file. What should I be looking 
for?

Thanks for your help!

Sue

##-- shell script command
psql -d ${DBNAME} -U ${DBNAME} -h ${HOSTNAME} -c < ${CSVPATH}copycmd.z

##-- executed command
"copy stg.bar_active from 
'/home/dbzone/stp/rfc_db/RFC-00001/RFC-00001_copy_bar_active.csv' 
delimiter ',' CSV HEADER;"

##-- content of .csv file
schema_name,table_name,table_alias            ...(this is the header 
row)
chief,source_systems,ssys
chief,lookup_categories,lcat
chief,lookup_data,ldat

##-- output from terminal window
/home/dbzone/stp/rfc_db/RFC-00001/RFC-00001_copy_bar_active.csv: 
schema_name,table_name,table_alias: not found
/home/dbzone/stp/rfc_db/RFC-00001/RFC-00001_copy_bar_active.csv: 
chief,source_systems,ssys: not found
/home/dbzone/stp/rfc_db/RFC-00001/RFC-00001_copy_bar_active.csv: 
chief,lookup_categories,lcat: not found
/home/dbzone/stp/rfc_db/RFC-00001/RFC-00001_copy_bar_active.csv: 
chief,lookup_data,ldat: not found

##-- select version();
PostgreSQL 11.9 on amd64-portbld-freebsd12.1, compiled by FreeBSD clang 
version 8.0.1 (tags/RELEASE_801/final 366581) (based on LLVM 8.0.1), 
64-bit

-- 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Susan E Hurst
Principal Consultant
Brookhurst Data LLC
Email: susan.hurst@brookhurstdata.com
Mobile: 314-486-3261



Re: copy command - something not found

From
John McKown
Date:
Can't really tell. You might want to post the output of the "printenv" command to show us what the shell variables  you are using have in them. Oh, does ${CSVPATH} end in a slash? If it is something like "~/mycsvs" then ${CSVPATH}copycmd.z will expand to "~/mycsvscopycmd.z". Most  "PATH" environment variables  don't end in a /, perhaps you need "${CSVPATH}/copycmd.z" ? Also, as an aside. most UNIX files which end in .z are compressed, IIRC.  Make sure the contents of the file  are plain text.

On Tue, Dec 29, 2020 at 1:12 PM Susan Hurst <susan.hurst@brookhurstdata.com> wrote:
I am trying to use the copy command from a csv files using a UNIX shell
script but something is 'not found'...I can't figure out what is 'not
found'. Below is my command from the shell script, the executed command,
the content of the csv file and the output from trying to execute the
command.

Clearly, the file is being read but I can't figure out what is not
found. BTW, the column names in the stg.bar_active table match the names
and order in the first row of the csv file. What should I be looking
for?

Thanks for your help!

Sue

##-- shell script command
psql -d ${DBNAME} -U ${DBNAME} -h ${HOSTNAME} -c < ${CSVPATH}copycmd.z

##-- executed command
"copy stg.bar_active from
'/home/dbzone/stp/rfc_db/RFC-00001/RFC-00001_copy_bar_active.csv'
delimiter ',' CSV HEADER;"

##-- content of .csv file
schema_name,table_name,table_alias            ...(this is the header
row)
chief,source_systems,ssys
chief,lookup_categories,lcat
chief,lookup_data,ldat

##-- output from terminal window
/home/dbzone/stp/rfc_db/RFC-00001/RFC-00001_copy_bar_active.csv:
schema_name,table_name,table_alias: not found
/home/dbzone/stp/rfc_db/RFC-00001/RFC-00001_copy_bar_active.csv:
chief,source_systems,ssys: not found
/home/dbzone/stp/rfc_db/RFC-00001/RFC-00001_copy_bar_active.csv:
chief,lookup_categories,lcat: not found
/home/dbzone/stp/rfc_db/RFC-00001/RFC-00001_copy_bar_active.csv:
chief,lookup_data,ldat: not found

##-- select version();
PostgreSQL 11.9 on amd64-portbld-freebsd12.1, compiled by FreeBSD clang
version 8.0.1 (tags/RELEASE_801/final 366581) (based on LLVM 8.0.1),
64-bit

--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Susan E Hurst
Principal Consultant
Brookhurst Data LLC
Email: susan.hurst@brookhurstdata.com
Mobile: 314-486-3261


Re: copy command - something not found

From
"David G. Johnston"
Date:

On Tuesday, December 29, 2020, Susan Hurst <susan.hurst@brookhurstdata.com> wrote:

##-- shell script command
psql -c < ${CSVPATH}copycmd.z

Given the meaning of “-c” what are you expecting that to do?

David J.

Re: copy command - something not found

From
Tom Lane
Date:
Susan Hurst <susan.hurst@brookhurstdata.com> writes:
> ##-- output from terminal window
> /home/dbzone/stp/rfc_db/RFC-00001/RFC-00001_copy_bar_active.csv: 
> schema_name,table_name,table_alias: not found
> /home/dbzone/stp/rfc_db/RFC-00001/RFC-00001_copy_bar_active.csv: 
> chief,source_systems,ssys: not found
> /home/dbzone/stp/rfc_db/RFC-00001/RFC-00001_copy_bar_active.csv: 
> chief,lookup_categories,lcat: not found
> /home/dbzone/stp/rfc_db/RFC-00001/RFC-00001_copy_bar_active.csv: 
> chief,lookup_data,ldat: not found

I'd say you're feeding the contents of the csv file to the Unix shell,
not to psql.  It's not real clear how that would happen given what you
show as your initial shell command, but maybe ${CSVPATH} contains
something odd?  Also, as David noted, "-c" without any argument
certainly isn't right.

            regards, tom lane



Re: copy command - something not found

From
Susan Hurst
Date:

Thank you for the reminders, John.  I changed my .z file extension to .tmp and removed the trailing / from my pre-defined directory path.

I'm still getting the same results as before though after changing the .z file extension to .tmp.

The ${DBNAME} and ${HOSTNAME} variables are input at run time. Here is usage format:
### Usage:                copy_data.sh rfc_name db_name [db_host]
Note: [db_host] (hostname) is optional with default localhost, which is what I'm using in this particular case.

My actual input at execution time was this:   ./install_db.sh RFC-00001 stp
This script calls the copy_data.sh script.

${CSVPATH} is:  /home/dbzone/stp/rfc_db/RFC-00001   (after removing the trailing /)

---
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Susan E Hurst Principal Consultant Brookhurst Data LLC Email: susan.hurst@brookhurstdata.com Mobile: 314-486-3261

On 2020-12-29 13:20, John McKown wrote:

Can't really tell. You might want to post the output of the "printenv" command to show us what the shell variables  you are using have in them. Oh, does ${CSVPATH} end in a slash? If it is something like "~/mycsvs" then ${CSVPATH}copycmd.z will expand to "~/mycsvscopycmd.z". Most  "PATH" environment variables  don't end in a /, perhaps you need "${CSVPATH}/copycmd.z" ? Also, as an aside. most UNIX files which end in .z are compressed, IIRC.  Make sure the contents of the file  are plain text.

On Tue, Dec 29, 2020 at 1:12 PM Susan Hurst <susan.hurst@brookhurstdata.com> wrote:
I am trying to use the copy command from a csv files using a UNIX shell
script but something is 'not found'...I can't figure out what is 'not
found'. Below is my command from the shell script, the executed command,
the content of the csv file and the output from trying to execute the
command.

Clearly, the file is being read but I can't figure out what is not
found. BTW, the column names in the stg.bar_active table match the names
and order in the first row of the csv file. What should I be looking
for?

Thanks for your help!

Sue

##-- shell script command
psql -d ${DBNAME} -U ${DBNAME} -h ${HOSTNAME} -c < ${CSVPATH}copycmd.z

##-- executed command
"copy stg.bar_active from
'/home/dbzone/stp/rfc_db/RFC-00001/RFC-00001_copy_bar_active.csv'
delimiter ',' CSV HEADER;"

##-- content of .csv file
schema_name,table_name,table_alias            ...(this is the header
row)
chief,source_systems,ssys
chief,lookup_categories,lcat
chief,lookup_data,ldat

##-- output from terminal window
/home/dbzone/stp/rfc_db/RFC-00001/RFC-00001_copy_bar_active.csv:
schema_name,table_name,table_alias: not found
/home/dbzone/stp/rfc_db/RFC-00001/RFC-00001_copy_bar_active.csv:
chief,source_systems,ssys: not found
/home/dbzone/stp/rfc_db/RFC-00001/RFC-00001_copy_bar_active.csv:
chief,lookup_categories,lcat: not found
/home/dbzone/stp/rfc_db/RFC-00001/RFC-00001_copy_bar_active.csv:
chief,lookup_data,ldat: not found

##-- select version();
PostgreSQL 11.9 on amd64-portbld-freebsd12.1, compiled by FreeBSD clang
version 8.0.1 (tags/RELEASE_801/final 366581) (based on LLVM 8.0.1),
64-bit

--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Susan E Hurst
Principal Consultant
Brookhurst Data LLC
Email: susan.hurst@brookhurstdata.com
Mobile: 314-486-3261


Re: copy command - something not found

From
Susan Hurst
Date:

Actually, the -c was in an example of a copy command that I found while working at my last job. I tried executing the command without the -c and got the same results as before, so I suppose I really don't know what it means.

Can you enlighten me?

 

---
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Susan E Hurst Principal Consultant Brookhurst Data LLC Email: susan.hurst@brookhurstdata.com Mobile: 314-486-3261

On 2020-12-29 13:23, David G. Johnston wrote:


On Tuesday, December 29, 2020, Susan Hurst <susan.hurst@brookhurstdata.com> wrote:

##-- shell script command
psql -c < ${CSVPATH}copycmd.z
 
Given the meaning of “-c” what are you expecting that to do?
 
David J.
 

Re: copy command - something not found

From
"David G. Johnston"
Date:
On Tue, Dec 29, 2020 at 1:01 PM Susan Hurst <susan.hurst@brookhurstdata.com> wrote:

Actually, the -c was in an example of a copy command that I found while working at my last job. I tried executing the command without the -c and got the same results as before, so I suppose I really don't know what it means.

Can you enlighten me

Read the fine manual before running stuff that you don't understand.


Then, provide a self-contained script that demonstrates the problem because at this point I am either unable to follow or untrusting of the written description of the problem.

Or consider using less indirection until you get something that is working and then add your layers incrementally testing as you go along.

David J.

Re: copy command - something not found

From
Igor Korot
Date:
Hi
You could try to do "set +x" before running the script...

Thank you.


On Tue, Dec 29, 2020, 2:23 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Tue, Dec 29, 2020 at 1:01 PM Susan Hurst <susan.hurst@brookhurstdata.com> wrote:

Actually, the -c was in an example of a copy command that I found while working at my last job. I tried executing the command without the -c and got the same results as before, so I suppose I really don't know what it means.

Can you enlighten me

Read the fine manual before running stuff that you don't understand.


Then, provide a self-contained script that demonstrates the problem because at this point I am either unable to follow or untrusting of the written description of the problem.

Or consider using less indirection until you get something that is working and then add your layers incrementally testing as you go along.

David J.

Re: copy command - something not found

From
Christophe Pettus
Date:

> On Dec 29, 2020, at 11:12, Susan Hurst <susan.hurst@brookhurstdata.com> wrote:
>
> ##-- shell script command
> psql -d ${DBNAME} -U ${DBNAME} -h ${HOSTNAME} -c < ${CSVPATH}copycmd.z

The -c argument there specifies a command to run, so it needs an argument of some kind.  It looks like ultimately the
.csvfile gets handed to the shell to execute, which of course doesn't work very well. 

The file ${CSVPATH}copycmd.z contains the COPY command to run, yes?

The -i argument specifies a file that contains a command to run, so you might give this a go:

    psql -d ${DBNAME} -U ${DBNAME} -h ${HOSTNAME} -i "${CSVPATH}copycmd.z"
--
-- Christophe Pettus
   xof@thebuild.com




Re: copy command - something not found

From
Adrian Klaver
Date:
On 12/29/20 12:01 PM, Susan Hurst wrote:
> Actually, the -c was in an example of a copy command that I found while 
> working at my last job. I tried executing the command without the -c and 
> got the same results as before, so I suppose I really don't know what it 
> means.
> 
> Can you enlighten me?

-c is for a command string and if I am following < 
${CSVPATH}copycmd.z(tmp) is a redirect of a file.

It will help to know what is actually in the file?

> 
> ---
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Susan E Hurst
> Principal Consultant
> Brookhurst Data LLC
> Email: susan.hurst@brookhurstdata.com
> Mobile: 314-486-3261
> 
> On 2020-12-29 13:23, David G. Johnston wrote:
> 
>>
>> On Tuesday, December 29, 2020, Susan Hurst 
>> <susan.hurst@brookhurstdata.com 
>> <mailto:susan.hurst@brookhurstdata.com>> wrote:
>>
>>
>>     ##-- shell script command
>>     psql -c < ${CSVPATH}copycmd.z
>>
>> Given the meaning of “-c” what are you expecting that to do?
>> David J.


-- 
Adrian Klaver
adrian.klaver@aklaver.com



Re: copy command - something not found

From
Susan Hurst
Date:
Tom...I think you are right about feeding the contents of the csv file 
to the shell instead of psql. After drilling down a bit more into my 
script, I now think I have a UNIX shell script problem rather than a 
psql problem.

I do appreciate everyone's input as it has been most helpful in my 
efforts to figure out what is not the problem.

Thanks for your help!

Sue
---
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Susan E Hurst
Principal Consultant
Brookhurst Data LLC
Email: susan.hurst@brookhurstdata.com
Mobile: 314-486-3261

On 2020-12-29 13:55, Tom Lane wrote:
> Susan Hurst <susan.hurst@brookhurstdata.com> writes:
>> ##-- output from terminal window
>> /home/dbzone/stp/rfc_db/RFC-00001/RFC-00001_copy_bar_active.csv:
>> schema_name,table_name,table_alias: not found
>> /home/dbzone/stp/rfc_db/RFC-00001/RFC-00001_copy_bar_active.csv:
>> chief,source_systems,ssys: not found
>> /home/dbzone/stp/rfc_db/RFC-00001/RFC-00001_copy_bar_active.csv:
>> chief,lookup_categories,lcat: not found
>> /home/dbzone/stp/rfc_db/RFC-00001/RFC-00001_copy_bar_active.csv:
>> chief,lookup_data,ldat: not found
> 
> I'd say you're feeding the contents of the csv file to the Unix shell,
> not to psql.  It's not real clear how that would happen given what you
> show as your initial shell command, but maybe ${CSVPATH} contains
> something odd?  Also, as David noted, "-c" without any argument
> certainly isn't right.
> 
>             regards, tom lane