Below are five ways to check if a table exists in a PostgreSQL database.
Object-Relational
How to Create a Table Only if it Doesn’t Exist in PostgreSQL
In PostgreSQL, you can use the IF NOT EXISTS clause of the CREATE TABLE statement to check whether or not a table of the same name already exists in the database before creating it.
The table will only be created if no other table exists with the same name. If a table already exists with that name, a “notice” will be issued instead of an error.
Find a String within a String in SQL
Most of the major DBMSs provide us with a way to find a string within a string using SQL. By this, I mean use a SQL query to find the position of a substring within a string.
There are several SQL functions that allow us to do this, including INSTR(), LOCATE(), POSITION(), and CHARINDEX(). The function you use will depend on your DBMS, and possibly whether or not you need to specify a starting position.
How to Return Query Results as a Comma Separated List in PostgreSQL
In Postgres, we can use the STRING_AGG() function to transform our query results into a comma separated list.
Instead of each value being output in a separate row (as with any regular query), the values are output to a single row, separated by a comma (or some other delimiter of our choosing).
Fix: “operator does not exist: integer || integer” in PostgreSQL
If you get the “operator does not exist: integer || integer” error in PostgreSQL, it’s probably because you’re trying to concatenate two numbers.
If you really want to concatenate two numbers, the easiest way to overcome this issue is to cast at least one of them to a string data type first.
Another way to do it is to use the CONCAT() function.
RR vs YY in Oracle
When formatting dates in Oracle Database, we have the option of using RR and YY to return a two digit year.
These two format elements are similar. The difference is in how they interpret two digits years.
We also have the option of using RRRR and YYYY when returning four digit years.
2 Ways to Get the Size of a Database in PostgreSQL
Below are two ways to return the size of a specific database in PostgreSQL.