Describe table in PostgreSQL? Yes we can.

In PostgreSQL, there isn't a built-in command or function that directly generates the CREATE TABLE statement for an existing table. Most resources suggest manually recreating the statement by querying the system catalogs to gather the necessary information, but this approach is difficult and does not get you the exact definition. If you have direct access to the PostgreSQL server, you can use pg_dump to retrieve the table definition. pg_dump is PostgreSQL’s command line tool to create backups of your database. It is similiar to mysqldump for MySQL. With the right arguements we can tell pg_dump to only give us the schema of a single table…

pg_dump -t 'table_name' --schema-only --no-owner --no-tablespaces --no-privileges --no-security-labels -s database_name

This will return something like the following:

could not change directory to "/root": Permission denied
--
-- PostgreSQL database dump
--

-- Dumped from database version 14.15 (Ubuntu 14.15-0ubuntu0.22.04.1)
-- Dumped by pg_dump version 14.15 (Ubuntu 14.15-0ubuntu0.22.04.1)

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

SET default_table_access_method = heap;

--
-- Name: news_archive; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.news_archive (
    source text,
    author text,
    title text,
    description text,
    url text,
    image_url text,
    published_date text,
    content text
);


--
-- PostgreSQL database dump complete
--

Since pg_dump often requires SSH access to the server to run it, it's not always the most convenient option. However, by leveraging Foreign Data Wrappers (FDWs), we can create a pure SQL-based solution.

As illustrated in the previous example, pg_dump's output includes extraneous information surrounding the CREATE TABLE statement. To remove the extra stuff we don’t need, we'll pipe the output to sed to extract only the CREATE TABLE statement. The sed command will use regular expressions to capture the text starting with CREATE and ending with the semicolon. We will also use PostgreSQL’s EXECUTE format to dynamically build the CREATE statement for the table name we pass into the function. Let’s also give the function an optional second parameter for the database name, defaulting to postgres.

CREATE OR REPLACE FUNCTION public.fn_describe_table(
    p_table_name text,
    p_database_name text DEFAULT 'postgres'
) RETURNS TABLE (definition text) AS
$$
BEGIN
    -- Drop the temporary foreign table if it exists
    DROP FOREIGN TABLE IF EXISTS temp_pgdump;
    
    -- Create temporary foreign table to capture pg_dump output
    EXECUTE format(
        $fmt$
        CREATE FOREIGN TABLE temp_pgdump (
            source text
        ) 
        SERVER import
        OPTIONS (
            PROGRAM $pg$pg_dump -d %I -t %I --schema-only --no-owner --no-tablespaces --no-privileges --no-security-labels | sed -n '/^CREATE/,/);/p'$pg$,
            FORMAT 'text'
        )
        $fmt$,
        p_database_name,
        p_table_name
    );
    
    -- Return results
    RETURN QUERY SELECT source FROM temp_pgdump;
    
    -- Cleanup
    DROP FOREIGN TABLE IF EXISTS temp_pgdump;
END;
$$ LANGUAGE plpgsql;

Now we can just call our function to get the actual table definition of any postgres table

SELECT definition FROM public.fn_describe_table('public.news_archive');

Here is the result

definition
=====================================
CREATE TABLE public.news_archive (
source text,
author text,
title text,
description text,
url text,
image_url text,
published_date text,
content text
);

Once again, PostgreSQL's Foreign Data Wrappers have proven their versatility and power, providing a clean and elegant SQL-based solution to a problem that traditionally required command-line tools and potentially complex string manipulation.

Thanks for reading! If you found this article enlightening then please SHARE with your friends! See you next week.

Sponsor: I love this outlet extender that I purchased recently. It has two USB C ports, two regular USB ports, and a night light. Check it out. https://www.amazon.com/dp/B0CPPHH9YV?tag=fingerpointfo-20