InvalidRequestError: Unknown PG numeric type: 1043

[Craig Swank]

Hello,
I am declaratively defining a table and am having trouble with adding
a float column. I get this error:

InvalidRequestError: Unknown PG numeric type: 1043

when I add a column with either:

from sqlalchemy.dialects.postgresql import NUMERIC

or

from sqlalchemy import Float

and the column definition looks like:

turbine_ok_hours = Column(Float(5))

I am using sqlalchemy-0.6.7, posgresql 9.0, psycopg2--2.4.

This is the 6th table I've added so far for this project, and all the
others are working fine. Any idea what may be wrong? I'm stumped.

Thanks,

Craig

[Michael Bayer]

On May 16, 2011, at 9:23 AM, Craig Swank wrote:

> Hello,
> I am declaratively defining a table and am having trouble with adding
> a float column. I get this error:
>
> InvalidRequestError: Unknown PG numeric type: 1043
>
> when I add a column with either:
>
> from sqlalchemy.dialects.postgresql import NUMERIC
>
> or
>
> from sqlalchemy import Float
>
> and the column definition looks like:
>
> turbine_ok_hours = Column(Float(5))
>
> I am using sqlalchemy-0.6.7, posgresql 9.0, psycopg2--2.4.

So that particular type needs to check the PostgreSQL type code in order to know how to handle what's in the result set. The DBAPI, usually psycopg2, sends back this code within every result set. 1043 as it turns out is VARCHAR, so can't be handled numerically. Your turbine_ok_hours is likely a VARCHAR.

pg8000's source code has a nice table of all these values:

# py type -> pg array typeoid
py_array_types = {
float: 1022,
bool: 1000,
str: 1009, # TEXT[]
unicode: 1009, # TEXT[]
decimal.Decimal: 1231, # NUMERIC[]
}

pg_types = {
16: {"bin_in": boolrecv},
17: {"bin_in": bytearecv},
19: {"bin_in": varcharin}, # name type
20: {"bin_in": int8recv},
21: {"bin_in": int2recv},
23: {"bin_in": int4recv},
25: {"bin_in": varcharin}, # TEXT type
26: {"txt_in": numeric_in}, # oid type
700: {"bin_in": float4recv},
701: {"bin_in": float8recv},
829: {"txt_in": varcharin}, # MACADDR type
1000: {"bin_in": array_recv}, # BOOL[]
1003: {"bin_in": array_recv}, # NAME[]
1005: {"bin_in": array_recv}, # INT2[]
1007: {"bin_in": array_recv}, # INT4[]
1009: {"bin_in": array_recv}, # TEXT[]
1014: {"bin_in": array_recv}, # CHAR[]
1015: {"bin_in": array_recv}, # VARCHAR[]
1016: {"bin_in": array_recv}, # INT8[]
1021: {"bin_in": array_recv}, # FLOAT4[]
1022: {"bin_in": array_recv}, # FLOAT8[]
1042: {"bin_in": varcharin}, # CHAR type
1043: {"bin_in": varcharin}, # VARCHAR type
1082: {"txt_in": date_in},
1083: {"txt_in": time_in},
1114: {"bin_in": timestamp_recv},
1184: {"bin_in": timestamptz_recv}, # timestamp w/ tz
1186: {"bin_in": interval_recv},
1231: {"bin_in": array_recv}, # NUMERIC[]
1263: {"bin_in": array_recv}, # cstring[]
1700: {"bin_in": numeric_recv},
2275: {"bin_in": varcharin}, # cstring
}

>
> This is the 6th table I've added so far for this project, and all the
> others are working fine. Any idea what may be wrong? I'm stumped.
>
> Thanks,
>
> Craig
>

> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>

[Craig Swank]

Indeed! That's what I get for defining my tables based on the pdf my
manager gave me, instead of looking at the tables in psql directly.
Thanks, I was stuck looking at my code for the problem.

Craig