Re: Selecting newly added column returns empty but only when selecting with other columns in table

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 <mrtruji(at)gmail(dot)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 <melvin6925(at)gmail(dot)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 <mrtruji(at)gmail(dot)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 <melvin6925(at)gmail(dot)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 <mrtruji(at)gmail(dot)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.