Re: Selecting newly added column returns empty but only when selecting with other columns in table - Mailing list pgsql-general

From mrtruji
Subject Re: Selecting newly added column returns empty but only when selecting with other columns in table
Date
Msg-id CAJEV4poC4mrVj2iBv0w5_YSdbuvwkjAY=5AVAhLi6-jsdciLKw@mail.gmail.com
Whole thread Raw
In response to Re: Selecting newly added column returns empty but only when selecting with other columns in table  (Adrian Klaver <adrian.klaver@aklaver.com>)
List pgsql-general
Sure. I copied my email submisison from my stack exchange posting so couldn't submit the formatted tables there. Below is an example and you are correct that features_bin is the newly added column. I mean EMPTY ROW in that features_bin returns empty.

     id     | state | features_bin
------------+-------+--------------
 23621407_1 | CA    |
 15892423_1 | CA    |
 15892423_2 | CA    |
 15892423_3 | CA    |
 23614571_2 | CA    |
 20908052_1 | CA    |
 23614571_3 | CA    |
 20908052_2 | CA    |
 20908052_3 | CA    |
 20908066_1 | CA    |
 20908066_2 | CA    |
 20908066_3 | CA    |
 20908072_1 | CA    |
 20908072_2 | CA    |
 20908072_3 | CA    |
 20909002_1 | CA    |
 20909002_2 | CA    |
 23599675_1 | CA    |
 23599675_2 | CA    |
 23599675_3 | CA    |
 18545613_1 | CA    |
 18545613_2 | CA    |


The stdout for the other query without 'where' ( select id, state, features_bin from data;) is too messy to show here. The very long binary array really messes with the formatting but main point is that the row result starts with     |       | \x8481e7dec36500 indicating that the id and state columns are returning empty.

On Thu, Nov 26, 2015 at 6:46 PM, Adrian Klaver <adrian.klaver@aklaver.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.

Can you show one of the unlimited(well at least as much as seems reasonable) query result sets, cut and pasted directly from a psql session?


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

pgsql-general by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: Selecting newly added column returns empty but only when selecting with other columns in table
Next
From: John R Pierce
Date:
Subject: Re: Selecting newly added column returns empty but only when selecting with other columns in table