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
– Chaitanya K, Commented Nov 6, 2012 at 9:34
This looks like a (buggy and unreliable) attempt to implement an exclusion constraint (postgresql.org/docs/current/static/…). 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
– Craig Ringer, Commented Nov 6, 2012 at 9: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). — user330315
– user330315, Commented Nov 6, 2012 at 9: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
– Craig Ringer, Commented Nov 6, 2012 at 9:50
@CraigRinger: correct, MySQL does not support indexes based on expressions. One the many restrictions MySQL has... — user330315
– user330315, Commented Nov 6, 2012 at 11:02

joop · Accepted Answer · 2012-11-06 12:07:28Z

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 6, 2012 at 12:07

joop

411 bronze badge

Sign up to request clarification or add additional context in comments.

3 Comments

Craig Ringer Over a year ago

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.

joop Over a year ago

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) and/or a rewrite rule if a happens to be > b.

Craig Ringer Over a year ago

Yep, that's what I thought, I just wanted to make sure it was clearly stated.

Community · Accepted Answer · 2017-05-23 10:34:03Z

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.

Collectives™ on Stack Overflow

postgresql query to mysql

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related