Example on using array for INSERT operation with PostgreSQL

[wplj]

Hello,
I'm trying to bulk update a table with some data I retrieved from a CSV file, so it's represented as PowerShell array of [PSCustomObject]s ($Data). I'd like to see an example of how to do it properly (from performance perspective).
Currently if I call it straightforward like this:

$Query = "INSERT INTO @table VALUES @data ON CONFLICT DO UPDATE;"
Invoke-SqlUpdate -Query $Query -Parameters @{table = $Params.TableName; data = $Data}

I get an error:

Invoke-SqlUpdate: Exception calling "ExecuteNonQuery" with "0" argument(s): "The CLR array type System.Object[] isn't supported by Npgsql or your PostgreSQL. If you wish to map it to an PostgreSQL composite type array you need to register it before usage, please refer to the documentation."

How to do this mapping? Or is there another way?

[mithrandyr]

@wplj -- that will not work. As far as I am aware, SQL does not support passing in the table name as a parameter.

Moreover, SimplySql does not have any support for treating a CSV file or an array of PSObjects as a source for its Invoke-SqlBulkCopy.

However, you can still get good performance through the use of transactions.

$Query = "INSERT INTO theTable (col1, col2, col3) VALUES (@c1, @c2, @c3)"
Open-PostGreConnection # fill in connection details

Start-SqlTransaction
try {
  foreach($d in $data) {
    # setting the result to null is the equivalent of using '| Out-Null' but more performant
    $null = Invoke-SqlUpdate -Query $Query -Parameters @{c1=$d.c1;c2=$d.c2;$c=$d.c3}
  }
  Complete-SqlTransaction
}
catch {
  Undo-SqlTransaction # rollback on error
}
Close-SqlConnection

[wplj]

Thanks for the quick reply.
Should I explicitly define all the column names (object properties) and their values? I have 24, it'd be quite a hassle...

BTW, Out-Null performance is fine in PS 7+, and it's more idiomatic.

[mithrandyr]

@wplj -- for valid SQL you will have to (as far as I am aware). FYI.. SimplySql (the current version) is untested/unsupported on PS7... so mileage may vary. As for the columnNames... if you have an object, you could dynamically generate them.

[wplj]

yes, that's how I did it - some transformations and dynamic query generation with format operator.
btw, in PS7 it works just fine, at least for my tasks.
Thanks again.