From 2ffae197c3ae0a35d682c41574119fea0ab411fd Mon Sep 17 00:00:00 2001
From: Dmitry Koprov <dmitry.koprov@gmail.com>
Date: Wed, 22 Jul 2015 18:52:24 +0300
Subject: adds more info on working with postgres ENUM type

---
 guides/source/active_record_postgresql.md | 46 +++++++++++++++++++++++++++----
 1 file changed, 41 insertions(+), 5 deletions(-)

(limited to 'guides/source/active_record_postgresql.md')

diff --git a/guides/source/active_record_postgresql.md b/guides/source/active_record_postgresql.md
index fe112a4708..b3486ef2bf 100644
--- a/guides/source/active_record_postgresql.md
+++ b/guides/source/active_record_postgresql.md
@@ -220,11 +220,22 @@ normal text columns:
 
 ```ruby
 # db/migrate/20131220144913_create_articles.rb
-execute <<-SQL
-  CREATE TYPE article_status AS ENUM ('draft', 'published');
-SQL
-create_table :articles do |t|
-  t.column :status, :article_status
+def up
+  execute <<-SQL
+    CREATE TYPE article_status AS ENUM ('draft', 'published');
+  SQL
+  create_table :articles do |t|
+    t.column :status, :article_status
+  end
+end
+
+# NOTE: It's important to drop table before dropping enum.
+def down
+  drop_table :articles
+
+  execute <<-SQL
+    DROP TYPE article_status;
+  SQL
 end
 
 # app/models/article.rb
@@ -240,6 +251,31 @@ article.status = "published"
 article.save!
 ```
 
+To add a new value before/after existing one you should use [ALTER TYPE](http://www.postgresql.org/docs/current/static/sql-altertype.html):
+```ruby
+# db/migrate/20150720144913_add_new_state_to_articles.rb
+# NOTE: ALTER TYPE ... ADD VALUE cannot be executed inside of a transaction block so here we are using disable_ddl_transaction!
+disable_ddl_transaction!
+
+def up
+  execute <<-SQL
+    ALTER TYPE article_status ADD VALUE IF NOT EXISTS 'archived' AFTER 'published';
+  SQL
+end
+```
+
+NOTE: by now we can't drop ENUM values. You can read why [here](http://www.postgresql.org/message-id/29F36C7C98AB09499B1A209D48EAA615B7653DBC8A@mail2a.alliedtesting.com).
+
+Hint: to show all the values of the all enums you have, you should call this query in `bin/rails db` or `psql` console:
+```sql
+SELECT n.nspname AS enum_schema,
+       t.typname AS enum_name,
+       e.enumlabel AS enum_value
+  FROM pg_type t
+      JOIN pg_enum e ON t.oid = e.enumtypid
+      JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
+```
+
 ### UUID
 
 * [type definition](http://www.postgresql.org/docs/current/static/datatype-uuid.html)
-- 
cgit v1.2.3