New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Problem retrieving array of point #613

Closed
jw35 opened this Issue Nov 9, 2017 · 4 comments

Comments

Projects
None yet
3 participants
@jw35

jw35 commented Nov 9, 2017

Given this table:

icp=# create table psycopg2_test (points point[]);
CREATE TABLE
icp=# insert into psycopg2_test values ('{"(1,2)","(3,4)"}');
INSERT 0 1
icp=# select * from psycopg2_test;
      points
-------------------
 {"(1,2)","(3,4)"}
(1 row)

this script

#!/usr/bin/env python3

import psycopg2

conn = psycopg2.connect("dbname='icp' host='localhost'")
cur = conn.cursor()

query = 'select points from psycopg2_test'

cur.execute(query)

for row in cur.fetchall():
    print(repr(row))

throws ValueError:

Traceback (most recent call last):
  File "./array_test.py", line 12, in <module>
    for row in cur.fetchall():
ValueError: could not convert string to float: '(1,2)'

I'm a bit new to both Postgresql Arrays and psycopg2 so I may be misunderstanding. I understand that I could (and have) worked around this using the type casting functions, but I think the documentation says that values of unknown array types will be returned in their underlying text format and that doesn't seem to be happening here.

This seems to be something of a problem for pgcli which blows up with the same message.

I may be imagining things, but in my copy of Postgres, the typarray value for the point datatype is 1017, and the value 1017 is associated with typecast_FLOATARRAY_types[] in typecast_builtins.c which might perhaps explain what's going on?

@jw35 jw35 referenced this issue Nov 9, 2017

Closed

Problem displaying arrays of 'point' data types #814

0 of 3 tasks complete
@fogzot

This comment has been minimized.

Member

fogzot commented Nov 10, 2017

Mm.. in fact, 1017 seems to be the OID that identities an array of points. Removing it from typecast_FLOATARRAY_types[] makes your code work and return just a string, as expected. Unless @dvarrazzo is already working on this I'll add the simple fix (and a test) during the weekend.

@jw35

This comment has been minimized.

jw35 commented Nov 13, 2017

Thanks, I think that will be good.

For what it's worth I went through all the other values assigned to ARRAY_types in typecast_builtins.c and (apart from this one) they all seem to correspond to OIDs for plausible base types for each array type.

@dvarrazzo

This comment has been minimized.

Member

dvarrazzo commented Nov 13, 2017

Yup, confirmed, 1017 shouldn't be associated to float array. It was since we have arrays, circa 2005 :D

Thank you for checking the other values, @jw35. @fogzot fix it if you can, otherwise I'll do it sometimes the next days (I was off for a few days and catching up just now).

@fogzot

This comment has been minimized.

Member

fogzot commented Nov 13, 2017

@dvarrazzo no problem. I already have the fix. Just need to write a test and commit.

@dvarrazzo dvarrazzo added this to the psycopg 2.7.4 milestone Nov 13, 2017

@fogzot fogzot closed this in 5983b96 Nov 16, 2017

fogzot added a commit that referenced this issue Nov 16, 2017

Merge pull request #614 from fogzot/fix-613
Don't cast point arrays to float arrays (fixes: #613)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment