public inbox for [email protected]help / color / mirror / Atom feed
Re: load fom csv 6+ messages / 2 participants [nested] [flat]
* Re: load fom csv @ 2024-09-16 16:12 Andy Hartman <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: Andy Hartman @ 2024-09-16 16:12 UTC (permalink / raw) To: Francisco Olarte <[email protected]>; +Cc: [email protected] 2024-09-16 12:06:00.968 EDT [4968] ERROR: relation "image_classification_master" does not exist 2024-09-16 12:06:00.968 EDT [4968] STATEMENT: COPY Image_Classification_Master FROM STDIN DELIMITER ',' CSV HEADER; On Mon, Sep 16, 2024 at 11:52 AM Francisco Olarte <[email protected]> wrote: > > > On Mon, 16 Sept 2024 at 17:36, Andy Hartman <[email protected]> > wrote: > >> I'm trying to run this piece of code from Powershell and it just sits >> there and never comes back. There are only 131 records in the csv. >> $connectionString = >> "Host=$pgServer;Database=$pgDatabase;Username=$pgUser;Password=$pgPassword" >> $copyCommand = "\COPY $pgTable FROM '$csvPath' DELIMITER ',' CSV HEADER;" >> psql -h $pgServer -d $pgDatabase -U $pgUser -c $copyCommand >> how can I debug this? >> > > I would start by adding -a and -e after "psql". > > IIRC Powershell is windows, and in windows shell do not pass command words > preparsed as in *ix to the executable, but a single command line with the > executable must parse. Given the amount of quoting, -a and -e will let you > see the commands are properly sent, and if it is trying to read something > what it is. > > I will also try to substitute the -c with a pipe. If it heals, it is > probably a quoting issue. > > Also, I just caught Ron's message, and psql might be waiting for a > password. > > Francisco Olarte. > > ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: load fom csv @ 2024-09-16 16:17 Adrian Klaver <[email protected]> parent: Andy Hartman <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: Adrian Klaver @ 2024-09-16 16:17 UTC (permalink / raw) To: Andy Hartman <[email protected]>; Francisco Olarte <[email protected]>; +Cc: [email protected] On 9/16/24 09:12, Andy Hartman wrote: > 2024-09-16 12:06:00.968 EDT [4968] ERROR: relation > "image_classification_master" does not exist > 2024-09-16 12:06:00.968 EDT [4968] STATEMENT: COPY > Image_Classification_Master FROM STDIN DELIMITER ',' CSV HEADER; I'm assuming this is from the Postgres log. Best guess is the table name in the database is mixed case and needs to be double quoted in the command to preserve the casing. See: https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS for why. > > On Mon, Sep 16, 2024 at 11:52 AM Francisco Olarte > <[email protected] <mailto:[email protected]>> wrote: > > > > On Mon, 16 Sept 2024 at 17:36, Andy Hartman <[email protected] > <mailto:[email protected]>> wrote: > > I'm trying to run this piece of code from Powershell and it just > sits there and never comes back. There are only 131 records in > the csv. > $connectionString = > "Host=$pgServer;Database=$pgDatabase;Username=$pgUser;Password=$pgPassword" > $copyCommand = "\COPY $pgTable FROM '$csvPath' DELIMITER ',' CSV > HEADER;" > psql -h $pgServer -d $pgDatabase -U $pgUser -c $copyCommand > how can I debug this? > > > I would start by adding -a and -e after "psql". > > IIRC Powershell is windows, and in windows shell do not pass command > words preparsed as in *ix to the executable, but a single command > line with the executable must parse. Given the amount of quoting, -a > and -e will let you see the commands are properly sent, and if it is > trying to read something what it is. > > I will also try to substitute the -c with a pipe. If it heals, it is > probably a quoting issue. > > Also, I just caught Ron's message, and psql might be waiting for a > password. > > Francisco Olarte. > -- Adrian Klaver [email protected] ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: load fom csv @ 2024-09-16 16:46 Andy Hartman <[email protected]> parent: Adrian Klaver <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: Andy Hartman @ 2024-09-16 16:46 UTC (permalink / raw) To: Adrian Klaver <[email protected]>; +Cc: Francisco Olarte <[email protected]>; [email protected] It Looks correct. $pgTable = "image_classification_master" On Mon, Sep 16, 2024 at 12:17 PM Adrian Klaver <[email protected]> wrote: > On 9/16/24 09:12, Andy Hartman wrote: > > 2024-09-16 12:06:00.968 EDT [4968] ERROR: relation > > "image_classification_master" does not exist > > 2024-09-16 12:06:00.968 EDT [4968] STATEMENT: COPY > > Image_Classification_Master FROM STDIN DELIMITER ',' CSV HEADER; > > I'm assuming this is from the Postgres log. > > Best guess is the table name in the database is mixed case and needs to > be double quoted in the command to preserve the casing. > > See: > > > https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS > > for why. > > > > > On Mon, Sep 16, 2024 at 11:52 AM Francisco Olarte > > <[email protected] <mailto:[email protected]>> wrote: > > > > > > > > On Mon, 16 Sept 2024 at 17:36, Andy Hartman <[email protected] > > <mailto:[email protected]>> wrote: > > > > I'm trying to run this piece of code from Powershell and it just > > sits there and never comes back. There are only 131 records in > > the csv. > > $connectionString = > > > "Host=$pgServer;Database=$pgDatabase;Username=$pgUser;Password=$pgPassword" > > $copyCommand = "\COPY $pgTable FROM '$csvPath' DELIMITER ',' CSV > > HEADER;" > > psql -h $pgServer -d $pgDatabase -U $pgUser -c $copyCommand > > how can I debug this? > > > > > > I would start by adding -a and -e after "psql". > > > > IIRC Powershell is windows, and in windows shell do not pass command > > words preparsed as in *ix to the executable, but a single command > > line with the executable must parse. Given the amount of quoting, -a > > and -e will let you see the commands are properly sent, and if it is > > trying to read something what it is. > > > > I will also try to substitute the -c with a pipe. If it heals, it is > > probably a quoting issue. > > > > Also, I just caught Ron's message, and psql might be waiting for a > > password. > > > > Francisco Olarte. > > > > -- > Adrian Klaver > [email protected] > > ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: load fom csv @ 2024-09-16 16:56 Adrian Klaver <[email protected]> parent: Andy Hartman <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: Adrian Klaver @ 2024-09-16 16:56 UTC (permalink / raw) To: Andy Hartman <[email protected]>; +Cc: Francisco Olarte <[email protected]>; [email protected] On 9/16/24 09:46, Andy Hartman wrote: > > It Looks correct. > > $pgTable = "image_classification_master" Connect to the database with psql and look at the table name. I'm betting it is not image_classification_master. Instead some mixed or all upper case version of the name. I don't use PowerShell or Windows for that matter these days so I can't be of much use on the script. I do suspect you will need to some escaping to get the table name properly quoted in the script. To work through this you need to try what I call the crawl/walk/run process. In this case that is: 1) Crawl. Connect using psql and run the \copy in it with hard coded values. 2) Walk. Use psql with the -c argument and supply the command again with hard coded values 3) Run. Then use PowerShell and do the variable substitution. > > > > > On Mon, Sep 16, 2024 at 12:17 PM Adrian Klaver > <[email protected] <mailto:[email protected]>> wrote: > > On 9/16/24 09:12, Andy Hartman wrote: > > 2024-09-16 12:06:00.968 EDT [4968] ERROR: relation > > "image_classification_master" does not exist > > 2024-09-16 12:06:00.968 EDT [4968] STATEMENT: COPY > > Image_Classification_Master FROM STDIN DELIMITER ',' CSV HEADER; > > I'm assuming this is from the Postgres log. > > Best guess is the table name in the database is mixed case and needs to > be double quoted in the command to preserve the casing. > > See: > > https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS <https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS; > > for why. > > > > > On Mon, Sep 16, 2024 at 11:52 AM Francisco Olarte > > <[email protected] <mailto:[email protected]> > <mailto:[email protected] <mailto:[email protected]>>> wrote: > > > > > > > > On Mon, 16 Sept 2024 at 17:36, Andy Hartman > <[email protected] <mailto:[email protected]> > > <mailto:[email protected] > <mailto:[email protected]>>> wrote: > > > > I'm trying to run this piece of code from Powershell and > it just > > sits there and never comes back. There are only 131 > records in > > the csv. > > $connectionString = > > > "Host=$pgServer;Database=$pgDatabase;Username=$pgUser;Password=$pgPassword" > > $copyCommand = "\COPY $pgTable FROM '$csvPath' DELIMITER > ',' CSV > > HEADER;" > > psql -h $pgServer -d $pgDatabase -U $pgUser -c $copyCommand > > how can I debug this? > > > > > > I would start by adding -a and -e after "psql". > > > > IIRC Powershell is windows, and in windows shell do not pass > command > > words preparsed as in *ix to the executable, but a single command > > line with the executable must parse. Given the amount of > quoting, -a > > and -e will let you see the commands are properly sent, and > if it is > > trying to read something what it is. > > > > I will also try to substitute the -c with a pipe. If it > heals, it is > > probably a quoting issue. > > > > Also, I just caught Ron's message, and psql might be waiting > for a > > password. > > > > Francisco Olarte. > > > > -- > Adrian Klaver > [email protected] <mailto:[email protected]> > -- Adrian Klaver [email protected] ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: load fom csv @ 2024-09-16 17:00 Andy Hartman <[email protected]> parent: Adrian Klaver <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: Andy Hartman @ 2024-09-16 17:00 UTC (permalink / raw) To: Adrian Klaver <[email protected]>; +Cc: Francisco Olarte <[email protected]>; [email protected] in LOG 2024-09-16 12:55:37.295 EDT [428] ERROR: invalid byte sequence for encoding "UTF8": 0x00 2024-09-16 12:55:37.295 EDT [428] CONTEXT: COPY image_classification_master, line 1, column spoolstarttime On Mon, Sep 16, 2024 at 12:56 PM Adrian Klaver <[email protected]> wrote: > On 9/16/24 09:46, Andy Hartman wrote: > > > > It Looks correct. > > > > $pgTable = "image_classification_master" > > Connect to the database with psql and look at the table name. I'm > betting it is not image_classification_master. Instead some mixed or all > upper case version of the name. > > I don't use PowerShell or Windows for that matter these days so I can't > be of much use on the script. I do suspect you will need to some > escaping to get the table name properly quoted in the script. To work > through this you need to try what I call the crawl/walk/run process. In > this case that is: > > 1) Crawl. Connect using psql and run the \copy in it with hard coded > values. > > 2) Walk. Use psql with the -c argument and supply the command again with > hard coded values > > 3) Run. Then use PowerShell and do the variable substitution. > > > > > > > > > > > On Mon, Sep 16, 2024 at 12:17 PM Adrian Klaver > > <[email protected] <mailto:[email protected]>> wrote: > > > > On 9/16/24 09:12, Andy Hartman wrote: > > > 2024-09-16 12:06:00.968 EDT [4968] ERROR: relation > > > "image_classification_master" does not exist > > > 2024-09-16 12:06:00.968 EDT [4968] STATEMENT: COPY > > > Image_Classification_Master FROM STDIN DELIMITER ',' CSV HEADER; > > > > I'm assuming this is from the Postgres log. > > > > Best guess is the table name in the database is mixed case and needs > to > > be double quoted in the command to preserve the casing. > > > > See: > > > > > https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS > < > https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS > > > > > > for why. > > > > > > > > On Mon, Sep 16, 2024 at 11:52 AM Francisco Olarte > > > <[email protected] <mailto:[email protected]> > > <mailto:[email protected] <mailto:[email protected]>>> > wrote: > > > > > > > > > > > > On Mon, 16 Sept 2024 at 17:36, Andy Hartman > > <[email protected] <mailto:[email protected]> > > > <mailto:[email protected] > > <mailto:[email protected]>>> wrote: > > > > > > I'm trying to run this piece of code from Powershell and > > it just > > > sits there and never comes back. There are only 131 > > records in > > > the csv. > > > $connectionString = > > > > > > "Host=$pgServer;Database=$pgDatabase;Username=$pgUser;Password=$pgPassword" > > > $copyCommand = "\COPY $pgTable FROM '$csvPath' DELIMITER > > ',' CSV > > > HEADER;" > > > psql -h $pgServer -d $pgDatabase -U $pgUser -c > $copyCommand > > > how can I debug this? > > > > > > > > > I would start by adding -a and -e after "psql". > > > > > > IIRC Powershell is windows, and in windows shell do not pass > > command > > > words preparsed as in *ix to the executable, but a single > command > > > line with the executable must parse. Given the amount of > > quoting, -a > > > and -e will let you see the commands are properly sent, and > > if it is > > > trying to read something what it is. > > > > > > I will also try to substitute the -c with a pipe. If it > > heals, it is > > > probably a quoting issue. > > > > > > Also, I just caught Ron's message, and psql might be waiting > > for a > > > password. > > > > > > Francisco Olarte. > > > > > > > -- > > Adrian Klaver > > [email protected] <mailto:[email protected]> > > > > -- > Adrian Klaver > [email protected] > > ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: load fom csv @ 2024-09-16 17:10 Adrian Klaver <[email protected]> parent: Andy Hartman <[email protected]> 0 siblings, 0 replies; 6+ messages in thread From: Adrian Klaver @ 2024-09-16 17:10 UTC (permalink / raw) To: Andy Hartman <[email protected]>; +Cc: Francisco Olarte <[email protected]>; [email protected] On 9/16/24 10:00, Andy Hartman wrote: > in LOG > > 2024-09-16 12:55:37.295 EDT [428] ERROR: invalid byte sequence for > encoding "UTF8": 0x00 > 2024-09-16 12:55:37.295 EDT [428] CONTEXT: COPY > image_classification_master, line 1, column spoolstarttime > I'm heading out the door I can't step you through the process, I can point you at: https://www.postgresql.org/docs/current/multibyte.html#MULTIBYTE-AUTOMATIC-CONVERSION Others will be able to answer the specifics. -- Adrian Klaver [email protected] ^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2024-09-16 17:10 UTC | newest] Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-09-16 16:12 Re: load fom csv Andy Hartman <[email protected]> 2024-09-16 16:17 ` Adrian Klaver <[email protected]> 2024-09-16 16:46 ` Andy Hartman <[email protected]> 2024-09-16 16:56 ` Adrian Klaver <[email protected]> 2024-09-16 17:00 ` Andy Hartman <[email protected]> 2024-09-16 17:10 ` Adrian Klaver <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox