Tag Archives: postgres

Manage postresql ENUM-s on RDS

If you like to work with ENUM fields in DB you can face some problems when you need to add, update or delete enum value. Although Postgres provides some useful tools for that http://www.postgresql.org/docs/9.1/static/sql-altertype.html it’s quite difficult to update or delete ENUM values. Moreover if you use RDS you’ll find out that RDS do not provide you grants to manage ENUM. I prepared some usefull set of queries on today decided to share this code in case it will be useful for someone


<?php

namespace om\db;

abstract class PgMigration extends \CDbMigration
{

    /**
     * Renames ENUM value
     * @param $table
     * @param $field
     * @param $enumName
     * @param $newEnumValue
     * @param $oldEnumValue
     * @throws \CDbException
     * @throws \CException
     */
    public function renameEnumValue($table, $field, $enumName, $newEnumValue, $oldEnumValue)
    {
        $existedValues = $this->getDbConnection()->createCommand("SELECT enum_range(NULL::{$enumName})")->queryScalar();
        $existedValues = substr($existedValues, 1, -1);
        $existedValues = explode(',', $existedValues);
        $existedValues = array_diff($existedValues, [$oldEnumValue]);
        $existedValues[] = $newEnumValue;
        $existedValues = array_unique($existedValues);

        $default = $this->_getColumnDefaultValue($table, $field);
        $isNull = $this->_getColumnIsNull($table, $field);

        $queries = [
            "ALTER TYPE \"{$enumName}\" RENAME TO \"old_{$enumName}\"",
            "CREATE TYPE \"{$enumName}\" AS ENUM ('" . implode("', '", $existedValues) . "')",
            "ALTER TABLE \"{$table}\" RENAME COLUMN \"{$field}\" TO \"old_{$field}\"",
            "ALTER TABLE \"{$table}\" ADD temp_{$field} VARCHAR",
            "ALTER TABLE \"{$table}\" ADD {$field} {$enumName}" . ($default ? " DEFAULT {$default}" : ""),
            "UPDATE \"{$table}\" SET temp_{$field} = old_{$field}::text",
            "UPDATE \"{$table}\" SET temp_{$field} = '{$newEnumValue}' WHERE temp_{$field} = '{$oldEnumValue}'",
            "UPDATE \"{$table}\" SET {$field} = temp_{$field}::{$enumName}",
            $isNull ? "SELECT 1" : "ALTER TABLE \"{$table}\" ALTER COLUMN {$field} SET NOT NULL",
            "ALTER TABLE \"{$table}\" DROP COLUMN old_{$field}",
            "ALTER TABLE \"{$table}\" DROP COLUMN temp_{$field}",
            "DROP TYPE old_{$enumName}"
        ];

        foreach ($queries as $query) {
            $this->getDbConnection()->createCommand($query)
                ->execute();
        }
    }

    /**
     * Adds new value to ENUM
     * @param $table
     * @param $field
     * @param $enumName
     * @param $newValue
     * @throws \CDbException
     * @throws \CException
     */
    public function addEnumValue($table, $field, $enumName, $newValue)
    {
        $existedValues = $this->getDbConnection()->createCommand("SELECT enum_range(NULL::{$enumName})")->queryScalar();
        $existedValues = substr($existedValues, 1, -1);
        $existedValues = explode(',', $existedValues);
        $existedValues[] = $newValue;

        $default = $this->_getColumnDefaultValue($table, $field);
        $isNull = $this->_getColumnIsNull($table, $field);

        $queries = [
            "ALTER TYPE {$enumName} RENAME TO old_{$enumName}",
            "CREATE TYPE {$enumName} AS ENUM ('" . implode("', '", $existedValues) . "')",
            "ALTER TABLE \"{$table}\" RENAME COLUMN {$field} TO old_{$field}",

            "ALTER TABLE \"{$table}\" ADD {$field} {$enumName}" . ($default ? " DEFAULT {$default}" : ""),
            "UPDATE \"{$table}\" SET {$field} = old_{$field}::text::{$enumName}",
            "ALTER TABLE \"{$table}\" DROP COLUMN old_{$field}",
            "DROP TYPE old_{$enumName}"
        ];

        if ($isNull) {
            $queries[] = "ALTER TABLE \"{$table}\" ALTER COLUMN {$field} SET NOT NULL";
        }

        foreach ($queries as $query) {
            $this->getDbConnection()->createCommand($query)
                ->execute();
        }
    }

    /**
     * Remove ENUM value
     * @param $table
     * @param $field
     * @param $enumName
     * @param $deleteValue
     * @param $replaceValue
     * @throws \CDbException
     * @throws \CException
     */
    public function removeEnumValue($table, $field, $enumName, $deleteValue, $replaceValue)
    {
        $existedValues = $this->getDbConnection()->createCommand("SELECT enum_range(NULL::{$enumName})")->queryScalar();
        $existedValues = substr($existedValues, 1, -1);
        $existedValues = explode(',', $existedValues);
        $existedValues = array_diff($existedValues, [$deleteValue]);

        $default = $this->_getColumnDefaultValue($table, $field);
        $isNull = $this->_getColumnIsNull($table, $field);

        $queries = [
            "ALTER TYPE {$enumName} RENAME TO old_{$enumName}",
            "CREATE TYPE {$enumName} AS ENUM ('" . implode("', '", $existedValues) . "')",
            "ALTER TABLE \"{$table}\" RENAME COLUMN {$field} TO old_{$field}",
            "ALTER TABLE \"{$table}\" ADD {$field} {$enumName}" . ($isNull ? "" : " NOT NULL")
            . ($default ? " DEFAULT {$default}" : ""),
            "UPDATE \"{$table}\" SET old_{$field} = '{$replaceValue}' WHERE old_{$field} = '{$deleteValue}'",
            "UPDATE \"{$table}\" SET {$field} = old_{$field}::text::{$enumName}",
            "ALTER TABLE \"{$table}\" DROP COLUMN old_{$field}",
            "DROP TYPE old_{$enumName}"
        ];

        foreach ($queries as $query) {
            $this->getDbConnection()->createCommand($query)
                ->execute();
        }
    }

    private function _getColumnDefaultValue($table, $field)
    {
        return $this->getDbConnection()->createCommand("
            SELECT column_default
            FROM
                information_schema.columns
            WHERE
                (table_schema, TABLE_NAME) = ('public', '{$table}')
                AND column_name = '{$field}'
        ")->queryScalar();
    }

    private function _getColumnIsNull($table, $field)
    {
        $isNull = $this->getDbConnection()->createCommand("
            SELECT is_nullable
            FROM
                information_schema.columns
            WHERE
                (table_schema, TABLE_NAME) = ('public', '{$table}')
                AND column_name = '{$field}'
        ")->queryScalar();

        return strtolower($isNull) == 'yes' ? true : false;
    }
}

In fact this queries will create a copy of enum type and column then copy all values and delete old type and column. This approach consider that you have one table and column dependent of certain ENUM type. If you have 2 dependent columns you’ll need to make some changes.  But you can use generated queries as an example.