> On Nov 26, 2015, at 21:29, mrtruji <mrtr...@gmail.com> wrote: > > Sure thing. Below are the results from your query along with the version and > table info. Not sure about the index. I queried the table quite a bit before > adding the new column and didn't have any issues. > > Here is the result from your query: > > nspname | relname | indexrelname | type | ?column? > ---------+---------+--------------+------+---------- > (0 rows) > > Version: > PostgreSQL 9.4.5 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu > 4.8.2-19ubuntu1) 4.8.2, 64-bit > > > Table "public.data" > Column | Type | Modifiers > --------------+----------------------+----------- > id | text | > name | text | > gender | text | > age | text | > street | text | > city | text | > state | text | > zip | text | > longitude | double precision | > latitude | double precision | > geom | geometry(Point,4326) | > features_bin | bytea | > Indexes: > "ix_data_id" btree (id) > > > > >> On Thu, Nov 26, 2015 at 6:19 PM, Melvin Davidson <melvin6...@gmail.com> >> wrote: >> OK, thanks for clarifying, but just for sanity sake, it would REALLY be nice >> if you would advise us of the exact version of PostgreSQL and the O/S you >> are working with. >> >> A copy of the table structure would also be helpful. >> >> Just one more thing, is it possible you have an index on that table that >> might be corrupted? >> >> What does the following query return? >> >> SELECT n.nspname, >> i.relname, >> i.indexrelname, >> CASE WHEN idx.indisprimary >> THEN 'pkey' >> WHEN idx.indisunique >> THEN 'uidx' >> ELSE 'idx' >> END AS type, >> 'INVALID' >> FROM pg_stat_all_indexes i >> JOIN pg_class c ON (c.oid = i.relid) >> JOIN pg_namespace n ON (n.oid = c.relnamespace) >> JOIN pg_index idx ON (idx.indexrelid = i.indexrelid ) >> WHERE idx.indisvalid = FALSE >> AND i.relname = 'data' >> ORDER BY 1, 2,3; >> >>> On Thu, Nov 26, 2015 at 9:10 PM, mrtruji <mrtr...@gmail.com> wrote: >>> Hi, >>> >>> Thanks for the reply. The limit is just to simplify results for the >>> examples. The same behavior occurs when each of the three queries are not >>> limited. Whenever I try to filter by the original columns and select the >>> new column the resultant values for the new column are empty. Conversely, >>> whenever I select the new column along with original columns without any >>> filtering the resultant values for the original columns return empty. It's >>> as if the added column is disconnected to the table in some way causing >>> problems with queries that combine original columns and the new one. >>> >>> I created and filled in the new column externally using psycopg2 in Python >>> so I'm not sure if that could be the source of the problem...
Did you just fire sql alter statements? Have you tried creating a new table with the bytea column and loading it with the same data? Just to help narrow things down? Also, can you reproduce it? >>> >>>> On Thu, Nov 26, 2015 at 5:39 PM, Melvin Davidson <melvin6...@gmail.com> >>>> wrote: >>>> Is it possible you have more than one row where state = 'CA'? Putting a >>>> LIMIT 1 would then restrict to only 1 row. >>>> Have you tried with no limit? IE: SELECT new_col FROM data; >>>> >>>> >>>>> On Thu, Nov 26, 2015 at 7:13 PM, mrtruji <mrtr...@gmail.com> wrote: >>>>> Just added a new bytea type column to an existing psql table and >>>>> populated the column entirely with row data. Running into some strange >>>>> query results: >>>>> >>>>> When I select the newly added column by itself I get all the data as >>>>> expected: >>>>> >>>>> SELECT new_col FROM data LIMIT 1; >>>>> Result: \x8481e7dec3650040b.... >>>>> When I try to filter with 'where' on another column in the table, I get >>>>> the values from the other columns as expected but empty from my >>>>> new_column: >>>>> >>>>> SELECT id, state, new_col FROM data WHERE state='CA' limit 1; >>>>> Result: 123456_1; CA; EMPTY ROW >>>>> The reverse is also true. If I select my new column in combination with >>>>> other columns with no 'where' I get the correct value from my new column >>>>> but empty for the other columns: >>>>> >>>>> SELECT id, state, new_col FROM data limit 1; >>>>> Result: EMPTY ROW; EMPTY ROW; \x8481e7dec3650040b.... >>>>> Thanks to anyone with advice! >>>>> >>>> >>>> >>>> >>>> -- >>>> Melvin Davidson >>>> I reserve the right to fantasize. Whether or not you >>>> wish to share my fantasy is entirely up to you. >> >> >> >> -- >> Melvin Davidson >> I reserve the right to fantasize. Whether or not you >> wish to share my fantasy is entirely up to you. >
