Reading and writing from a Postgres database in Powershell for Linux-CodePudding

I'm on an Ubuntu server (20.04 LTS) using Powershell for Linux (7.2.1) and am trying to figure out how to read and write from a Postgres 13 database also installed on the same Ubuntu server.

Can anyone post an example script showing how this is done? Most scripts assume Windows is involved and it is not. I'm converting a Windows Powershell script that used to access MS SQL Server to a new home on Ubuntu with Postgres. I hope to reuse the same Powershell logic instead of rewriting the script in Bash.

Thanks in advance!

CodePudding user response：

Here's an ODBC example I use on Rhel 7, pwsh v7.2.1, without any extra modules except for the postgresql-odbc driver:

$conn = New-Object System.Data.Odbc.OdbcConnection
$conn.ConnectionString= "Driver={PostgreSQL};Server=SERVERNAME;Port=1234;Database=DBNAME;Uid=USERNAME;Pwd=PASS;"
$conn.open()

$query = 'SELECT * FROM pg_catalog.pg_tables'
$command = New-Object System.Data.Odbc.OdbcCommand($query,$conn)

# For commands/queries that return rows
$reader = $command.ExecuteReader()  

  # Optionally, convert reader output to DataTable object for viewing
  # I disable constraints on my result table for this query. Otherwise returns "Failed to enable constraints"
  $DataSet = New-Object Data.Dataset -Property @{ EnforceConstraints = $false }  
  $DataTable = New-Object Data.DataTable
  $DataSet.Tables.Add($DataTable)
  $DataTable.Load($reader,[Data.LoadOption]::OverwriteChanges)

# For Write/Inserts:
$command.ExecuteNonQuery()  ## for commands that don't return data e.g. Update

# Cleanup:
$command.Dispose()  ## OdbcCommand objects cannot be re-used, so it helps to close them after execution
$conn.Close()  ## Connections can be reused, but close it before ending your script.


# displaying results
$DataTable|ft

schemaname tablename        tableowner tablespace hasindexes hasrules hastriggers rowsecurity
---------- ---------        ---------- ---------- ---------- -------- ----------- -----------
public     sys_db_changelog admin01               1          0        0           0
public     sys_server       admin01               1          0        0           0
public     sys_config       admin01               1          0        0           0