MySQL and PostgreSQL

When you are transitioning between MySQL and PostgreSQL, the biggest hurdle isn't usually the core SQL logic—it’s the muscle memory. While both are incredibly powerful, open-source relational databases, they speak slightly different dialects and use completely different command-line interfaces.

Here is a quick, practical cheat sheet to help you translate your favorite commands between MySQL and PostgreSQL (often referred to as Postgres). Key Differences Under the Hood Beyond the shortcuts you type into the terminal, these two databases have fundamentally different philosophies.

Limit and Offset vs. Fetch When you want to paginate results, MySQL users universally reach for LIMIT. Postgres supports LIMIT, but standard SQL prefers a different syntax.

MySQL: SELECT * FROM users LIMIT 10 OFFSET 20;

Postgres: SELECT * FROM users OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY; (Note: Postgres still accepts the LIMIT syntax, but standard SQL purists prefer FETCH).

Text Casing MySQL is generally case-insensitive by default when it comes to table and column names (depending on your OS). Postgres, however, folds everything to lowercase unless you wrap the names in double quotes.

In Postgres, SELECT * FROM Users; will look for a table named users. If your table is actually named Users, you must write SELECT * FROM "Users";.

Data Types and Extensibility MySQL is built for speed and simplicity. Postgres is an object-relational database, meaning it is built for complex data types and strict standards compliance.

Postgres has native support for advanced types like JSONB (indexed JSON), network addresses (inet), and geometric data.

While MySQL has added great JSON support over the years, Postgres remains the go-to for complex, highly customized data structures.

• Environment & Navigation Commands
• List all databases
• MySQL: SHOW DATABASES;
• PostgreSQL: \l (or \list)
• Connect to / switch database
• MySQL: USE dbname;
• PostgreSQL: \c dbname
• List all tables in the current database
• MySQL: SHOW TABLES;
• PostgreSQL: \dt
• Exit the command-line interface
• MySQL: EXIT; (or QUIT;)
• PostgreSQL: \q
• Inspecting Database Objects
• View table structure/columns
• MySQL: DESCRIBE tablename; (or EXPLAIN tablename;)
• PostgreSQL: \d tablename
• List all database users
• MySQL: SELECT user FROM mysql.user;
• PostgreSQL: \du
• Show table indexes
• MySQL: SHOW INDEX FROM tablename;
• PostgreSQL: \d tablename (indexes are listed at the bottom of the table description)