Search for a command to run...
Hi everyone, This is Mani. Excited to join this community. I am currently working on my blog for tipseason.com for simple tutorials and interview preparation tips . Looking forward to collaborate with you all. Feel free to DM me if you need any insights about website/blog development or just to say hi 🙂 . Thanks. My website: https://tipseason.com/
No comments yet. Be the first to comment.
In this series, we will learn about different databases and their usage in more beginner friendly way.
Top 6 Databases trending recently (as per stackoverflow developer report) and what each of them are used for? MySQL , PostgreSQL are the top 2 open source SQL databases used by most tech companies and great for interview preparation. They are mainl...
One of the major advancements in search engine world is the ability to provide straight forward answer to the user queries instead of giving a list of links in the search results. Take a look at this example. If you are wondering what these are, t...
The moment Google announced Carbon programming language with a goal to become a successor to C++, it brought a huge attention among Rust language communities with some of the questions like What's the comparison between Carbon language vs Rust ? Why...
Google introduced Carbon programming language with a modern language syntax to become a successor to C++. One of the challenges many beginner developers are facing is to run basic examples since the language is in a very early stage without even havi...
Google introduced Carbon language as the next generation programming language with a goal to replace C++ as a successor. It's an open source project still in experimental phase. Originally demonstrated by Chandler Carruth in CppNorth conference, Carb...
An ultimate case study to combine dbt data transformation at scale
PostgreSQL and MySQL are widely used databases by major companies across multiple projects, given the amount of scale and performance these databases can handle. Many developers work on these two important databases when transitioning to different projects or different companies. When we get used to one database for example MySQL and then transition to another database say PostgresSQL or viceversa, then we tend to use the same commands across each other. However, syntax for postgres and mysql is slightly different. In this tutorial we will compare and understand the differences between PostgreSQL and MySQL in terms of syntax for various operations or queries that are frequently used in production systems.
For the purpose of this tutorial we use psql and mysql clients for showcasing examples. As a quick recap,
psql is a terminal based client for PostgreSQL. We can connect to PostgreSQL server with psql and perform different operations.
mysql is a terminal based client for MySQL. We can connect to MySQL server with mysql and perform different operations.
So let's get started.
As part of setup, we have created a database named demo (using create database demo) upfront in both PostgreSQL and
MySQL. To show a list of all databases present in PostgreSQL or MySQL, we can use the below commands:
Default databases in most PostgreSQL servers are template0 , template0, postgres.
To list all the databases in Postgres i.e. psql we can use : \l
Example in psql client: For our tutorial, you can see database demo is listed in the below example.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+---------------+----------+-------------+-------------+-----------------------
demo | demouser | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
Default databases in most MySQL servers are information_schema , performance_schema, mysql, sys
In MySQL to show list of all databases we can use: show databases
Example in mysql client: For our tutorial, you can see database demo is listed in the below example.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| demo |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.01 sec)
MySQL show databases is equivalent to PostgreSQL list (\l) databases.
If you have multiple databases on your DB server, we often need to switch between multiple databases to perform various DDL or DML operations or to read data. In order to change or use a different database than the currently selected database, we can use the below queries.
To change the database in PostgreSQL we can use : \c {dbname}
\c {dbname} : Switches the current database to the specified database in PostgreSQL
Note: Text in curly braces can to be replaced with your own data. Don't forget to remove the braces. See example below.
postgres-# \c demo
You are now connected to database "demo" as user "demouser".
To change the database in MySQL we can use : use {dbname}
use {dbname} : Switches the current database to the specified database in MySQL
Example:
mysql> use demo;
Database changed
mysql>
Schema is like a namespace of database objects such as tables, views, indexes etc. Depending on the type of DB server, a database can contain multiple schemas, but a schema belongs to only one database. In some DB servers, schema and database are used synonymously.
PostgreSQL supports having multiple schemas in a single database so that you can specify different namespace for different features.
\dn : Shows list of schemas in PostgreSQL
Example:
postgres=# \dn
List of schemas
Name | Owner
--------+----------
public | postgres
(1 row)
In MySQL a schema is similar or synonymous with a database. You can substitute the keyword SCHEMA
instead of DATABASE in MySQL SQL syntax, for example using CREATE SCHEMA instead of CREATE DATABASE.
show schemas : Shows list of schemas in MySQL
Example:
mysql> show schemas;
+--------------------+
| Database |
+--------------------+
| demo |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.02 sec)
Once we change to the desired database, one of the most frequently used operations is to list all the tables that are present in the current database.
To show the list of tables in PostgreSQL, we can use \dt or \dt+ or \d or \d+
\dt or \d : Gives list of tables in current database
\dt+ or \d+: Gives list of tables in current database with additional information like Size, description etc.
star Pro Tip: In postgres/psql, many commands support + as part of the syntax. Eg: Try \dt+ instead of \dt . If we
use + , we can see additional information for that specific command.
Example: For this example two tables demo_table_primarykey (with primary key) and demo_table (without primary key)
are already created for demo purposes.
demo-# \dt
List of relations
Schema | Name | Type | Owner
--------+-----------------------+-------+---------------
public | demo_table | table | demouser
public | demo_table_primarykey | table | demouser
(2 rows)
demo-# \dt+
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+-----------------------+-------+---------------+-------------+---------------+---------+-------------
public | demo_table | table | demouser | permanent | heap | 0 bytes |
public | demo_table_primarykey | table | demouser | permanent | heap | 0 bytes |
(2 rows)
Once we select current database with use demo , to show the list of tables in MySQL, we can use show tables
show tables Gives list of tables in current database for mysql
mysql> show tables;
+-----------------------+
| Tables_in_demo |
+-----------------------+
| demo_table |
| demo_table_primarykey |
+-----------------------+
2 rows in set (0.00 sec)
Now that we know how to list all the tables, let's focus on specific table. If we have to describe the table fields, index etc, we can use the below queries.
To describe a table in PostgreSQL, we can use : \dt {table_name} or \dt+ {table_name}
Alternatively \d {table_name} or \d+ {table_name} can also be used with each of the queries resulting in slightly different columns.
\dt {table_name} Gives basic information about specific table\dt+ {table_name} Gives additional information about specific table\d+ {table_name} Gives index level details too for more specific information. (Preferred way)
Example:
demo=# \d+ demo_table_primarykey;
Table "public.demo_table_primarykey"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
id | integer | | not null | | plain | | |
Indexes:
"demo_table_primarykey_pkey" PRIMARY KEY, btree (id)
Access method: heap
demo=# \dt demo_table_primarykey;
List of relations
Schema | Name | Type | Owner
--------+-----------------------+-------+---------------
public | demo_table_primarykey | table | demouser
(1 row)
demo=# \dt+ demo_table_primarykey;
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+-----------------------+-------+---------------+-------------+---------------+---------+-------------
public | demo_table_primarykey | table | demouser | permanent | heap | 0 bytes |
(1 row)
In MySQL, to describe a table we can use desc {table_name} or describe {table_name} for detailed table information.
describe {table_name} Gives full information about specific table
Example below:
mysql> describe table demo_table_primarykey;
+----+-------------+-----------------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | demo_table_primarykey | NULL | index | NULL | PRIMARY | 4 | NULL | 1 | 100.00 | Using index |
+----+-------------+-----------------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
If we want to see how the table is created or detailed indexes etc, MySQL has a convenient way to show the exact table structure. But in PostgreSQL there is no straight forward way to see it. But here are some alternatives.
Since there is no inbuilt query to show how table is created, we can use \d+ {table_name} like we discussed in the
list table section to view the detailed table info.
If we want the exact table structure we can rely on pg_dump utility which is originally an export schema/data utility of postgres.
We can leverage the schema dump mechanism from that.
pg_dump -st {table_name} {dbname} Dumps the exact table DDL
In MySQL, in order to see how the table is created, we can use
show create table {table_name} Shows the exact DDL that is used to create the table
mysql> show create table demo_table_primarykey;
+-----------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| demo_table_primarykey | CREATE TABLE `demo_table_primarykey` (
`id` int NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-----------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
To view the list of users or roles in PostgreSQL vs MySQL we can use the below syntax.
Detailed of information of users and roles can be obtained from \du
\du Shows the list of roles and their attributes in PostgreSQL
demo=# \du
List of roles
Role name | Attributes | Member of
---------------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
demouser | Superuser, Create role, Create DB | {}
In MySQL mysql.user table gives detailed information about various users, roles and their properties.
To get user and their host you can use below query. Optionally try DESCRIBE mysql.user to see full list of supported attributes.
SELECT User, Host from mysql.user; Shows the list of roles and their attributes in MySQL
mysql> SELECT User, Host from mysql.user;
+------------------+-----------+
| User | Host |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| demouser | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)
If you have a slow query or you want to view the list of current running process list, we can use the below queries.
To show process list in PostgreSQL, we can rely on pg_stat_activity table. This table has very useful information about
currently running activities on the database.
select * from pg_stat_activity Gives the detailed list of processes in PostgreSQL
Example:
postgres=# select * from pg_stat_activity;
datid | datname | pid | leader_pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start | query_start | state_change | wait_event_type | wait_event | state | backend_xid | backend_xmin | query_id | query | backend_type
-------+----------+-------+------------+----------+---------------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-----------------+---------------------+--------+-------------+--------------+----------+---------------------------------+------------------------------
| | 1855 | | | | | | | | 2022-06-17 17:30:34.569684-07 | | | | Activity | AutoVacuumMain | | | | | | autovacuum launcher
| | 1857 | | 10 | postgres | | | | | 2022-06-17 17:30:34.574222-07 | | | | Activity | LogicalLauncherMain | | | | | | logical replication launcher
only some datashown for example purposes
To show process list in MySQL you can use : show processlist
show processlist Gives the detailed list of processes in MySQL
Example:
mysql> show processlist;
+----+-----------------+-----------+------+---------+-------+------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+------+---------+-------+------------------------+------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 65151 | Waiting on empty queue | NULL |
| 8 | demouser | localhost | demo | Query | 0 | init | show processlist |
+----+-----------------+-
As you can see from the above comparison, each database is different and the way you can get information from these databases change a bit. So bookmark and share this post to ease your day-to-day development.
There are some basic differences between PostgreSQL VS MySQL in terms of comments, quotes, case sensitivity. Lets check them here.
--#') character for strings. Example: WHERE site = 'tipseason.com'') and double quote (") characters for strings. Example: WHERE site = 'tipseason.com' or WHERE site = "tipseason.com"WHERE site = 'tipseason.com' might give different results than WHERE site = 'TipSeason.com' . We can use case conversion like (lower , upper etc.) and compare the objects. What do you want to learn next ? Drop a comment below!