Thread: PostgreSQL force create table / ignore constraints?

Version Postgresql 9.1.6
OS: SLES 11 64 bit

Background:
Our developers create database schema in development environment using
PGAdmin (often using the GUI to generate the DDL).
We always deploy to production using a script, a single .sql file which we
execute via psql command line. This allows us to generate an output with any
errors and have good view of deployment history over time.

Issue
The issue we have is that developers generate the .sql script mainly by
copying and pasting from PGAdmin's SQL pane. The issue we have is then the
order of the object creation is important otherwise creation of tables and
fail when there is a foreign key constraint on another table that does not
exist (but is created later in the script). This is not a big deal in a
schema with 3 or 4 tables but when there are 20+ it is time consuming task
to reorder all the create statements.

Can anyone recommend a way of dealing with this? My only other thought has
been pg_dump although i would prefer if the developers could generate the
scripts themselves.

Thanks,
Rob




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/PostgreSQL-force-create-table-ignore-constraints-tp5726469.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On Wed, 3 Oct 2012 08:12:25 -0700 (PDT)
hartrc <rhart2@mt.gov> wrote:

> Version Postgresql 9.1.6
> OS: SLES 11 64 bit
>
> Background:
> Our developers create database schema in development environment using
> PGAdmin (often using the GUI to generate the DDL).
> We always deploy to production using a script, a single .sql file
> which we execute via psql command line. This allows us to generate an
> output with any errors and have good view of deployment history over
> time.
>
> Issue
> The issue we have is that developers generate the .sql script mainly
> by copying and pasting from PGAdmin's SQL pane. The issue we have is
> then the order of the object creation is important otherwise creation
> of tables and fail when there is a foreign key constraint on another
> table that does not exist (but is created later in the script). This
> is not a big deal in a schema with 3 or 4 tables but when there are 20
> + it is time consuming task to reorder all the create statements.
>
> Can anyone recommend a way of dealing with this? My only other
> thought has been pg_dump although i would prefer if the developers
> could generate the scripts themselves.

What about using pg_dump --schema-only when creating the files? (Or
are you talking about icremental changes?)

Cheers,
Frank

--
Frank Lanitz <frank@frank.uvena.de>