PostgreSQL Query Schema

In PostgreSQL, you can retrieve the schema details of a specific table by querying the information_schema.columns view or by using the pg_catalog.pg_attribute table. Here’s an example SQL query using information_schema.columns:

sqlCopy
SELECT
    column_name,
    data_type,
    character_maximum_length,
    is_nullable,
    column_default
FROM
    information_schema.columns
WHERE
    table_name = 'your_table_name'
ORDER BY
    ordinal_position;

This query will provide details such as column names, data types, maximum length, whether the column allows NULL values, and any default values.

Be sure to replace 'your_table_name' with the name of the table you want to inspect.

---

Select all tables in the database.

sqlCopy
SELECT
    table_name,
    column_name,
    data_type,
    is_nullable,
    column_default
FROM
    information_schema.columns
WHERE
    table_schema = 'public'
ORDER BY
    table_name,
    ordinal_position;

---

Select schema details for all tables that have a specific prefix in their name.

sqlCopy
SELECT
    table_name,
    column_name,
    data_type,
    is_nullable,
    column_default
FROM
    information_schema.columns
WHERE
    table_schema = 'public'
    AND table_name LIKE 'prefix_%'
ORDER BY
    table_name,
    ordinal_position;

Replace prefix_ with the actual prefix you're looking for.