question

UncleCisco avatar image
0 Votes"
UncleCisco asked NavtejSaini-MSFT commented

PostgreSQL Bulk Copy with PowerShell

Hello folks! I sure hope you're having a kick ass weekend!
This is my first of hopefully many posts here and i have to admit it was a bit of a click bait.
There is no Bulk Copy on PostgreSQL that i know and like any good guy who's just starting to learn something i struggled to find anything on the web.
The only effective solution was a PS Module, but it has a paid license.
So i started playing with the PostgreSQL ODBC driver and came up with a function that accepts a PSCustomObject and copies it to the database using the COPY statement.
There are two drawbacks and I'll be more than glad to hear your opinion on it:

  • The function exports the PSCustomObject as a CSV file and then uses the COPY statement to up this info.

  • The PSCustomObject needs to be alphabetically ordered, cause i use Get-Member cmdlet to get the NoteProperties from the object.

The code can be found bellow and on the PS Gallery:
https://www.powershellgallery.com/packages/Write-PsqlDataTable/1.0.1
This script is not mine, is ours, so be my guest to modify it as it may fit your needs.
Cheers and Party on!

 Function Write-PsqlDataTable
 {
    
     [CmdletBinding()] 
     param(
    
         [Parameter  (Position = 0, Mandatory = $true)]
         [string]    $ServerInstance,
    
         [Parameter  (Mandatory = $false)]
         [int]       $Port=5432,
    
         [Parameter  (Position = 1, Mandatory = $true)]
         [string]    $Database,
    
         [Parameter  (Position = 2, Mandatory = $true)]
         [string]    $TableName,
    
         # The PSCustomObject NoteProperties must be ordered! Otherwise PgSQL COPY will fail!
         [Parameter  (Position = 3, Mandatory = $true, HelpMessage = 'The PSCustomObject NoteProperties must be ordered! Otherwise PgSQL COPY will fail!')]
         [PSCustomObject] $Data,
                        
         [Parameter  (Position = 4, Mandatory = $false)] 
         [string]    $Username,
    
         [Parameter  (Position = 5, Mandatory = $false)]
         [Security.SecureString] $Password,
    
         [Parameter  (Mandatory = $false)]
         [string]    $Docker
    
     ) 
         
        
     $DBConn = New-Object System.Data.Odbc.OdbcConnection
     $BTSR = [System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($Password)
     $PTP = [System.Runtime.InteropServices.Marshal]::PtrToStringAuto($BTSR)
     if ($Username) {
         $DBConnectionString = "Driver={PostgreSQL UNICODE(x64)};Server=$ServerInstance;Port=$Port;Database=$Database;Uid=$Username;Pwd=$PTP;"
     } 
     else {
         $DBConnectionString = "Driver={PostgreSQL UNICODE(x64)};Server=$ServerInstance;Port=$Port;Database=$Database;"
     } 
     
     $DBConn.ConnectionString = $DBConnectionString
             
     try 
     {
         $Columns = ($Data | Get-Member | Where-Object {$_.MemberType -eq 'NoteProperty'}).Name
         $Columns = [System.String]::Join(',',$Columns)
         $Data | Export-Csv $Env:TEMP\TempPsAd.csv -Delimiter ',' -NoTypeInformation
         if ($Docker) {
             docker cp $Env:TEMP\TempPsAd.csv ${Docker}:/media/TempPsAd.csv
             $DBConn.Open()
             $DBCmd = $DBConn.CreateCommand()
             $DBCmd.CommandText = @"
                 COPY $TableName ($Columns)
                 FROM '/media/TempPsAd.csv'
                 DELIMITER ','
                 CSV HEADER
 "@
             $DBCmd.ExecuteReader()
             $DBConn.Close()
             docker exec $Docker rm -rf /media/TempPsAd.csv
             Remove-Item $Env:TEMP\TempPsAd.csv -Force
         }
         else {
             $DBConn.Open()
             $DBCmd = $DBConn.CreateCommand()
             $DBCmd.CommandText = @"
                 COPY $TableName ($Columns)
                 FROM '$Env:TEMP\TempPsAd.csv'
                 DELIMITER ','
                 CSV HEADER
 "@
             $DBCmd.ExecuteReader()
             $DBConn.Close()
             Remove-Item $Env:TEMP\TempPsAd.csv -Force
         }
            
     } 
     catch 
     { 
         Write-Error "$($_.Exception.Message)"
         continue 
     } 
 }
azure-database-postgresql
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@UncleCisco

Please confirm if this issue is related to full fledged Postgresql on a Azure VM(IaaS) or Azure service - Azure database for Postgresql(PaaS). This will help to route the question correctly.

Thanks
Navtej S

0 Votes 0 ·

@UncleCisco Please provide this info to take this forward.

Regards
Navtej S

0 Votes 0 ·

0 Answers