Switch database on PGSQL

To switch to another database when connect to PostgreSQL Server use following command on psql.

postgres=>\connect databasename

postgres=> \c databasename

For eg: Lets say you are connected to default postgres database and want to connect to database name mydatabase, try below syntax

postgres=> \c mydatabase or postgres=> \connect mydatabase

mydatabase=>

Commonly use oracle queries and commands

To View all databases 

SQL> SELECT NAME FROM v$database

Shutdown database

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Start the instance

SQL> startup;

ORACLE instance started.

Total System Global Area 4966053832 bytes
Fixed Size                  8906696 bytes
Variable Size             922746880 bytes
Database Buffers         4026531840 bytes
Redo Buffers                7868416 bytes
Database mounted

Open the database

SQL> alter database open;

SQL> select instance_name, status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
orcl             MOUNTED

SQL> alter database open;
Database altered.

SQL> select instance_name, status from v$instance;
INSTANCE_NAME    STATUS
---------------- ------------
orcl            OPEN

MariaDB delete duplicate using ROW_NUMBER()

Please note : Window Functions are available in MariaDB 10.2 onwards

In our previous article, we talked about deleting duplicate data in MySQL using join. In this article we are going to demo on how to delete duplicate using row_number(). Since we already have our demo table created with dummy data, going ahead directly with the query

Step 1: Find duplicate using row_number()

SELECT  id  FROM (SELECT id, ROW_NUMBER() OVER(PARTITION BY name ORDER BY name) AS row_num FROM demo) d WHERE row_num > 1;
+------+
| id   |
+------+
|    7 |
|    3 |
+------+
2 rows in set (0.000 sec)

Step 2: Select * from demo

 select * from demo;

+------+--------+

| id   | name   |

+------+--------+

|    1 | abc    |

|    2 | abd    |

|    3 | vijay  |

|    4 | vijay  |

|    5 | ajay   |

|    6 | sanjay |

|    7 | vijay  |

+------+--------+

7 rows in set (0.000 sec)

Step 3: Delete duplicate

DELETE FROM demo WHERE id IN (SELECT id FROM (SELECT id,ROW_NUMBER() OVER (PARTITION BY name ORDER BY name) AS row_num FROM demo) d  WHERE row_num > 1);
Query OK, 2 rows affected (0.005 sec)

Step 4: Check if duplicate values are deleted using step 2 query

SELECT  id  FROM (SELECT id, ROW_NUMBER() OVER(PARTITION BY name ORDER BY name) AS row_num FROM demo) d WHERE row_num > 1;
Empty set (0.000 sec)

Done, duplicate data deleted.