PostgreSQL - System.ArgumentException: Column requires a valid DataType.

Problem

Sometimes you might get the following error while trying to read the PostgreSQL Table Data using the ZS PostgreSQL Source or PostgreSQL ExecuteSQL Task. 

System.ArgumentException: Column requires a valid DataType.
at System.Data.DataColumn.set_DataType(Type value)
at ZappySys.Common.DataHelper.ConvertDataReaderToDataTable(IDataReader dataReader, DataTable& schemaTable, Int32 maxRows)
at ZappySys.PostgreSql.PgsqlConnectionBase.GetDataTable(SqlDataRequest request)
at ZappySys.PowerPack.SqlData.SqlDataSourceUIBase.GetPreviewData(SqlDataFetchSettings args)
at ZappySys.PowerPack.Controls.SqlDataSourcePropertyPage.(Int32 )
at ZappySys.Common.UI.Forms.DataPreviewForm.()
at ZappySys.Common.UI.Forms.DataPreviewForm.(Boolean )
at ZappySys.Common.UI.Forms.DataPreviewForm..ctor(Func`2 fun, Int32 defaultRowCnt)
at ZappySys.PowerPack.Controls.SqlDataSourcePropertyPage.(Object , EventArgs )

Possible Cause

In most cases, it happens due to you having Geometric Data Types Columns in the PostgreSQL Source Table. And while you try to read the data of that table, it will throw the "Column requires a valid DataType " error if you try to read it without casting it.

https://www.postgresql.org/docs/current/datatype-geometric.html#DATATYPE-GEO-TABLE

PostgreSQL Geometric Types

Name	Storage Size	Description	Representation
point	16 bytes	Point on a plane	(x,y)
line	32 bytes	Infinite line	{A,B,C}
lseg	32 bytes	Finite line segment	((x1,y1),(x2,y2))
box	32 bytes	Rectangular box	((x1,y1),(x2,y2))
path	16+16n bytes	Closed path (similar to polygon)	((x1,y1),...)
path	16+16n bytes	Open path	[(x1,y1),...]
polygon	40+16n bytes	Polygon (similar to closed path)	((x1,y1),...)
circle	24 bytes	Circle	<(x,y),r> (center point and radius)

 

Possible Solutions

So as a solution, you need to cast all the Geometric Data Types Columns to String Type. If your column's data length is under 4000 characters you can cast it to varchar e.g. cast(PointData as varchar) and if you have more than 4000 characters of data you need to cast it to text e.g. cast(PolygonData as text). That's it and you will be able to read the data from that table.

Contact Us

if you have more questions feel free to contact us via Live chat or email at [e-mail]

https://zappysys.com/support/