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

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@gmail.com
> <mailto:melvin6925@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 <mrtruji@gmail.com
>     <mailto:mrtruji@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.
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com