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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With