CODEWITHSUNDEEP
sqlpostgresql
Xeoncross
Xeoncross

Reputation: 57284

Is there anyway to export PostgreSQL schema condensed?

When I create a table in PostgreSQL, the SQL I use looks like this:

CREATE TABLE domain (
    id serial,
    domain character varying(60) NOT NULL,
    banned boolean,
    created timestamp NOT NULL
);

However, when I export the schema I get a whole, uncompressed version with junk like who owns the table and the full sequence. Is there anyway to get an export without at least the owner part?

CREATE TABLE domain (
    id integer NOT NULL,
    domain character varying(60) NOT NULL,
    banned boolean,
    created timestamp without time zone NOT NULL
);

ALTER TABLE public.domain OWNER TO postgres;

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

ALTER TABLE public.domain_id_seq OWNER TO postgres;
ALTER SEQUENCE domain_id_seq OWNED BY domain.id;
SELECT pg_catalog.setval('domain_id_seq', 3, true);

The main thing is that I want to be able to pass the schema around to others (who might not even be using PostgreSQL) without having to manually edit the file and remove the parts that are specific to my DB user. The short hand is also much easier to read.

Upvotes: 5

Views: 2390

Answers (1)

mu is too short
mu is too short

Reputation: 434885

Something like this will get you part of the way there:

pg_dump --schema-only       \
        --no-owner          \
        --no-privileges     \
        --table=$TABLE_NAME \
        $DATABASE_NAME

You'll still have all the SET... stuff at the top and constraints, sequences, and indexes at the end.

You might be able to massage the output of

echo "\d $TABLENAME" | psql -d $DATABASE -U $USER

into what you want with a little bit of scripting.

You could also root around in the system tables to get what you want but that would probably be more work than manually chopping out the middle of what pg_dump has to say.

Upvotes: 4

Related Questions