Reading and writing from a Postgres database in Powershell for Linux

Question

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!

Cpt.Whale · Accepted Answer · 2022-01-12 16:31:09Z

4

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

answered Jan 12, 2022 at 16:31

Cpt.Whale

5,3911 gold badge15 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3799635 Over a year ago

Thank you and this is exactly what I needed! I had to update the ODBC files in /etc/odbc.ini and /etc/odbcinst.ini (used full path for the psqlodbcw.so Driver location) and then the above script worked great.

Collectives™ on Stack Overflow

Reading and writing from a Postgres database in Powershell for Linux

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related