I have already written a similar article to delete duplicate records in SQL Server and MySQL. Here, You can also access that articles. Delete all duplicate rows in MySQL Delete all duplicates rows except one in SQL Server Recently, I got one request for one script to delete duplicate records in PostgreSQL. Most of the Database Developers have such a requirement to delete duplicate records from the Database. Like SQL Server, ROW_NUMBER() PARTITION BY is also available in PostgreSQL. I have prepared this script, using simple inner query with the use of ROW_NUMBER() PARTITION BY clause ..read more

In this post, I am sharing an example of applying Full Text Search on PostgreSQL Table with Index. Here, I am using tsvector for full text search which is document type and uses match operator like @@. PostgreSQL: Example of Trigram Index for Full Text Search using pg_trgm Extension Below is an example: Create a sample table with data: CREATE TABLE tbl_Textdata (id int, txt character varying); INSERT INTO tbl_Textdata VALUES (1,'I am DBA'),(2,'database research & development'),(3,'think for data only') ,(4,'data is important'),(5,'data is a future'),(6,'I am data owner'); Do Full Te ..read more

THE SERIALIZABLE Isolation level is one kind of extended version of the REPEATABLE READ Isolation level. Now with the SERIALIZABLE Isolation level, you cannot modify the data while another transaction is reading the same data. For SELECT-only transactions, use the SERIALIZABLE isolation level when you do not want to see the other transaction commits during your transaction. For UPDATE and DELETE transactions, SERIALIZABLE isolation prevents concurrent modification of the same data row; it should therefore be used with caution. Create a table with few sample records: CREATE TABLE tbl_Employ ..read more

Important notice: Never try in your Production or any other Live server. This post is only ad-hoc and test server. Data Durability is an important part of Database ACID property. In this post, I am sharing few parameters which we can disable for achieving good performance of PostgreSQL Database Server. We should disable Non-Durable parameters on our test server where we need only POC related work, and if your system doesn’t require any guarantee of data, then you can disable. Why disabling the Non-Durable parameters? PostgreSQL guarantees the full durability of data even if the server cras ..read more

In this post, I am sharing the use of “unaccent” extension of PostgreSQL. Use unaccent, for removing diacritic signs from the string and convert into the valid character as per the specified collation. When we are importing data from different types of datasets, we can find diacritic signs in the data. We can use unaccent() for removing those signs. Check below demonstration: Create an extension: CREATE EXTENSION unaccent; Use unaccent(): SELECT unaccent('ô ã À Æ ß Ä'); Result: unaccent ------------------ 'o a A AE ss A ..read more

In this post, I am sharing few mathematical operators for finding factorial, absolute and negation value in PostgreSQL. I was reading the official the official page of PostgreSQL and found few interesting operators which we can use for the mathematical purpose. Check below examples: ! operator for factorial: SELECT 8 ! AS "8 factorial"; 8 factorial ------------ '40320' @ operator for absolute: SELECT @ '-8.8' AS "absolute value"; absolute values ------------ '40320' ~ operator for negation: SELECT ~ CAST('16' AS int8) AS "negation value"; negation value --------------- '-17 ..read more

Beginners are always searching for sample database so, in this post, I am sharing an official download link to a MySQL Sakila sample database which we can use for our testing purpose. Sakila sample database is a DVD rental database. The database contains sample tables, views, SPs, Functions and Triggers. The MySQL beginner can install this database and can start the practice with it. PostgreSQL beginner can also use this database. Please check the official page here, and you can see all require documentation like, database structure, installation steps ..read more

In this post, I am sharing a script for transforming normal DB user to Super User in PostgreSQL. The Super User is the admin user and it has all kind of access to PostgreSQL Server. We can create multiple superusers in PostgreSQL. I need this kind of handy commands/scripts because when any new dba join the team, giving basic permission of database and later the same user requires superuser permission. Convert a user into Super User: ALTER USER user_name WITH SUPERUSER; Revoke Super User Permission: ALTER USER user_name WITH NOSUPERUSER ..read more

As per the en.wikibooks.org: Bash is a “Unix shell”: a command-line interface for interacting with the operating system. It has the ability to run an entire script of commands, known as a “Bash shell script”. I recently started to create UNIX / LINUX Bash Shell script for enhancing my PostgreSQL DBA Work. You can create a Bash shell script and can connect PostgreSQL using psql. In this post, I am sharing a sample bash shell script to execute psql commands. Open a new file in vi editor: [root@dbrnd anvesh]$ vi helloworld.bash Press [i] key to get an insert prompt: Copy below sample Bash Sh ..read more

In this post, I am sharing an important tip for PostgreSQL DBAs. I recently started work with a new PostgreSQL Client. They have total 326 databases in their PostgreSQL Server. They are taking full backup for all the database and this process is taking around total 7 hours. When I looked this, my first step was to find database statistics. I have found 25% of databases are not being updated frequently. I added one additional check in a backup process like “skip the backup for those databases which have not been changed for any operations (DDL or DML operation)”. After this check, the backup ..read more