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

[Scott Mead <scottm@xxxxxxxxxxx>]

On Nov 26, 2015, at 21:29, mrtruji <mrtruji@xxxxxxxxx> 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@xxxxxxxxx> 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@xxxxxxxxx> 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...

Did you just fire sql alter statements?  

Have you tried creating a new table with the bytea column and loading it with the same data?  Just to help narrow things down?

  Also, can you reproduce it?

On Thu, Nov 26, 2015 at 5:39 PM, Melvin Davidson <melvin6925@xxxxxxxxx> 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@xxxxxxxxx> 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.