Обсуждение: 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