Non-obvious ALTER statements

I’ve got a friend, let’s call him Ivan. I suppose Ivan is a Postgres fan. So. We talked much about pros and cons of different RDBMS (Relational Database Management Systems) and one of the Ivan’s charges against Postgres was the fact that ALTER commands were incomplete. Say, one cannot change OWNER of a SEQUENCE without recreating it for the role of the desired owner. Same thing about INDEX objects.

For anyone who encounter such troubles as Ivan here are some non-obvious ALTER statements. Ivan, by the way, is an easily carried away person who only skims through manuals. That is why some important notes often escape his attention:

Sequence:

  • ALTER TABLE “Sequence” OWNER TO “Role”
  • ALTER TABLE “Sequence” SET SCHEMA “Schema”
  • ALTER TABLE “Sequence0” RENAME TO “Sequence”
  • GRANT ALL ON TABLE “Sequence” TO “Role”

View:

  • ALTER TABLE “View” OWNER TO “Role”
  • ALTER TABLE “View” SET SCHEMA “Schema”
  • ALTER TABLE “View0” RENAME TO “View”
  • GRANT ALL ON TABLE “View” TO “Role”

Index:

  • all ALTER actions on indices may be done using ALTER TABLE

Some of the commands above are just aliases, but without others it is impossible to make a change. Actually, I am not surprised that by the fact that such a notation is used for this objects, because you know tables, indices, views and sequences are stored in pg_class system catalog.

But it is still obscure for me why there are aliases only for some of the commands, why not to have for every one?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s