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

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

From

mrtruji

Date:

27 November 2015, 03:13:43

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!

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

From

Melvin Davidson

Date:

27 November 2015, 04:40:36

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

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

From

mrtruji

Date:

27 November 2015, 05:10:30

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

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

From

Melvin Davidson

Date:

27 November 2015, 05:20:12

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

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

From

mrtruji

Date:

27 November 2015, 05:29:36

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

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

From

Scott Mead

Date:

27 November 2015, 05:35:54

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

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

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

From

Melvin Davidson

Date:

27 November 2015, 05:36:40

ok. It looks like a bug tome, and this is Thanksgiving holiday, so probably the developers won't be able to lot at this until Monday. But just to be sure, what happens when you create a new table with the same structure and populate that?

And can you do a pg_dump of the table and see if the data appears?

On Thu, Nov 26, 2015 at 9:29 PM, mrtruji <mrtruji@gmail.com> 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@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 <mrtruji@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 <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> 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.

Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

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

From

Adrian Klaver

Date:

27 November 2015, 05:43:29

On 11/26/2015 04:13 PM, mrtruji 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 |

I am not following the above.

What does EMPTY ROW mean when referring to a column?

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

The same above, or do you mean EMPTY COLUMN?

Where are you getting the above results, in psql, your Python program or
somewhere else?

Assuming new_col refers to features_bin in your later post, correct?

>
> Thanks to anyone with advice!
>


--
Adrian Klaver
adrian.klaver@aklaver.com

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

From

Adrian Klaver

Date:

27 November 2015, 05:46:39

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

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

From

Adrian Klaver

Date:

27 November 2015, 05:55:47

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

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

From

mrtruji

Date:

27 November 2015, 05:58:13

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

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

From

John R Pierce

Date:

27 November 2015, 06:04:24

On 11/26/2015 4:13 PM, mrtruji 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:.....
>

can you show the SQL commands you used to add this column and populate
it?   if you used INSERT to populate the new column, that would have
inserted NEW rows, not modified existing rows.  to modify the existing
rows, you need to use UPDATE with a WHERE clause that selects the
correct row to UPDATE with the data for your new field...

--
john r pierce, recycling bits in santa cruz

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

From

mrtruji

Date:

27 November 2015, 06:08:59

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

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

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

From

mrtruji

Date:

27 November 2015, 06:12:13

Used psycopg2 in python to create and fill in the table with the code below:

cur.execute('alter table data add features_bin bytea;')

for x in features:

cur.execute('insert into data (features_bin) values (%s);',[x])

conn.commit()

features is a list variable of binary array objects.

On Thu, Nov 26, 2015 at 7:04 PM, John R Pierce <pierce@hogranch.com> wrote:

On 11/26/2015 4:13 PM, mrtruji 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:.....

can you show the SQL commands you used to add this column and populate it? if you used INSERT to populate the new column, that would have inserted NEW rows, not modified existing rows. to modify the existing rows, you need to use UPDATE with a WHERE clause that selects the correct row to UPDATE with the data for your new field...

--
john r pierce, recycling bits in santa cruz

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

From

John R Pierce

Date:

27 November 2015, 06:22:02

On 11/26/2015 7:08 PM, mrtruji wrote:
> 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)


you inserted 278096 additional rows with (NULL, NULL, <somevalues>) to
the 279096 existing rows that had (value,value,NULL)





--
john r pierce, recycling bits in santa cruz

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

From

John R Pierce

Date:

27 November 2015, 06:25:26

On 11/26/2015 7:11 PM, mrtruji wrote:
> for x in features:
>    cur.execute('insert into data (features_bin) values (%s);',[x])
>    conn.commit()

yup, my guess was right.  you inserted new rows with the features_bin
field, but no other fields.

you want to use UPDATE, not INSERT, and you'd better have some way of
specifying which row you want each UPDATE to modify...  like...

     UPDATE data SET features_bin = %s WHERE id = ....;

--
john r pierce, recycling bits in santa cruz

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

From

Melvin Davidson

Date:

27 November 2015, 06:25:39

Look like another case of PEBKAC solved. Thanks Adrian & JOhn for the assist. Happy Thanksgiving everyone.

On Thu, Nov 26, 2015 at 10:22 PM, John R Pierce <pierce@hogranch.com> wrote:

On 11/26/2015 7:08 PM, mrtruji wrote:
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)

you inserted 278096 additional rows with (NULL, NULL, <somevalues>) to the 279096 existing rows that had (value,value,NULL)

--
john r pierce, recycling bits in santa cruz

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

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

From

"David G. Johnston"

Date:

27 November 2015, 06:26:43

On Thursday, November 26, 2015, mrtruji <mrtruji@gmail.com> wrote:

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)

Please bottom post.

What do you get with explain analyze on those two queries.

David J.

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

From

"David G. Johnston"

Date:

27 November 2015, 06:30:08

On Thursday, November 26, 2015, David G. Johnston <david.g.johnston@gmail.com> wrote:

On Thursday, November 26, 2015, mrtruji <mrtruji@gmail.com> wrote:
Ok this is strange. Shouldn't it always be one or the other? Total row count for the table is 279,096.

I would suggest proving the above statement. Do a count without any where clause.

David J.

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

From

mrtruji

Date:

27 November 2015, 06:31:48

On Thu, Nov 26, 2015 at 7:24 PM, Melvin Davidson <melvin6925@gmail.com> wrote:

Look like another case of PEBKAC solved. Thanks Adrian & JOhn for the assist. Happy Thanksgiving everyone.

On Thu, Nov 26, 2015 at 10:22 PM, John R Pierce <pierce@hogranch.com> wrote:
On 11/26/2015 7:08 PM, mrtruji wrote:
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)

you inserted 278096 additional rows with (NULL, NULL, <somevalues>) to the 279096 existing rows that had (value,value,NULL)

--
john r pierce, recycling bits in santa cruz

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

Thanks for all the responses and for pointing me in the right direction!