Обсуждение: BUG #8407: json_populate_record case sensitivity

The following bug has been logged on the website:

Bug reference:      8407
Logged by:          Possible bug with json_populate_record?
Email address:      george.stragand@gmail.com
PostgreSQL version: 9.3rc1
Operating system:   Ubuntu
Description:

Using PostgreSQL 9.3beta2.


SELECT version();


PostgreSQL 9.3beta2 on x86_64-unknown-linux-gnu, compiled by gcc
(Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit


The question is regarding json keys in uppercase.  Here's a small example:


create type testPop as (
    foobar int
);


This returns as expected:


select * from json_populate_record(null::testPop, '{"foobar": 1}');


Whereas this form, upper-cased version of foobar, returns an empty record.


select * from json_populate_record(null::testPop, '{"FOOBAR": 1}');


That could be working as designed, as 'foobar' != 'FOOBAR', and is
completely acceptable.


So, drop the type and recreate it with these statements:


drop type testPop;


create type testPop as (
    FOOBAR int
);


And this form will still return an empty record:


select * from json_populate_record(null::testPop, '{"FOOBAR": 1}');


Again, this could be 100% working as designed per the contract of column
names.  That's acceptable, but then the question is how to match keys
expressed as uppercase.  The source of the json is from an external vendor,
so not something we can go back and change directly.


Thank you.

george.stragand@gmail.com escribió:

> create type testPop as (
>     FOOBAR int
> );

Your problem is that this identifier has been downcased.  This works:

alvherre=# create type testPop as (
alvherre(# "FOOBAR" int);
CREATE TYPE
alvherre=# select * from json_populate_record(null::testPop, '{"FOOBAR": 1}');
 FOOBAR
--------
      1
(1 fila)

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 2013-08-29 21:08:56 +0000, george.stragand@gmail.com wrote:
> The following bug has been logged on the website:
>=20
> Bug reference:      8407
> Logged by:          Possible bug with json_populate_record?
> Email address:      george.stragand@gmail.com
> PostgreSQL version: 9.3rc1
> Operating system:   Ubuntu
> Description:       =20
>=20
> Using PostgreSQL 9.3beta2.
>=20
>=20
> SELECT version();
>=20
>=20
> PostgreSQL 9.3beta2 on x86_64-unknown-linux-gnu, compiled by gcc
> (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit
>=20
>=20
> The question is regarding json keys in uppercase.  Here's a small examp=
le:
>=20
>=20
> create type testPop as (
>     foobar int
> );
>=20
>=20
> This returns as expected:
>=20
>=20
> select * from json_populate_record(null::testPop, '{"foobar": 1}');
>=20
>=20
> Whereas this form, upper-cased version of foobar, returns an empty reco=
rd.
>=20
>=20
> select * from json_populate_record(null::testPop, '{"FOOBAR": 1}');
>=20
>=20
> That could be working as designed, as 'foobar' !=3D 'FOOBAR', and is
> completely acceptable.=20
>=20
>=20
> So, drop the type and recreate it with these statements:
>=20
>=20
> drop type testPop;
>=20
>=20
> create type testPop as (
>     FOOBAR int
> );

Postgresql lowercases unquoted identifiers. Make that =BB"FOOBAR" int=AB =
and
it should work.

Greetings,

Andres Freund

--=20
 Andres Freund                       http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services