Database Reference
In-Depth Information
command 3;
COMMIT;
Writing a transaction control command involves editing the script, which you may not want
or even have access to do. There are also other ways.
From psql, you can do this more simply just by using the command line options
-1
or
--single
-transaction
as follows:
bash $ psql -1 -f myscript.sql
bash $ psql --single-transaction -f myscript.sql
The
-1
is short, though I recommend using
--single-transaction
, as it's much clearer
which option is being selected.
How it works...
The whole script will fail if at any point, one of the commands gives an error or higher message.
Almost all of the SQL for defining objects (DDL) allows a way to avoid throwing errors.
Typically, commands that begin with the keyword
DROP
have an option
IF
EXISTS
. This
allows you to execute the
DROP
, whether or not the object already exists; so by the end
of the command that object will not exist.
DROP VIEW IF EXISTS cust_view;
Also, commands that begin with the keyword
CREATE
, mostly have the optional suffix
OR
REPLACE
. This allows the
CREATE
to overwrite the definition if one already exists, or add the
new object if it didn't exist yet, such as the following:
CREATE OR REPLACE VIEW cust_view AS
SELECT * FROM cust;
If both
DROP
IF
EXISTS
and
CREATE
OR
REPLACE
options exist, then you might think you
would just use
CREATE
OR
REPLACE
. If you change the output definition of a function or a
view, then using
OR
REPLACE
is not sufficient. In that case, you must use
DROP
and recreate,
as follows:
postgres=# CREATE OR REPLACE VIEW cust_view AS
SELECT col as title1 FROM cust;
CREATE VIEW
postgres=# CREATE OR REPLACE VIEW cust_view
AS SELECT col as title2 FROM cust;
ERROR: cannot change name of view column "title1" to "title2"
Note also that
CREATE
INDEX
does not have an
OR
REPLACE
option. If you run it twice, you'll
get two indexes on your table, unless you specifically name the index. There is a
DROP
INDEX
IF
EXISTS
, but it may take a long time to drop and recreate an index.
