From: | mrtruji <mrtruji(at)gmail(dot)com> |
---|---|
To: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
Cc: | Melvin Davidson <melvin6925(at)gmail(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Selecting newly added column returns empty but only when selecting with other columns in table |
Date: | 2015-11-27 03:08:35 |
Message-ID: | CAJEV4ppvbRm4zWJ+wRsya2s3gvudL4ww+9qU2EkCDRGzu5gaaw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Ok this is strange. Shouldn't it always be one or the other? Total row
count for the table is 279,096.
doggies=# select count(*) from data where features_bin is null;
count
--------
279096
(1 row)
doggies=# select count(*) from data where features_bin is not null;
count
--------
279096
(1 row)
On Thu, Nov 26, 2015 at 6:55 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:
> On 11/26/2015 06:10 PM, mrtruji 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...
>>
>
> Just had a thought. Wondering if when you populated the table you added
> all the new_col values without actually populating the other fields. So now
> you have existing rows with the non new_col fields populated and another
> set of rows with new_col populated but not the other fields. Your table
> definition allows NULL in all fields so the above is possible.
>
> What happens if you do:
>
> Select * from data where new_col IS NOT NULL;
>
> select * from data where new_col IS NULL;
>
>
>
>
>> On Thu, Nov 26, 2015 at 5:39 PM, Melvin Davidson <melvin6925(at)gmail(dot)com
>> <mailto: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
>> <mailto: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.
>>
>>
>>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>
From | Date | Subject | |
---|---|---|---|
Next Message | mrtruji | 2015-11-27 03:11:50 | Re: Selecting newly added column returns empty but only when selecting with other columns in table |
Previous Message | John R Pierce | 2015-11-27 03:04:35 | Re: Selecting newly added column returns empty but only when selecting with other columns in table |