Postgres and Open Source Experts

Use the ADD CONSTRAINT or DROP CONSTRAINT clause with the ALTER TABLE command to add or drop a primary key constraint from a Postgres table.

Postgres offers a couple of standard tools/utilities, such as pg_dump and pg_dumpall, to back up a single or all databases.

Install “psycopg2” module, import it into your python program, and use the connect() function of “psycopg2” module to connect to the Postgres database server via python.

Postgres provides an ARRAY_APPEND() function that is used to append/add elements at the end of the array. It accepts two parameters: an array and an element to be appended.

To get the first non-null value in Postgres, an inbuilt function named COALESCE() is used. It accepts the “n” number of arguments and retrieves the first non-null value.

In PostgreSQL, the OFFSET clause is used to skip some records before returning the result set of a query. By default, the OFFSET clause skipped the records from the top.

In PostgreSQL, the EXCEPT operator is used to compare multiple tables. It executes two SELECT queries and retrieves only those records that are not available in the second SELECT query.

In Postgres, the NOW() function retrieves the current date and time along with the time zone(based on the database server’s setting).

PostgreSQL provides a pg_relation_size() function to get the size of a particular table. The “pg_size_pretty()” function is used to get the table’s size in a human-readable format like KBs, MBs.

CURRENT_DATE is one of the Postgres built-in date functions that retrieve the current/today’s date. It doesn’t take any argument.