![]() If we are working with a large or busy database, or we just want to have some extra assurances that we will not lose data, I prefer simply adding a new column rather than changing the type of an existing one. An Alternative to Changing Column Type in PostgreSQL - Adding a New Column InsteadĬhanging a column type is fairly simple, but for a large database with millions of records and a lot of concurrent queries it is also very expensive because of the ACCESS EXCLUSIVE table lock. Honestly it’s hard to know for certain if a table lock will be required by just looking through documentation.įear not, there is an alternative. These commits for not locking when increasing or removing type lengths (eg going from VARCHAR(100) to VARCHAR(255)).This commit for not locking for VARCHAR -> TEXT conversions, and any other conversions that are binary compatible.Renaming a column is a dangerous thing to do because. There are a few hints at exceptions in the PostgreSQL mailing lists, for example: A tables columns may be safely renamed in PostgreSQL without modifying the data contained in the table. DROP COLUMN IF EXISTS This form drops a column from a table. But indeed I had different types (VARCHAR in the first table, and TEXT in the second table) for the same named columns. If IF NOT EXISTS is specified and a column already exists with this name, no error is thrown. A little bit off-topic comment: I've faced the similar issue when tried to aggregate data from two tables with aid of view. There are some type changes that do not require a lock, but there’s no single place where these are documented (they’re not documented on the ALTER TABLE docs for example), so it’s kind of hard to tell what sort of pain your change will cause. ADD COLUMN IF NOT EXISTS This form adds a new column to the table, using the same syntax as CREATE TABLE. This is crazy slow and expensive, we do not want this to happen to a production database in the middle of the day! When changing the type of a column to a type that has a different internal representation, PostgreSQL will lock the table with an ACCESS EXCLUSIVE lock in order to rewrite the whole table on disk. WARNING: PostgreSQL Column Type Changes Will Lock The Table If you do not use the USING clause, castings can fail and throw errors, like we saw above. When we use the USING clause, we provide an explicit way to transform the values. PostgreSQL will implicitly cast the common values and the constraints to the new data type if you do not use the USING clause, but this doesn’t work for all column type changes. USING Is Not Always Required For Column Type Changes For example we could create a custom function that performs a complex operation and use this instead. While this example is fabricated, this is a foundation for much more complex and useful transformations. Third, specify the new name for the column after the TO keyword. Second, provide name of the column that you want to rename after the RENAME COLUMN keywords. In this simple example we’re converting the value of the title column to be a character count for the original title using a built in PostgreSQL function. First, specify the name of the table that contains the column which you want to rename after the ALTER TABLE clause. ALTER TABLE book ALTER COLUMN title TYPE INTEGER USING ( char_length ( title ))
2 Comments
|
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |