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)

'code'$conn = New-Object -comobject ADODB.Connection # use existing 64 bit ODBC System DSN that we set up manually $conn.Open("PostgreSQL35W") $recordset = $conn.Execute("SELECT * FROM JobHistory") 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(); Exit — ken
– ken, Commented Apr 29, 2015 at 18:49

dog · Accepted Answer · 2020-08-10 02:01:40Z

6

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}}

edited Aug 10, 2020 at 2:01

answered Aug 9, 2020 at 9:13

dog

1,59716 silver badges10 bronze badges

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

3 Comments

Jeremy Thompson Over a year ago

I tried this today and I'm getting the error psql.exe: illegal option -- csv any idea?

Jeremy Thompson Over a year ago

I worked it out $data = "select * from feeds" | .\psql $connstr | ConvertFrom-Csv

Daniel Lidström Over a year ago

.\psql "-A" | ConvertFrom-Csv -Delimiter '|'

Joe R. · Accepted Answer · 2020-05-26 16:34:24Z

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();

Daniel Lidström · Accepted Answer · 2022-05-12 08:46:28Z

2

This is slightly adapted from another answer and it worked for me.

$dburl="postgresql://postgres:secret_pwd@database-host:5432/dbname"
$psqlPath = 'C:\Program Files\PostgreSQL\11\bin\psql.exe'

function Query {
  param($Sql)
  Write-Host $Sql
  $rows = $Sql `
  | &$psqlPath "-A" $dburl | ConvertFrom-Csv -Delimiter '|'
  $result = @($rows | Select-Object -SkipLast 1)
  Write-Host "-> " (ConvertTo-Json $result)
  $result
}

$rows = Query "select ... from ..."

answered May 12, 2022 at 8:46

Daniel Lidström

10.4k2 gold badges30 silver badges35 bronze badges

Comments

ken · Accepted Answer · 2015-04-29 18:53:36Z

1

I ended up figuring it out - here's what I did

$conn = New-Object -comobject ADODB.Connection

# use existing 64 bit ODBC System DSN that we set up manually
$conn.Open("PostgreSQL35W")

$recordset = $conn.Execute("SELECT * FROM JobHistory")
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();
Exit

answered Apr 29, 2015 at 18:53

ken

3092 gold badges3 silver badges10 bronze badges

1 Comment

ken Over a year ago

to set up the ODBC connection: Install the postgreSQL ODBC driver from odbc.postgresql.org Create the ODBC connection There are two copies of the ODBC manager in windows 7 You have to use the 64 bit version from C:\Windows\SysWOW64 Do NOT use the one in system32 (it is the default on the Administrative Tools menu - don't use it)

ListenSoftware Louise Ai Agent · Accepted Answer · 2020-12-21 19:02:58Z

1

use the dot notation. You don't need to split the data.

$list = New-Object Collections.Generic.List[OnlineCourse]

foreach($element in $results)
{
    $tempObj= New-Object OnlineCourse($element.id,$element.courseName,$element.completedRatio,$element.completedRatio,$element.lastActivity, $element.provider)
    $list.add($tempObj)
}

answered Dec 21, 2020 at 19:02

ListenSoftware Louise Ai Agent

4,3432 gold badges31 silver badges39 bronze badges

Comments

Jeremy Thompson · Accepted Answer · 2021-12-22 03:44:01Z

I have a slightly different approach to @dog, I couldn't get the --csv to work, so I resorted to tuple only rows returned, then parse them into a List of Classes (which happen to be called OnlineCourses):

class OnlineCourse
{
    [int]$id
    [string]$email
    [string]$courseName
    [int]$completedRatio
    [datetime]$lastActivity
    [String]$provider
    OnlineCourse([int]$id,
        [string]$email,
        [string]$courseName,
        [int]$completedPerc,
        [datetime]$lastActivity,
        [String]$provider) {
            $this.id = $id
            $this.email = $email.Trim()
            $this.courseName = $courseName.Trim()
            $this.completedRatio = $completedPerc
            $this.lastActivity = $lastActivity
            $this.provider = $provider.Trim()
    }
}

$connstr="postgresql://exusername:expw@exhostname:5432/postgres"
$data = "select * from onlinecourses" | .\psql -t $connstr

$list = New-Object Collections.Generic.List[OnlineCourse]

foreach ($field in $data) { 
    $id, $email, $courseName,  $completedratio, $lastactivity, $provider = $field.split('|')
    $course = [OnlineCourse]::new($id, $email, $courseName, $completedratio, $lastactivity, $provider)
    $list.Add($course)
}

Collectives™ on Stack Overflow

Retrieve data from PostgreSQL using Powershell

6 Answers 6

3 Comments

Comments

Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

3 Comments

Comments

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related