Retrieve data from PostgreSQL using Powershell

Question

I have been wrestling with database connection to PostgreSQL from Powershell. I finally am able to connect to and insert into the database. Now I can't figure out how to extract data from a DB select into a variable.

I'm not including my insert for the sake of clarity but will tack it onto this thread later as I know it was super hard to find and may be helpful to someone.

so here's my code:

# use existing 64 bit ODBC System DSN that we set up manually
$DBconn = New-Object -comobject ADODB.Connection
$DBconn.Open("PostgreSQL35W")

$theQuery = "select * from test1"
$theObject = $DBconn.Execute($theQuery) # $theObject is a System.__ComObject
$numRecords = $theObject.RecordCount
write-host "found $numRecords records"  # getting -1
$theObject.MoveFirst()  # throws no error
# $theValue = $theObject.DataMember # throws no error, but gives no result
$theValue = $theObject.Index[1] # throws "Cannot index into a null array" 
write-host($theValue)

Answer 1

Via psql, which comes with postgresql

$dburl="postgresql://exusername:expw@exhostname:5432/postgres"
$data="select * from extable" | psql --csv $dburl | ConvertFrom-Csv

You must have psql in your path or reference it, its within e.g. C:\Program Files\PostgreSQL\12\bin. Should be able to type "psql" and see output within powershell.

As a warning, expect strings. E.g $data[0].age.GetType() would be string, despite being stored in the database as an integer. You can immediately cast it, cast it later, or hope powershell infers type correctly.

If you want to add back in type information can do e.g.:

$data = $data | %{[pscustomobject]@{name=$_.name;age=[int]$_.age}}

Answer 2

try this
replace "#database#" with your database name in $cnString
replace "#server_ip#" with your server ip address in $cnString
replace "#user#" with a valid user in $cnString and $user
replace "#pass#" with a valid pass in $pass
replace "#table#" with a valid table name of your db
replace 5432 with your db port

$cnString = "DRIVER={PostgreSQL Unicode(x64)};DATABASE=#database#;SERVER=#server_ip#;PORT=5432;UID=#user#;"
$user="#user#"
$pass="#pass#"

$conn = New-Object -comobject ADODB.Connection
$conn.Open($cnString,$user,$pass)

$recordset = $conn.Execute("SELECT * FROM #table# limit 1;")
while ($recordset.EOF -ne $True) 
{  
    foreach ($field in $recordset.Fields)
    {    
        '{0,30} = {1,-30}' -f # this line sets up a nice pretty field format, but you don't really need it
        $field.name, $field.value  
    }
   ''  # this line adds a line between records
$recordset.MoveNext()
}

$conn.Close();