On 03/02/2018 10:43 AM, Alexander Farber wrote:
# select * from words_moves where gid=609 limit 3;
-[ RECORD 1
]----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
mid | 1040
action | play
gid | 609
uid | 1192
played | 2018-03-02 10:13:57.943876+01
tiles | [{"col": 3, "row": 7, "value": 2, "letter": "С"}, {"col": 4,
"row": 7, "value": 1, "letter": "О"}, {"col": 5, "row": 7, "value": 2,
"letter": "П"}, {"col": 6, "row": 7, "value": 0, "letter": "Л"}, {"col":
7, "row": 7, "value": 3, "letter": "Я"}]
score | 10
-[ RECORD 2
]----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
mid | 1041
action | play
gid | 609
uid | 7
played | 2018-03-02 10:56:58.72503+01
tiles | [{"col": 3, "row": 8, "value": 2, "letter": "В"}, {"col": 3,
"row": 9, "value": 1, "letter": "И"}, {"col": 3, "row": 10, "value": 2,
"letter": "Т"}, {"col": 3, "row": 11, "value": 2, "letter": "К"},
{"col": 3, "row": 12, "value": 1, "letter": "А"}]
score | 14
-[ RECORD 3
]----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
mid | 1043
action | play
gid | 609
uid | 1192
played | 2018-03-02 11:03:58.614094+01
tiles | [{"col": 0, "row": 10, "value": 2, "letter": "С"}, {"col": 1,
"row": 10, "value": 2, "letter": "М"}, {"col": 2, "row": 10, "value": 1,
"letter": "О"}, {"col": 4, "row": 10, "value": 2, "letter": "Р"}]
score | 13
# select column_name, data_type from information_schema.columns where
table_name='words_moves';
column_name | data_type
-------------+--------------------------
mid | bigint
action | text
gid | integer
uid | integer
played | timestamp with time zone
tiles | jsonb
score | integer
(7 rows)
# select jsonb_array_length(tiles) from words_moves where gid=609 limit 3;
jsonb_array_length
--------------------
5
5
4
(3 rows)
BUT:
# select jsonb_array_length(tiles) from words_moves where gid=609 ;
ERROR: 22023: cannot get array length of a scalar
LOCATION: jsonb_array_length, jsonfuncs.c:1579
Which means only some data is bad, but how to find it please?
What is? :
select count(*) from words_moves where gid=609;
A simplistic approach would be:
select mid, jsonb_array_length(tiles) from words_moves where gid=609
order by mid limit x;
where you increment x until you trigger the error.
A more sophisticated approach would be to use plpgsql EXCEPTION handling:
https://www.postgresql.org/docs/10/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING
and create a function that loops through:
select jsonb_array_length(tiles) from words_moves where gid=609 ;
and RAISES a NOTICE for each incorrect value along with its mid value.
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
