How to use Excel Power Query to access a postgresql database

Question

We have a postgresql 9.0 database which we want to connect to via Excel 2010 running the Power Query plugin. I've setup the machine running excel to be able to use Npgsql to do the connection according to these instructions http://office.microsoft.com/en-us/excel-help/connect-to-a-postgresql-database-HA104028095.aspx?CTT=5&origin=HA104003952. The connection fails in Excel with this error message:

DataSource.Error: PostgreSQL: ERROR: 42883: function concat(information_schema.character_data, unknown) does not exist Details: Message=ERROR: 42883:..., ErrorCode=-2147467259

Has anyone successfully connected to a postgresql database from Excel using the Power Query plugin? There's a menu item on the power query ribbon in excel specifically for postgresql so I figured it would work. The concat function isn't in our postgresql version of 9.0 so do I have to upgrade our postgresql database to do this?

Yeah I guess. I'm looking into pg_upgrade. I wonder why excel wants to use that function to simply connect to the database. — Adam Bruss
– Adam Bruss, Commented Dec 6, 2013 at 14:27
As "information_schema" is mentioned in the error, it probably retrieves some metadata (e.g. list of various existing DB objects). — Milen A. Radev
– Milen A. Radev, Commented Dec 6, 2013 at 14:41

Chris Travers · Accepted Answer · 2013-12-22 04:43:54Z

1

You can either upgrade or define your own function like this:

CREATE OR REPLACE concat(information_schema.character_data, varchar) 
RETURNS varchar LANGUAGE SQL AS
$$ SELECT $1::varchar || $2; $$;

But upgrading to 9.1 seems likely the best approach.

answered Dec 22, 2013 at 4:43

Chris Travers

26.6k6 gold badges70 silver badges191 bronze badges

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

Collectives™ on Stack Overflow

How to use Excel Power Query to access a postgresql database

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related