CODEWITHSUNDEEP
postgresqlpowershellcopypsql
Locupus
Locupus

Reputation: 29

Need to handle psql errors in powershell

I had a small issue with PowerShell and Postgres PSQL command. I'm loading a .CSV file into a postgres DB using Powershell and \COPY command. Everything works fine, but I identified a case where the PSQL command execute with an error due to .csv not well formatted. Unfortunately, even if I do a try catch of the PSQL command this is not well handled. I'm able to palliate the error but I want to find a way to handle it correctly and being able to stop the process or to reject the file in case of issue.

I need to add a precision here, the error only appear when running the script on a PowerShell window but nothing appear in the Log file I created. It looks like the error is directly link to PSQL \COPY command and is not handled in PowerShell. That's annoying for me as I'm not able to detect a potention issue by only using the Log file.

This is my code:

$Copydatatotable = "\COPY $Load_table_path (account, account_holder, Bank, account_type,......

Write-Host " $Copydatatotable"  

try 
  { 
    psql -h $MyServer -U $MyUid -d $MyDB  -p $MyPort -c $Copydatatotable 
  }        
catch
  {
    Write-Host "  -->  IN CATCH"                                                    
    $Copydatatotable   = "INSERT INTO $Load_process_log_table_not_loaded_path (file_path,.....
    throw $_
    $Try_catch_value = "Y"
  }

This is the error:

#                                                                               #
# STEP 5 - Load file into temporary table                                       #
#                                                                               #
#                                                                               #

 \COPY "temp_load_prod".isabel_transaction (account, account_holder, Bank, account_type,  ......

ERROR:  missing data for column "account_holder"
CONTEXT:  COPY isabel_transaction, line 1284: ""

Upvotes: 2

Views: 377

Answers (1)

Locupus
Locupus

Reputation: 29

After long moment trying to find a way to correctly handle PSQL error on PowerShell while using \copy command, I finally bypass the problem. As my problem was to ensure that all rows of my .CSV file was correctly inserted into the database table, I just count the number of rows of my .CSV file and do a count of rows inserted into my table, then comparing both to ensure all is ok.

On .CSV side

$nb_lines_in_file = Get-Content $($MyFile.FullName) | Measure-Object -Line $Record_to_load = $calcul_nb_lines_in_file.lines

on Postgres side

$count= "SELECT COUNT(*) FROM $table_path WHERE audit_trail = '$MyAudit_trail'" $cpt= psql -t -h $MyServer -U $MyUid -d $MyDB -p $MyPort -c $count

Upvotes: 1

Related Questions