Re: [GENERAL] Is there a way to address all elements of JSON array when creating a constraint in PosgreSQL?
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 a foreign key to orders. But I want to know if this is possible within
> single JSON column, so I can keep that in mind when designing a database
> schema.
>
>
If you write a short function to help, it's possible. You would need a
subquery to make this assertion, but you can't add one directly as a check
constraint:
create table orders (data JSON);
alter table orders add check (1 <= ALL((select
array_agg((a->>'product_id')::integer) from
json_array_elements(data->'products') as a)));
ERROR: cannot use subquery in check constraint
create function data_product_ids(JSON) returns integer[] immutable as $$
select array_agg((a->>'product_id')::integer) from
json_array_elements($1->'products') as a $$ language sql ;
CREATE FUNCTION
alter table orders add check (1 <= ALL(data_product_ids(data)));
ALTER TABLE
insert into orders (data) values ('{"products": [{ "product_id":1 }, {
"product_id":2 }]}');
INSERT 0 1
insert into orders (data) values ('{"products": [{ "product_id":0 }, {
"product_id":2 }]}');
ERROR: new row for relation "orders" violates check constraint
"orders_data_check"
DETAIL: Failing row contains ({"products": [{ "product_id":0 }, {
"product_id":2 }]}).
Re: [GENERAL] Rank based on the number of matching OR fields?
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 conjunction matches:
ORDER BY case when to_tsquery('A & B & C') @@ vector then 0 else 1
end, ts_rank(vector, to_tsquery('A | B | C')) desc
I think either of these would have the property you want, but I don't know
how they would otherwise affect the quality of the ranking. You should set
up a test group of documents and make sure your mechanism ranks that group
properly on test queries.
Joel
On Tue, Sep 25, 2012 at 11:16 AM, W. Matthew Wilson wrote:
> I want to run a query like to_tsquery("A | B | C") and then rank the
> results so that if a document contained A, B, and C, then it would
> rank above a document that just had some subset.
>
> How would I do such a thing?
>
> --
> W. Matthew Wilson
> m...@tplus1.com
> http://tplus1.com
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
Re: [GENERAL] Add a check an a array column
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_check" # insert into i values (ARRAY[0,1,2,3,1024]); ERROR: new row for relation "i" violates check constraint "i_i_check" Joel On Sat, Sep 8, 2012 at 8:31 AM, Andreas Kretschmer < akretsch...@spamfence.net> wrote: > test=# create or replace function check_array(int[]) returns bool as > $declare i int; begin select into i max(unnest) from unnest($1); if i > 10 > then return false; end if; return true; end$ language plpgsql ; > CREATE FUNCTION > Time: 0,579 ms > test=*# create table a (i int[] check (check_array(i))); > CREATE TABLE > Time: 6,768 ms > test=*# insert into a values (array[1,2,3]); > INSERT 0 1 > Time: 0,605 ms > test=*# insert into a values (array[1,2,30]); > ERROR: new row for relation "a" violates check constraint "a_i_check" >
