1

In Postgres, ALTER TABLE can accept multiple action forms, separated by a comma. For example:

ALTER TABLE mytable
  DROP CONSTRAINT pkey,
  ADD CONSTRAINT pkey PRIMARY KEY USING INDEX myindex;

Will the actions be executed atomically somehow? Or does the statement need to be wrapped in a transaction to ensure all actions take effect in unison?

2

Every SQL statement is run inside a transaction. At minimum (and by default) each statement runs in its own transaction.

The documentation says:

By default (without BEGIN), PostgreSQL executes transactions in “autocommit” mode, that is, each statement is executed in its own transaction and a commit is implicitly performed at the end of the statement (if execution was successful, otherwise a rollback is done).

So either both actions succeed, or both are rolled back.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.