Query information_schema in Postgres

Getting information about your database

PostgreSQL has a system database called INFORMATION_SCHEMA that allows us to extract information about objects, including tables, in our database. First lets see how to query the tables table of the INFORMATION_SCHEMA database to discover information about tables. Most of the time, we will be interested in finding out the tables inside the database for which we will use INFORMATION_SCHEMA.TABLES table and secondly when we want to find out the names of columns and their data types, we use INFORMATION_SCHEMA.COLUMNS table. Each of these tables stores all the relevant metadata about the tables/columns.

-- Select all columns
SELECT *
FROM INFORMATION_SCHEMA.TABLES
-- Filter by schema
WHERE table_schema = 'public';

-- Select all columns
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'actor';

Determining data types

The columns table of the INFORMATION_SCHEMA database also allows us to extract information about the data types of columns in a table. We can extract information like the character or string length of a CHAR or VARCHAR column or the precision of a DECIMAL or NUMERIC floating point type.

-- Get the column name and data type
SELECT
 	column_name,
    data_type
-- From the system database information schema
FROM INFORMATION_SCHEMA.COLUMNS
-- For the customer table
WHERE table_name = 'customer';

Querying the information_schema

If you are using MySQL, you use the DESCRIBE statement to find the information on the columns of a particular table. PostgreSQL does not provide the DESCRIBE statement. However, you can query the information on columns of a table in a couple of ways.

Columns and their types:

SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'provider_json' AND table_schema = 'public';

Check all the tables:

-- Query the right table in information_schema
SELECT table_name
FROM information_schema.tables
-- Specify the correct table_schema value
WHERE table_schema = 'public';