Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Is there anyway to export PostgreSQL schema condensed?

Tags:

sql

postgresql

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.

like image 813
Xeoncross Avatar asked Oct 11 '22 15:10

Xeoncross


1 Answers

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.

like image 82
mu is too short Avatar answered Nov 02 '22 06:11

mu is too short