-
In this article we are going to cover how to upgrade a user to be a superuser in PostgreSQL. In PostgreSQL there is a unique term “role”, the term “role” will be discussed later because it’s a complicated feature by itself. TL;DR: ALTER USER username WITH SUPERUSER; Upgrade a User to be a Superuser In…
-
psql is capable of running SQL statements written into a .sql file. Either, you want to run it on a local or remote server you can use psql client. TL;DR: psql -f filename Requirement Either for new product installation, migration, cloning environment, we may need to run sql scripts written in files. In my professional…
-
You have a value to insert to database, but you are not sure, if this row exists on database. What will you do? Upsert is the right term for you, UPdate, if fails inSERT. We can generalize it as insert, on duplicate update. TL;DR: INSERT INTO table () VALUES () ON CONFLICT (row) DO UPDATE…
-
When you need to find the row you want to update with a join statement, you can use join statement as same as select statements in PostgreSQL. TL;DR: UPDATE orders AS v SET price = s.price_per_product FROM products AS s WHERE v.product_id = s.id; In update statements you can use from and join statement as…
-
Capacity planning is an important topic of database management, in terms of size, performance and robustness. TL;DR: \l+ database_name Purpose: PostgreSQL DB Size Command Database size is an important metric for database administrators, that shows growth ratio of the total data. The amount of data we have in our databases effects database performance. Bigger data…
-
Each row in a data table, should be identified, in most cases, using a numeric identifier is the easiest way to achieve it. TL;DR: generated always as identity Creating an Identity Column When you say auto incrementing identitiy column, every DBA think about same, “sequences”. In PostgreSQL you can define a sequence and mark it…
-
In this article we will discuss about how to insert text with single quotes in PostgreSQL. PostgreSQL escapes single quote character with a trick, Today we will cover structure and escaping. TL;DR: ” => real solution Let’s create a demo table to try first. Let’s insert a row with text that includes single quote character…
-
No, you can not. For all database engine, the answer is same, you can not drop a database if there are active connections. But PostgreSQL and some other database engines provide options to kill active connections automatically. TL;DR: DROP DATABASE database_name WITH (FORCE); Dropping a database with active connections As mentioned above, it is not…
-
When you need to create a copy of a database there are several ways to achieve it in PostgeSQL. TL;DR: createdb -O ownerusername -T originaldb newdb If you need to clon a database, you need to plan it carefully. Because you approach will change either if your database is under traffic or not. In this…
-
Today we are going to discuss about character data types in PostgreSQL. On internet you can find misinformation about their performances, storage methods. But today we are going to correct all of them. TL;DR: There is no performance difference between CHAR, VARCHAR and TEXT. You can find PostgreSQL documentation about character datatypes from this link.…