ok. It looks like a bug tome, and this is Thanksgiving holiday, so probably the developers won't be able to lot at this until Monday. But just to be sure, what happens when you create a new table with the same structure and populate that?
And can you do a pg_dump of the table and see if the data appears? On Thu, Nov 26, 2015 at 9:29 PM, 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... >>> >>> 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. >> > > -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.
