postgresql query to mysql

Question

I want create query in MySQL similiar to PostgreSQL below:

CREATE FUNCTION check_unique_pair(IN id1 INTEGER, IN id2 INTEGER) RETURNS INTEGER AS   $body$
DECLARE retval INTEGER DEFAULT 0;
BEGIN 
 SELECT COUNT(*) INTO retval FROM (
  SELECT * FROM edges WHERE a = id1 AND b = id2
  UNION ALL
  SELECT * FROM edges WHERE a = id2 AND b = id1
)  AS pairs;
RETURN retval;
END
$body$
LANGUAGE 'plpgsql';

ALTER TABLE edges ADD CONSTRAINT unique_pair CHECK (check_unique_pair(a, b) < 1);

I am new to MySQL.

try describing your table structure and what you really want to do using this function. Additionally you can check phpmyadmin and MySQL Workbench where you can perform SQL operations graphically. — Chaitanya K, Nov 06 '12 at 09:34
This looks like a (buggy and unreliable) attempt to implement an exclusion constraint (http://www.postgresql.org/docs/current/static/ddl-constraints.html#DDL-CONSTRAINTS-EXCLUSION). Your implementation is extremely unlikely to actually work in the face of any concurrency; it should be urgently replaced with a PostgreSQL 9.1+ exclusion constraint. — Craig Ringer, Nov 06 '12 at 09:46
@CraigRinger: that can be achieved with a simple unique index: `create unique index idx_unique_pair on edges (least(a,b), greatest(a,b))`. That also will be a ***lot*** more efficient (but that's not possible in MySQL). — , Nov 06 '12 at 09:49
@a_horse_with_no_name Nice trick. Sometimes the real talent is seeing the simplest possible answer - something you seem to be awfully good at. Also, wow, MySQL doesn't support expression indexes? — Craig Ringer, Nov 06 '12 at 09:50
@CraigRinger: correct, MySQL does not support indexes based on expressions. One the many restrictions MySQL has... — , Nov 06 '12 at 11:02

score 3 · Answer 1 · answered Nov 06 '12 at 12:07

3

The query can be simplified to:

SELECT COUNT(*)
FROM edges
WHERE a IN (id1, id2)
AND b in (id1, id2)
AND a <> b
   ;

, which can probably use a composite index on {a,b}

answered Nov 06 '12 at 12:07

joop

41
1

That won't provide a constraint, though. For that they'll need a trigger to check that only one row is returned from the (greatly improved) query. – Craig Ringer Nov 06 '12 at 23:25
Embedding it into a trigger function is left as an exercise to the reader. Besides: The question is tagged *both* mysql and postgres, and the trigger syntax may differ. In postgres the whole thing could possibly be implemented by a foreign key constraint + unique{a,b} + CHECK (a b. – joop Nov 08 '12 at 11:36
Yep, that's what I thought, I just wanted to make sure it was clearly stated. – Craig Ringer Nov 08 '12 at 12:20

score 1 · Answer 2 · edited May 23 '17 at 10:34

You appear to be attempting to implement an ASSERTION, table-level CHECK constraint, or an exclusion constraint, though the implementation shown won't work reliably.

MySQL doesn't support CREATE ASSERTION; neither does any other current RDBMS, as far as I know. Nor is subquery support in CHECK for table-level assertions generally supported.

You may need to emulate the behaviour you want using a trigger. See:

@a_horse_with_no_name points out in the comments that you can achieve this with a composite unique expression index. MySQL doesn't support expression indexes, so as noted in the comment it won't work in MySQL. Keep an eye on that bug.

postgresql query to mysql

2 Answers2