Interacting with Postgres Integer Array Field

[Paulo Serrão]

Hi!

I have a table on a Postgres database that has a field of type integer[]

Is there a way to use this directly in web2py/pydal and interact with it as a python integer list?

I want to be able to select that field and get a list with [1,2,3,4,5] and I want to be able to update the field and put there [6,7,8,9,10]

The best I could do was set the Field as 'string', that works with the selects (I can loop thru the result and get the numbers), but unfortunately it does not work for the update, it is generating the following SQL:

UPDATE table SET table.fieldname='|6|7|8|9|10|' WHERE (table.id = 123);

Is there a way to push an array back to Postgres?

[Dave S]

Did you try list:integer?
<URL http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#Field-types >

/dps

[Paulo Serrão]

I did, it returns an error from pydal trying to apply a split.

On the documentation it says: On relational databases lists are stored as a text field. The items are separated by a | and each | in string item is escaped as a ||.

So I guess it is expecting the returned field to be a string and raises the error when it tries to split it by |.

[Ovidio Marinho]

web2py interacts with all SQL dialects, but it is always good to use the form of interaction of the proper framework. Native SQL commands should be preceded by the information that you will use, but we do not have any problems with Postgres, maybe this is the only database that sympathizes with web2py. Send us an excerpt of your code so that agent can analyze better. operating system, version of web2py is important in the help request.

Sugestion:

Atualizations Psycopg2 and pg8000 

http://initd.org/psycopg/download/

                  

                     http://itjp.net.br
          Ovidio Marinho Falcao Neto
                 ovid...@gmail.com

                            Brasil

            

--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups "web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to web2py+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Val K]

See Custom Field types in the book