DESCRIBE

---

In many SQL flavors (like MySQL, Oracle, or Hive), DESCRIBE is a standard command used to see the columns, types, and constraints of a table.

However, in PostgreSQL, DESCRIBE is not a SQL command. If you try to run DESCRIBE my_table; in a Postgres SQL editor, it will return a syntax error. Instead, Postgres handles this through its command-line interface (psql) or by querying the System Information Schema.

---

The psql Method (The Meta-Command)

If you are using the terminal-based psql client, you use "backslash commands" to describe objects. These are incredibly fast and provide a wealth of metadata.

Command	Purpose
\d table_name	Describes columns, types, modifiers, and indexes for a table.
\d+ table_name	Provides an "expanded" view including comments and physical storage size.
\dt	Lists all tables in the current database.
\df	Lists all functions.
\dv	Lists all views.

---

The SQL-Standard Method (Information Schema)

If you are writing a script or using a GUI (like DBeaver or pgAdmin) and need to "describe" a table using actual SQL, you query the information_schema. This is portable across many database systems.

SELECT 
    column_name, 
    data_type, 
    is_nullable, 
    column_default
FROM information_schema.columns
WHERE table_name = 'users';

---

Comparing "Describe" across SQL Flavors

As a Data Engineer, you’ll likely switch between systems. Here is how "Describe" translates:

Database	Command
MySQL / MariaDB	DESCRIBE table_name; or DESC table_name;
Oracle	DESC table_name;
SQL Server	exec sp_help 'table_name';
PostgreSQL	\d table_name (CLI) or query information_schema
Hive / Spark SQL	DESCRIBE FORMATTED table_name;

---

What information is actually returned?

When you "describe" a table in Postgres (via \d), you get a detailed report containing:

1. Column Name: The identifier.

2. Type: The data type (e.g., integer, timestamp with time zone).

3. Collation: Language settings for strings.

4. Nullable: Whether the column allows NULL.

5. Default: Any default values (like nextval() for IDs).

6. Indexes: A list of B-Tree, GIN, or Hash indexes on the table.

7. Foreign Key Constraints: Which tables this table references.

8. Referenced By: Which tables reference this table.

---

Summary

• Command: In Postgres, use \d in the terminal.

• Non-CLI: Query information_schema.columns for a programmatic description.

• Pro-Tip: Use \d+ if you want to see the underlying physical persistence details (like whether the table is partitioned or what its "fillfactor" is).

---