How do I select arrays that are not empty?

Question

Why is this so tricky, what is token set to that it isn't equal to null nor an empty string?

SELECT lexemes
FROM ts_debug('This is a title')
WHERE alias = 'asciiword';

 lexemes 
---------
 {}
 {}
 {}
 {titl}
(4 rows)

Ok.. So I want to get rid of {},

SELECT lexemes
FROM ts_debug('This is a title')
WHERE alias = 'asciiword'
  AND lexemes <> '{}'
  AND lexemes <> ARRAY[]::text[]
  AND lexemes IS NOT NULL
  AND lexemes <> ARRAY[' ']
  AND lexemes <> ARRAY[null]::text[];

I knew most of these wouldn't work., but I'm totally confused why <> '{}' wouldn't work not <> ARRAY[]::text;. How do I filter this out?

Answer 1

The reason seems to be that empty strings in that column have array dimensions [1:0]. Should normally be NULL. See:

SELECT lexemes, array_dims(lexemes) FROM ts_debug('a title');

 lexemes | array_dims
---------+------------
 {}      | [1:0]  -- !!
         |
 {titl}  | [1:1]

Empty arrays normally have NULL as array dimensions.

SELECT '{}'::text[] AS test, array_dims('{}'::text[]);

 test    | array_dims
---------+------------
 {}      | <NULL>

Hence, the comparison lexemes = '{}'::text[] returns FALSE. Looks like a bug to me. I tested versions 8.4 - 10 Beta. It's in all versions.

As a workaround, to exclude all empty arrays including the odd case (and NULL):

SELECT *
FROM   ts_debug('This is a title')
WHERE  cardinality(lexemes) > 0;

Or compare the text representation:

...
AND    lexemes::text <> '{}';

I filed bug report #14826.

Answer 2

We can use below query:

SELECT lexemes
FROM ts_debug
WHERE alias !='{}';