Postgresql – Altering same table across multiple databases

alter-tabledblinkpostgresql

I have multiple databases on the same instance and I am in the process of updating a table schema that must propagate across all the databases.

I am not sure I know what the right procedure for this is but I think it should go a little something like this?

DO $$DECLARE r record;
BEGIN
  FOR r IN SELECT datname FROM pg_database WHERE datistemplate = false 
  LOOP
    EXECUTE 'ALTER TABLE public.' || quote_ident(r) || ' ALTER VARCHAR(200);';
    EXECUTE ...
  END LOOP;
END;
$$;

Any thoughts?

Best Answer

First of all, if you'd be using the data type text or varchar (without length modifier) instead of the mostly pointless varchar(n), you wouldn't be in this tight spot to begin with. If you really need to enforce a maximum length, use CHECK constraints for that, those can be altered without messing with the table structure.
More details in this related answer on SO.

Next, you cannot execute SQL command in any other DB than the one you are connected to in standard PostgreSQL at this time. This includes DBs in the same cluster. If you try you get an error like:

ERROR: cross-database references are not implemented: ...

To execute SQL commands in other (Postgres) databases, you could install the additional module dblink. I quote the manual here:

dblink executes a query (usually a SELECT, but it can be any SQL statement that returns rows) in a remote database.

Then you can go about refining your DO statement with dblink calls.

Related Solutions

Postgresql – Altering table schema takes too much time

Yes, there is a quicker way to do it if you are using version 9.1. The PostgreSQL version 9.1 release notes include this:

Allow ALTER TABLE ... SET DATA TYPE to avoid table rewrites in appropriate cases (Noah Misch, Robert Haas)

For example, converting a varchar column to text no longer requires a rewrite of the table. However, increasing the length constraint on a varchar column still requires a table rewrite.

Further improvements in this area are coming in version 9.2, which hits beta release next week. But for now it appears the faster way is to alter the type to text, in which case it is a quick update to system tables with no change to the heap.

SQL Server – Alter Table for Multiple Databases

Here's the technique I frequently use to do things like this with dynamic SQL. This also has applications beyond just "looping" across databases, too.

Now, you didn't say which 50 databases, so I just went with everything in the instance, as that's a pretty common thing to do:

DECLARE @sql nvarchar(MAX);

SELECT
    @sql = ISNULL(@sql, N'') +
        N'ALTER TABLE ' + QUOTENAME(d.name) + N'.[dbo].[MyTable] ADD C1 int;'
    FROM sys.databases d
    WHERE
        (d.database_id > 4) AND /* No system databases */
        (d.state = 0) AND /* Online only */
        (d.is_distributor = 0) AND /* Not a distribution database */
        (d.is_read_only = 0) AND /* We can write to it */
        /* TODO: filter me more? */;

BEGIN TRANSACTION;
    EXEC(@sql);
COMMIT TRANSACTION;

You can replace the sys.databases part with a list of constants, or a query from a master database table, or whatever is appropriate for your scenario. There's also no error checking, so you may want to add that.

_{(Yes, I left the syntax error in the above code on purpose.)}

Best Answer

Related Solutions

Postgresql – Altering table schema takes too much time

SQL Server – Alter Table for Multiple Databases

Related Question