Thread: copy command - something not found
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
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
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.
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
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
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.zGiven the meaning of “-c” what are you expecting that to do?David J.
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.
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.
> 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
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
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