Translating basic commands between mysql vs psql

          mysql … to … psql      

Several years back when I was learning PostgreSQL and I came from a MySQL background, there were a lot of things I had to learn as far as differences between the two DBMS technologies.

One of the first things to get a handle on were the differences in how to perform very basic tasks such as listing available databases, doing a describe on database objects, listing users etc. in Postgres.

I’ve put together this chart that should be helpful to those who may be somewhat struggling in finding relevant commands in the translation between the two command line tools ( mysql vs psql ) :

TASK MYSQL PSQL
Show databases SHOW DATABASES; list or l or l+
Connect to a database named ‘testdb’ USE testdb Show tables SHOW TABLES; dt or dt+
Show views dv or dv+
Describe Table "}”>d+ <tableName>
Show indexes di or di+
List Users select user, host from mysql.user; du or du+
Create user LOGIN [PASSWORD ‘‘]; (simplified)"}”>CREATE ROLE <username> LOGIN [PASSWORD ‘<password>’]; (simplified)
Change Password for a user WITH ENCRYPTED PASSWORD ‘‘;"}”>ALTER ROLE <username> WITH ENCRYPTED PASSWORD ‘<password>’;
Show the indexes in a table

Copyright © 2020 Nadeem Asrar's Database Journal