Getting Postgres CREATE TABLE statements

Question

I created some tables (9, to be exact) with a Django (1.9.6) migration and now I'm trying to get simple CREATE TABLE statements for them. I tried this answer, but using pg_dump in this way gives me over 800 lines of output for the 9 tables. For example, part of the output creating the first table is

--
-- Name: popresearch_question; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE popresearch_question (
    id integer NOT NULL,
    created_date timestamp with time zone NOT NULL,
    modified_date timestamp with time zone NOT NULL,
    question_text character varying(500) NOT NULL,
    question_template_id integer,
    question_type_id integer,
    user_id integer
);


ALTER TABLE popresearch_question OWNER TO postgres;

--
-- Name: popresearch_question_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE popresearch_question_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE popresearch_question_id_seq OWNER TO postgres;

--
-- Name: popresearch_question_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE popresearch_question_id_seq OWNED BY popresearch_question.id;

and then later on are more ALTER statements:

--
-- Name: popresearch_question id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY popresearch_question ALTER COLUMN id SET DEFAULT nextval('popresearch_question_id_seq'::regclass);

and then later:

--
-- Name: popresearch_question popresearch_question_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY popresearch_question
    ADD CONSTRAINT popresearch_question_pkey PRIMARY KEY (id);


--
-- Name: popresearch_question popresearch_question_question_text_key; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY popresearch_question
    ADD CONSTRAINT popresearch_question_question_text_key UNIQUE (question_text);

and after that there are at least a dozen more ALTER TABLE statements just for this one table scattered in the pg_dump output. Is there a way to get a simple, condensed CREATE TABLE statement that includes all the keys, constraints, etc.?

score 0 · Answer 1 · answered May 10 '17 at 02:40

0

I don't know of any Postgres-specific tool that can do that, maybe you should approach this from the Django perspective: https://docs.djangoproject.com/en/1.8/ref/django-admin/#django-admin-sql

answered May 10 '17 at 02:40

Yawar

206
1
8

score 0 · Answer 2 · answered Dec 03 '19 at 14:13

No, there is no way to produce the smallest SQL for the table generated. Though as a side note, if you use IDENTITY COLUMNS rather than serial you'll fine the output is smaller. PostgreSQL is optimized to produce the fastest SQL for the dump; it's not the most pretty.

Use pg_dump -s -t

-s schema only
-t only for table..

Now you may not need all the statements provided, but it will only provide statements about your specific table.

Getting Postgres CREATE TABLE statements

2 Answers2