Equivalent Query from MySQL to PostgreSQL

Question

I'm switching from MySQL to PostgreSQL and trying to add a UNIQUE INDEX.

Command that I ran in MySql:

create UNIQUE index idx_friendship_userid_friend_id on Friendship(owner_id, friend_id);

Command that I think is equivalent to above in PostgreSQL:

CREATE UNIQUE INDEX idx_friendship_userid_friend_id ON public."Friendship" USING btree (owner_id, friend_id);

Are the two commands equivalent or am I doing something wrong?

Unless you created your table with a capital F i.e. enclosed its name in double quotes and capitalized the F in there you don't need the double quotes here. And if you're search path is already set to public you can omit the schema qualification too. And BTREE is already the default index option so you don't need to specify that. In short there are good chances that you don't need to change the statement at all and can use the one from MySQL. But why don't you just try any of them and see if they work? If you get an error than you can come back with it and we can help more accurately. — sticky bit
– sticky bit, Commented Apr 14, 2019 at 20:54
@stickybit I didn't get any errors, but just wanted to make sure that the statements are the same. Thanks! — user1107173
– user1107173, Commented Apr 14, 2019 at 21:07

JCE · Accepted Answer · 2019-04-14 21:51:05Z

2

The two statements are equivalent, with a few of caveats:

You don't need to be explicit for public references.
By quoting "Friendship" you are using that specific string, don't use them unless you also used quotes in your CREATE TABLE.
btree is the default value for USING you don't need to be explicit either.

So this should work:

CREATE UNIQUE INDEX idx_friendship_userid_friend_id ON friendship (owner_id, friend_id);

Or this if the table name was quoted when created:

CREATE UNIQUE INDEX idx_friendship_userid_friend_id ON "Friendship" (owner_id, friend_id);

Just in case here's the CREATE INDEX reference.

edited Apr 14, 2019 at 21:51

answered Apr 14, 2019 at 21:02

JCE

1185 bronze badges

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

3 Comments

user1107173 Over a year ago

Thank you!. When I run the command you wrote, I get the following error relation "friendship" does not exist

JCE Over a year ago

That probably means you used quotes in your 'CREATE TABLE', so you'll need them here too.

coladict Over a year ago

@user1107173 When you use double-quotes around the name, it must be exactly the same case-sensitive. If you use the name unquoted, then PostgreSQL will look for the name in lower-case. With MySQL names are case insensitive when the server is running under Windows, and case-sensitive under all other systems.

Zakir Hossain · Accepted Answer · 2019-04-15 03:21:22Z

1

You can try this ......

CREATE UNIQUE INDEX idx_friendship_userid_friend_id
ON friendship
USING btree
(owner_id COLLATE pg_catalog."default" , friend_id );

answered Apr 15, 2019 at 3:21

Zakir Hossain

4425 silver badges22 bronze badges

Collectives™ on Stack Overflow

Equivalent Query from MySQL to PostgreSQL

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related