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';