Postgresql ARRAY not supported

[rrottier]

Description

I am trying to retrieve some data from a postgresql database using the SQL Provider. I am able to query some tables without any issue but when I try to run a query on a table containing a column with a double precision [][] type.

The error is:

FsLab Experiment1.fsx(50,18): error FS3021: Unexpected exception from provided type 'FSharp.Data.Sql.SqlDataProvider,DatabaseVendor="2",ConnectionString="Server=crunch.****.com;Database=itrd_db;User Id=******;Password=*****",ResolutionPath="Z:\\FSharp\\EVTAnalysis\\EVTAnalysis\\packages\\Npgsql\\lib\\net45",UseOptionTypes="True"+dataContext+publicSchema+public.itrd_telemetrymatrix' member 'GetMethods': The type provider 'FSharp.Data.Sql.SqlTypeProvider' reported an error: Could not get columns for `public.itrd_telemetrymatrix`, the type `ARRAY` is unknown to Npgsql

But when I just run a plain Npgsql query I am able to get the data without any issue.

Related information

• Windows 10
• Visual Studio 2015

[ingted]

Hi rrottier,

What version is your PG?
I bumped into some similar issues and after porting Npqsql 2.2.7 to .net 4.6.1 and replace Npgsql 3.1.4 in projects reference, problems were gone!

PS. I use VS2015 as well...

[rrottier]

Yep I am using Npgsql 3.1.4 which is just what nuget got me. I am not sure how to port Npgsql to .net 4.6.1 as I am pretty new to .Net land. So if you have any detailed steps that I could follow that would be great.

I use postgres v9.3.5

But I am curious, if the issue is with Npgsql why then am I able to get the data without any issue when just running it as an SQL query through Npgsql 3.1.4 directly?

[janno-p]

Hey!

The error message is actually misleading. It comes from SqlProvider, not Npgsql, so it shouldn't make statements about what Npgsql supports or not.

The real cause of the error is that currently SqlProvider doesn't handle complex Npgsql/PosgreSQL types like array, enum, composite or range which are supported by Npgsql.

[ingted]

Hi rrottier,

Please try this Npgsql 2.2.7 with .net 4.6.1
https://github.com/ingted/npgsql4.6.1

(Maybe I was wrong... but take a try just for a chance, right?)

Regards
Anibal

[ingted]

Hi rrottier,

Excuse me, I found that SQLProvider doesn't support ARRAY...

Here is the code.... (in Providers.Postgresql.fs)

        //"array"                       , typemap<Array>                      ["Array"]
        //"composite"                   , typemap<obj>                        ["Composite"]
        //"enum"                        , typemap<obj>                        ["Enum"]
        //"range"                       , typemap<Array>                      ["Range"]

[rrottier]

Ah I see, the array code is all commented out. Thanks so much @ingted for finding the issue. Does that mean that it is broken for now but may be fixed in the future?

Any suggestions for what else to use for Postgres access that might support arrays and F# Query language?

I know that npgsql works but then I am back to doing all my queries as strings...

[giovicespedes]

I have a problem with roundcube
Error: xframework does not support database provider postgres
Someone help me