*Hi Everyone, I have found that it is necessary to use a CAST() operation
when selecting from tables that contain citext in postgres tables.*
*i have recently found that the following inconsistency / error *
SELECT CAST(tb1.id AS INTEGER) AS id,
CAST(tb1.term AS VARCHAR) as term,
tb1.term as term1,
tb1.created_at as created_at ,
tb1.updated_at as updated_at
FROM my_postgres.public.p_keywords tb1 ;
*note*: column term is defined as CITEXT
SELECT returns the following
+-------+---------------------------------+-------+--------------------------+--------------------------+
| id | term | term1 | created_at
| updated_at |
+-------+---------------------------------+-------+--------------------------+--------------------------+
| 1300 | tall herringbone shirt | null | 2017-08-29 09:11:39.261
| 2017-08-29 09:11:39.261 |
| 1301 | short sleeve herringbone shirt | null | 2017-08-29 09:11:39.267
| 2017-08-29 09:11:39.267 |
| 1302 | slim fit herringbone shirt | null | 2017-08-29 09:11:39.274
| 2017-08-29 09:11:39.274 |
| 1303 | leather leggings | null | 2017-08-29 09:11:39.28
| 2017-08-29 09:11:39.28 |
| 1304 | faux leather leggings | null | 2017-08-29 09:11:39.287
| 2017-08-29 09:11:39.287 |
| 1305 | string bikini bottom | null | 2017-08-29 09:11:39.293
| 2017-08-29 09:11:39.293 |
| 1306 | drawstring bikini bottom | null | 2017-08-29 09:11:39.299
| 2017-08-29 09:11:39.299 |
| 1307 | dress shoes | null | 2017-08-29 09:11:39.306
| 2017-08-29 09:11:39.306 |
| 1308 | lace up dress shoes | null | 2017-08-29 09:11:39.312
| 2017-08-29 09:11:39.312 |
| 1309 | bowl pendant | null | 2017-08-29 09:11:39.319
| 2017-08-29 09:11:39.319 |
| 1310 | shoe cabinet | null | 2017-08-29 09:11:39.325
| 2017-08-29 09:11:39.325 |
| 1311 | shawl collar | null | 2017-08-29 09:11:39.331
| 2017-08-29 09:11:39.331 |
+-------+---------------------------------+-------+--------------------------+--------------------------+
term1 is null as expected due to the lack of a specific CAST() operation on
the column
When I add another column (term_count) to the select statement, the first
CAST also fails
SELECT CAST(tb1.id AS INTEGER) AS id,
CAST(tb1.term AS VARCHAR) as term,
tb1.term as term1,
tb1.term_count as term_count,
tb1.created_at as created_at ,
tb1.updated_at as updated_at
FROM my_postgres.public.p_keywords tb1 ;
CAST(tb1.term AS VARCHAR) not being applied.
+-------+-------+-------+------------+--------------------------+--------------------------+
| id | term | term1 | term_count | created_at |
updated_at |
+-------+-------+-------+------------+--------------------------+--------------------------+
| 1300 | null | null | 3 | 2017-08-29 09:11:39.261 |
2017-08-29 09:11:39.261 |
| 1301 | null | null | 4 | 2017-08-29 09:11:39.267 |
2017-08-29 09:11:39.267 |
| 1302 | null | null | 4 | 2017-08-29 09:11:39.274 |
2017-08-29 09:11:39.274 |
| 1303 | null | null | 2 | 2017-08-29 09:11:39.28 |
2017-08-29 09:11:39.28 |
| 1304 | null | null | 3 | 2017-08-29 09:11:39.287 |
2017-08-29 09:11:39.287 |
| 1305 | null | null | 3 | 2017-08-29 09:11:39.293 |
2017-08-29 09:11:39.293 |
| 1306 | null | null | 3 | 2017-08-29 09:11:39.299 |
2017-08-29 09:11:39.299 |
| 1307 | null | null | 2 | 2017-08-29 09:11:39.306 |
2017-08-29 09:11:39.306 |
| 1308 | null | null | 4 | 2017-08-29 09:11:39.312 |
2017-08-29 09:11:39.312 |
| 1309 | null | null | 2 | 2017-08-29 09:11:39.319 |
2017-08-29 09:11:39.319 |
| 1310 | null | null | 2 | 2017-08-29 09:11:39.325 |
2017-08-29 09:11:39.325 |
| 1311 | null | null | 2 | 2017-08-29 09:11:39.331 |
2017-08-29 09:11:39.331 |
+-------+-------+-------+------------+--------------------------+--------------------------+
is this a bug in version 1.11.0 ? Postgres is PostgreSQL 9.6.3 using
jdbc driver version 42.1.4
Thanks
mb
