PostgreSQL のテーブル定義を出力

やりたいこと

PostgreSQL のテーブル定義を一覧で閲覧できるようにする

pg_dump コマンド

以下で データベース=test_dump、スキーマ=public の全テーブルのテーブル定義を出力することができる。

コマンド

pg_dump -d test_dump -s -n public

※以下は不要な改行を削除してある。

実行例

--
-- PostgreSQL database dump
--

-- Dumped from database version 17.2
-- Dumped by pg_dump version 17.2

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET transaction_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;

--
-- Name: public; Type: SCHEMA; Schema: -; Owner: pg_database_owner
--
CREATE SCHEMA public;
ALTER SCHEMA public OWNER TO pg_database_owner;

--
-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: pg_database_owner
--
COMMENT ON SCHEMA public IS 'standard public schema';

SET default_tablespace = '';
SET default_table_access_method = heap;

--
-- Name: test1; Type: TABLE; Schema: public; Owner: postgres
--
CREATE TABLE public.test1 (
    id integer NOT NULL,
    name character varying(32)
);

ALTER TABLE public.test1 OWNER TO postgres;

--
-- Name: test1_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--
ALTER TABLE public.test1 ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME public.test1_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);

--
-- Name: test2; Type: TABLE; Schema: public; Owner: postgres
--
CREATE TABLE public.test2 (
    id integer NOT NULL,
    name character varying(32)
);

ALTER TABLE public.test2 OWNER TO postgres;

--
-- Name: test2_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--
ALTER TABLE public.test2 ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME public.test2_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);

--
-- Name: test1 test1_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--
ALTER TABLE ONLY public.test1
    ADD CONSTRAINT test1_pkey PRIMARY KEY (id);

--
-- Name: test2 test2_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--
ALTER TABLE ONLY public.test2
    ADD CONSTRAINT test2_pkey PRIMARY KEY (id);

--
-- Name: test1_name_idx; Type: INDEX; Schema: public; Owner: postgres
--
CREATE INDEX test1_name_idx ON public.test1 USING btree (name);

--
-- Name: test2_name_idx; Type: INDEX; Schema: public; Owner: postgres
--
CREATE INDEX test2_name_idx ON public.test2 USING btree (name);

--
-- PostgreSQL database dump complete
--

CREATE TABLE 以外の情報が多く出力される。

information_schema テーブル

information_schema でテーブル名、カラム名等を参照できる。

\connect test_dump

select
    udt_catalog,
    table_name,
    column_name,
    data_type
from
    information_schema.columns
where
    table_schema = 'public'
order by
    table_name asc,
    column_name asc
;

実行結果

 udt_catalog | table_name | column_name |     data_type
-------------+------------+-------------+-------------------
 test_dump   | test1      | id          | integer
 test_dump   | test1      | name        | character varying
 test_dump   | test2      | id          | integer
 test_dump   | test2      | name        | character varying

\d {schema}.*

echo '\d public.*' | psql -d test_dump

実行結果

                                   Table "public.test1"
 Column |         Type          | Collation | Nullable |             Default
--------+-----------------------+-----------+----------+----------------------------------
 id     | integer               |           | not null | generated by default as identity
 name   | character varying(32) |           |          |
Indexes:
    "test1_pkey" PRIMARY KEY, btree (id)
    "test1_name_idx" btree (name)

                    Sequence "public.test1_id_seq"
  Type   | Start | Minimum |  Maximum   | Increment | Cycles? | Cache
---------+-------+---------+------------+-----------+---------+-------
 integer |     1 |       1 | 2147483647 |         1 | no      |     1
Sequence for identity column: public.test1.id

           Index "public.test1_name_idx"
 Column |         Type          | Key? | Definition
--------+-----------------------+------+------------
 name   | character varying(32) | yes  | name
btree, for table "public.test1"

      Index "public.test1_pkey"
 Column |  Type   | Key? | Definition
--------+---------+------+------------
 id     | integer | yes  | id
primary key, btree, for table "public.test1"

                                   Table "public.test2"
 Column |         Type          | Collation | Nullable |             Default
--------+-----------------------+-----------+----------+----------------------------------
 id     | integer               |           | not null | generated by default as identity
 name   | character varying(32) |           |          |
Indexes:
    "test2_pkey" PRIMARY KEY, btree (id)
    "test2_name_idx" btree (name)

                    Sequence "public.test2_id_seq"
  Type   | Start | Minimum |  Maximum   | Increment | Cycles? | Cache
---------+-------+---------+------------+-----------+---------+-------
 integer |     1 |       1 | 2147483647 |         1 | no      |     1
Sequence for identity column: public.test2.id

           Index "public.test2_name_idx"
 Column |         Type          | Key? | Definition
--------+-----------------------+------+------------
 name   | character varying(32) | yes  | name
btree, for table "public.test2"

      Index "public.test2_pkey"
 Column |  Type   | Key? | Definition
--------+---------+------+------------
 id     | integer | yes  | id
primary key, btree, for table "public.test2"