Non-obvious ALTER statements are gone

Decided to read PostgreSQL 8.4 Release notes before going to sleep and was pleasantly surprised. The fact of ALTER statements mess (which I described in my post) is now fixed:

Modify the ALTER TABLE syntax to allow all reasonable combinations for tables, indexes, sequences, and views (Tom)

This change allows the following new syntaxes:

  • ALTER SEQUENCE OWNER TO
  • ALTER VIEW ALTER COLUMN SET/DROP DEFAULT
  • ALTER VIEW OWNER TO
  • ALTER VIEW SET SCHEMA

There is no actual new functionality here, but formerly you had to say ALTER TABLE to do these things, which was confusing.

Cool! Way to go!

Advertisements

Re: FK, CHECK, ENUM or DOMAIN

Joshua Drake published his fascinating researches about pros and cons of using ENUMs, DOMAINs, FOREIGN KEY and CHECK constraints. Since I haven’t found a way to leave a comment I’m writing this post.

Joshua wrotes:

A DOMAIN for this problem suffers from the same problems as ENUM as it registers as a type. However a DOMAIN is more flexible as you can apply complex logic to the validation (just as you can with a CHECK). For example a DOMAIN could contain the regex to validate if a email address is correctly formed. I have used domains many times in the past to create complex validating types. They are useful.

In addition I could say that DOMAIN is more flexible because it can have several CHECK constraints. Hence, DOMAIN doesn’t suffer much from being a type. In case of ENUM we must drop the whole type to change its values, but with DOMAIN we may just drop needed CONSTRAINT(s) and(or) create new one.

Let’s imagine situation when we need to add or remove some values to pass validating:

--create domain
CREATE DOMAIN my_domain AS text 
  CONSTRAINT my_domain_check CHECK(VALUE IN ('foo1','foo2'));

--add additional values
ALTER DOMAIN my_domain 
  ADD CONSTRAINT my_domain_check2 CHECK(VALUE IN ('foo3','foo4'));

--remove some values
ALTER DOMAIN my_domain 
  DROP CONSTRAINT my_domain_check;

--list available constraints information
SELECT conname, pg_get_constraintdef(pg_constraint.oid) 
  FROM pg_constraint, pg_type 
  WHERE 
     pg_constraint.contypid = pg_type.oid 
    AND 
     pg_type.oid = 'my_domain'::regtype;

--P.S. Do not like JOINs ;)

Regards

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?