Re: INET type and arrays: too much casting?

On Sep 15, 2008, at 13:25 , Stephane Bortzmeyer wrote:

> On Mon, Sep 15, 2008 at 08:05:15AM -0700,
>  Chris Cogdon <[email protected]> wrote
>  a message of 64 lines which said:
>
>> 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.
>
> Yes, the proper syntax, the one psycopg should have generated, is:
>
> essais=> insert into foobar values ('{2001::1 ,  
> 2001:db8::dead:babe}') ;
> INSERT 0 1
>
> (Quoting the array but not the array members.)
>
>> So, it's postgresql that has a problem turning a text[] into a
>> inet[], not psycopg.
>
> I disagree. psycopg should not have quoted the addresses. I assume it
> did so because, Python being weakly typed, psycopg cannot know that
> 2001::1 is a PostgreSQL special type (Python knows about booleans,
> about integers - I have no problems filling arrays of integers - but
> not UUID or IP addresses).
>
> I do not know if there is a workaround for that?

Well, I kinda disagree with the "psycopg should not quote" sentiment.  
(This is, of course, just a opinion, and there are many opinions just  
like it, and this one is mine :) )

For starters, this isn't really "quoting", but using a far more  
explicit syntax. Ie, it's the comparison between:

'{1,2}'

and

ARRAY[ 1, 2 ]


You can see that the second one is far more explicit and less prone  
to error. The first one is a single string which, in a PARTICULAR  
CONTEXT is converted to an array of strings, and then from strings to  
integers. While the second one... there is no confusion at all of  
what it is: an array of integers. So, in fact, python/psycopg is  
being MORE strongly typed than is necessary.

Using the "string only" syntax is going to be a major headache.  
Consider what is required when you have an array of texts, you have  
to quote the entire thing, then also escape the element separators.  
Far easier to specify the array explicitly, and not have to worry  
about that.

>> However, the following, including an explicit cast, works just fine
>>
>> c.execute ( 'insert into foobar values (%s::inet[])', [["2001::1",
>> "2001:db8::dead:babe"]])
>
> Yes, but this would force me to change a lot of things in my
> application (the INSERT is at a point where the type is no longer
> known - it should be sent together with the value).

Bummer.

> For integers and booleans, arrays work automagically.

That's only because psycopg knows the difference between a string and  
an integer. INET4's are represented as strings at the python level,  
so they're explicit strings at the postgresql level.

> For my IP addresses, should I use adapters?

Yep. That'd be a great way of solving it.


-- 
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