Connect to remote PostgreSql database using Powershell

Question

I'm trying to connect to a remote PostgreSql database using powershell. This is my first time using powershell so I'm sorry if this is a noob question. This is my Code:

$DBConnectionString = "Driver={PostgreSQL UNICODE}:Server=$MyServer;Port=$MyPort;Database=$MyDB;Uid=$MyUid;Pwd=$MyPass;"
$DBConn = New-Object System.Data.Odbc.OdbcConnection;
$DBConn.ConnectionString = $DBConnectionString;
$DBConn.Open();
$DBCmd = $DBConn.CreateCommand();
$DBCmd.CommandText = "SELECT * FROM mytable;";
$DBCmd.ExecuteReader();
$DBConn.Close();

When I run this I get "Exception Calling "Open" with "0" argument(s): ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified". I've downloaded and installed the pgsqlodbc driver but I'm still getting this error. Does anyone have any ideas how I could fix this? I have searched the internet and I'm really not getting anywhere at this point.

Thanks.

Ansgar Wiechers · Accepted Answer · 2017-08-16 11:40:55Z

9

Consult: https://odbc.postgresql.org/
Download: https://www.postgresql.org/ftp/odbc/versions/msi/

Data sources (ODBC) on Windows: Start → Search → odbc → User DSN → Add/Configure

Example :

$MyServer = "<ip>"
$MyPort  = "5432"
$MyDB = "<database>"
$MyUid = "<user>"
$MyPass = "<pass>"

$DBConnectionString = "Driver={PostgreSQL UNICODE(x64)};Server=$MyServer;Port=$MyPort;Database=$MyDB;Uid=$MyUid;Pwd=$MyPass;"
$DBConn = New-Object System.Data.Odbc.OdbcConnection;
$DBConn.ConnectionString = $DBConnectionString;
$DBConn.Open();
$DBCmd = $DBConn.CreateCommand();
$DBCmd.CommandText = "SELECT * FROM tb_module;";
$DBCmd.ExecuteReader();
$DBConn.Close();

edited Aug 16, 2017 at 11:40

Ansgar Wiechers

202k27 gold badges286 silver badges363 bronze badges

answered Feb 6, 2017 at 13:45

Thiago Back

911 silver badge2 bronze badges

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

1 Comment

sixplus4iszen Over a year ago

This works for me but my result is array which had header FieldCountr and 5 rows with number 3 there. The result is 3 column with 5 rown but how can I assign it to array?

dog · Accepted Answer · 2020-08-10 02:00:53Z

8

You can use psql which comes with postgresql if you happen to have postgresql installed on your client

$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.

edited Aug 10, 2020 at 2:00

answered Aug 9, 2020 at 10:10

dog

1,59716 silver badges10 bronze badges

Comments

Avshalom · Accepted Answer · 2020-06-09 13:29:50Z

5

You actually have a typo in your connection string after Driver declaration. There is a double colon instead of a semicolon :)

Wrong:

{PostgreSQL UNICODE} : Server

Correct:

{PostgreSQL UNICODE} ; Server

edited Jun 9, 2020 at 13:29

Avshalom

8,9471 gold badge29 silver badges50 bronze badges

answered Oct 12, 2016 at 11:04

Michał Ziomek

511 silver badge1 bronze badge

Comments

Vao Tsun · Accepted Answer · 2017-02-01 19:48:44Z

4

Check if the DSN exists in ODBC data source. If not you have to create one going to 'Control Panel', 'Admin. Tools', 'Data Sources (ODBC)'. Then select 'Add User DSN'- Select the PostgreSQL driver, and fill in your server and database details. Test connection to check is all ok!

edited Feb 1, 2017 at 19:48

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

answered Feb 9, 2012 at 20:04

CB.

61.3k9 gold badges170 silver badges165 bronze badges

Comments

stirling · Accepted Answer · 2012-02-14 18:28:13Z

2

I found the problem, I thought the Postgresql ODBC driver was installed, but it wasn't. I finally got it to work after finding this site: http://code.google.com/p/visionmap/wiki/psqlODBC Then I followed the instructions above. it works.

Thanks for all the help.

answered Feb 14, 2012 at 18:28

stirling

1652 gold badges3 silver badges10 bronze badges

Collectives™ on Stack Overflow

Connect to remote PostgreSql database using Powershell

5 Answers 5

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest