PostgreSQL 檢視資料表的schema及DDL check table schema and ddl

PostgresSQL查看資料表schema及DDL的方式如下。

範例環境：

• PostgreSQL 14

要查看資料表的schema，即欄位名稱、資料型態、主鍵及索引等，可在psql輸入\d <tablename>或\d+ <tablename>檢視，<tablename>為資料表名稱。

例如emloyee資料表如下：

CREATE TABLE IF NOT EXISTS employee (
   id bigserial PRIMARY KEY,
   name varchar(60) UNIQUE NOT NULL,
   age integer,
   created_at timestamp NOT NULL
);

下面在psql輸入\d employee查看employee資料表的schema。

postgres=> \d employee
                                        Table "public.employee"
   Column   |            Type             | Collation | Nullable |               Default
------------+-----------------------------+-----------+----------+--------------------------------------
 id         | bigint                      |           | not null | nextval('employee_id_seq'::regclass)
 name       | character varying(60)       |           | not null |
 age        | integer                     |           |          |
 created_at | timestamp without time zone |           | not null |
Indexes:
    "employee_pkey" PRIMARY KEY, btree (id)
    "employee_name_key" UNIQUE CONSTRAINT, btree (name)

若要查看資料表的DDL，則使用pg_dump命令工具。

例如下面以使用者admin查看本機的postgres資料庫的public.employee資料表的DDL。執行後會印出關於資料表的詳細資訊，其中即有資料表的DDL script。

$ pg_dump -h localhost -U admin -d postgres -t 'public.employee' --schema-only
--
-- PostgreSQL database dump
--

-- Dumped from database version 14.1
-- Dumped by pg_dump version 14.1
...
--
-- Name: employee; Type: TABLE; Schema: public; Owner: admin
--

CREATE TABLE public.employee (
    id bigint NOT NULL,
    name character varying(60) NOT NULL,
    age integer,
    created_at timestamp without time zone NOT NULL
);


ALTER TABLE public.employee OWNER TO admin;

--
-- Name: employee_id_seq; Type: SEQUENCE; Schema: public; Owner: admin
--

CREATE SEQUENCE public.employee_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.employee_id_seq OWNER TO admin;

--
-- Name: employee_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: admin
--

ALTER SEQUENCE public.employee_id_seq OWNED BY public.employee.id;


--
-- Name: employee id; Type: DEFAULT; Schema: public; Owner: admin
--

ALTER TABLE ONLY public.employee ALTER COLUMN id SET DEFAULT nextval('public.employee_id_seq'::regclass);


--
-- Name: employee employee_name_key; Type: CONSTRAINT; Schema: public; Owner: admin
--

ALTER TABLE ONLY public.employee
    ADD CONSTRAINT employee_name_key UNIQUE (name);


--
-- Name: employee employee_pkey; Type: CONSTRAINT; Schema: public; Owner: admin
--

ALTER TABLE ONLY public.employee
    ADD CONSTRAINT employee_pkey PRIMARY KEY (id);


--
-- PostgreSQL database dump complete
--

• How to generate the "create table" sql statement for an existing table in postgreSQL