q&a   

Every once in a while you are tasked with an issue such as having to create logging fields in each of your tables or having to put like constraints on each of your tables or you need to Grant an X group or X user rights to a certain set of tables.

The nice thing about having an information_schema is that it provides an easy way to generate scripts to do just that with plain SELECT statements. In PostgreSQL its even easier with the array_to_string functions and ARRAY functions, you can get the script in a single field result. In the following sections we'll demonstrate some examples of this.

As Robert Treat pointed out in our PostgreSQL 8.3 is out and the Project Moves On, one of the features that was introduced in PostgreSQL 8.0 was the syntax of

ALTER TABLE sometable 
    ALTER COLUMN somecolumn TYPE new_data_type 
    USING some_function_call_to_cast(somecolumn);

Often times when you start a new database, you put all your tables and views in the public schema. For databases with few tables and all relatively commonly grouped data, the benefits of using schemas for logical groupings vs. the downside of having to reference it with the schema name is more trouble than its worth.

As time goes by and with organic growth, this simple database you had that does one thing suddenly starts doing a lot of other things you hadn't initially planned for. Now to keep your sanity you really need the benefit of schemas for logical groupings. How do you retroactively do this? The answer is not quite as easy as one would hope. Ideally you would want to do a RENAME from public.sometable to newschema.sometable, but that doesn't work.