On Tue, Jan 21, 2014 at 1:34 AM, Maciej Szopinski wrote:
> Hello,
>
> Does PostgreSQL provide any notation/method for putting a constraint on
> each element of a JSON array?
>
> An example to illustrate:
>
>
> [...]
> I know that this can be done by extracting products to a separate table
> with
More concisely, you can compare directly against all values of the array:
# create table i (i int[] check (0 <= ALL(i) AND 1023 >= ALL(i)));
# insert into i values (ARRAY[0,1,2,3,1023]);
# insert into i values (ARRAY[0,1,2,3,-1]);
ERROR: new row for relation "i" violates check constraint "i_i_che
If you're easily able to do it, (i.e. you're building rather than receiving
the query), you could rank them by the conjunction of the search terms
first:
ORDER BY ts_rank(vector, to_tsquery('A & B & C')) desc, ts_rank(vector,
to_tsquery('A | B | C')) desc
Or just explicitly order by whether the c