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.
>
