Re: [GENERAL] Is there a way to address all elements of JSON array when creating a constraint in PosgreSQL?

2014-01-21 Thread Joel Hoffman
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

Re: [GENERAL] Add a check an a array column

2012-09-08 Thread Joel Hoffman
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

Re: [GENERAL] Rank based on the number of matching OR fields?

2012-09-25 Thread Joel Hoffman
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