Postgres and Open Source Experts

In PostgreSQL, the crosstab() function and the unnest() function are used to transpose columns into rows. These functions help us transform the table by returning the transposed table.

To get random rows from a database table, use the RANDOM() function with the ORDER BY clause. We can also specify the limit of the number of rows we want to fetch/retrieve.

The lcm() and gcd() functions take two numeric type parameters and return their lowest common factor and the greatest common divisor respectively.

To get the data between two specified date ranges, use the “WHERE” and “AND” clauses, the BETWEEN operator, the SYMMETRIC keyword, and the Range data types.

To find the size of the Postgres Tablespace, use the “pg_tablespace_size” function that takes the tablespace name as an argument and returns its size.

To find the average of a column we use the AVG() function taking the name of the column as a parameter. The ROUND() function rounds off a specified number up to particular decimal places.

In PostgreSQL, the ordering is done by using the ASC and DESC keywords but we can also customize the sorting order using several ways, such as the CASE keyword, array_position() function, etc.

To change a Primary key in Postgres, remove the existing primary key from the table, and add a new primary key using the “ALTER TABLE ADD PRIMARY KEY” statement.

To concatenate multiple columns in PostgreSQL, the CONCAT() function is used. Moreover, we can also use the concatenation operator “||”.

The NOT LIKE operator looks for the specified character/text/string in the database and returns those records that do not contain that specified string/pattern.