Category 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.

Postgres doesn’t create indexes for foreign keys

Story about another #epicfail. I understood if everything is ok I don’t like to write about that cause it looks simple. But when something goes wrong 🙂

Well I worked with Postgres last time about 4 years ago. So definitely I wasn’t a good postgres guys. Last 4 years in most cases I worked with MySql. And mysql automatically creates indexes for foreign keys. Some kind of “second system effect”. When you even don’t think about indexes cause you’re sure that they already should be created.

Well my topic would be useless if I didn’t provider any elegant solution. Right ?

So feel the power of postgres.


SELECT
	tc. TABLE_NAME,
	kcu. COLUMN_NAME
FROM
	information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu ON tc. CONSTRAINT_NAME = kcu. CONSTRAINT_NAME
JOIN information_schema.constraint_column_usage AS ccu ON ccu. CONSTRAINT_NAME = tc. CONSTRAINT_NAME
WHERE
	constraint_type = 'FOREIGN KEY'
ORDER BY table_name

This will return table and column names for ALL foreign keys. Then you can just take this result to generate indexes.

Do not repeat my errors.