Re: INET type and arrays: too much casting?

On Sep 15, 2008, at 03:27 , Stephane Bortzmeyer wrote:

> If I create a table:
>
> CREATE TABLE Foobar (addr INET);
>
> psycopg2 can write data just fine:
>
>>>> cursor.execute("""INSERT INTO Foobar VALUES (%s)""", ["2001::1"])
>>>>
>
> But if the column has an array type:
>
> CREATE TABLE Foobar (addr INET[]);
>
> psycopg2 can no longer insert data:
>
>>>> cursor.execute("""INSERT INTO Foobar VALUES (%s)""",  
>>>> [["2001::1", "2001:db8::dead:babe"]])
> Traceback (most recent call last):
>   File "<stdin>", line 1, in <module>
> psycopg2.ProgrammingError: column "addr" is of type inet[] but  
> expression is of type text[]
> HINT:  You will need to rewrite or cast the expression.

The above python statement produces this command to the back-end:

insert into foobar values ( array[E'2001::1',E'2001:db8::dead:babe'] )

Typing that command into psql directly produces the same error. So,  
it's postgresql that has a problem turning a text[] into a inet[],  
not psycopg.

However, the following, including an explicit cast, works just fine

c.execute ( 'insert into foobar values (%s::inet[])', [["2001::1",  
"2001:db8::dead:babe"]])



As an interesting aside, the following WORKS in psql:

insert into foobar values ( E'{2001::1,2001:db8::dead:babe}' );

I THINK this may be because the type of a text string is initially  
just "text" (not text[]), and the parser is free to convert that  
directly into a inet[]. However, since psycopg is creating the array  
part directly, the array ends up as a text[] and postgresql can no  
longer do the conversion implicitly.





-- 
Chris Cogdon       <[email protected]>                         
Chris:  650 242 3518
Truviso Inc.           http:// 
www.truviso.com                              Switch: 650 242 3500
1065 E Hillsdale Blvd Suite 230 Foster City CA 94404     Fax:    650  
242 3501