通过“使用”的重要用法来改变列类型条款

发布于 2025-01-11 21:03:33 字数 874 浏览 0 评论 0原文

假设我们有一个名为“mytable”的表：

name [varchar]	eat_habits [int]
Anna	1
Roland	3
Sepp	1
Katrin	2
Lukas	4
Hedwig	3

现在我意识到我想将“eating_habits”列更改为具体的。我创建自己的枚举类型：

CREATE TYPE diet AS ENUM ('vegetarian', 'vegan', 'omni');

我想要的是将“eating_habits”列的类型更改为“diet”。为此，我还想在这样的类型之间进行映射

1 --> 'vegan'
2 --> 'vegetarian'
rest --> 'omni'

我将如何处理？我知道可以像这样使用 USING 子句：

ALTER TABLE mytable
  ALTER COLUMN eating_habits TYPE diet
  USING (
    <Expression>
  )

但我无法弄清楚“表达式”应该是什么，并且网上的绝大多数示例都是简单的转换。

原文

Assume we have this table named "mytable":

name [varchar]	eating_habits [int]
Anna	1
Roland	3
Sepp	1
Katrin	2
Lukas	4
Hedwig	3

Now I realize I want to change the column "eating_habits" to be specific. I create my own enum type:

CREATE TYPE diet AS ENUM ('vegetarian', 'vegan', 'omni');

What I want is to change the type of the column "eating_habits" to "diet". To do this I also want to map between the types like this

1 --> 'vegan'
2 --> 'vegetarian'
rest --> 'omni'

How would I go about this? I know that one can use the USING clause like this:

ALTER TABLE mytable
  ALTER COLUMN eating_habits TYPE diet
  USING (
    <Expression>
  )

But I can't figure out what "Expression" should be, and the vast majority of examples online are trivial casts.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

余厌 2025-01-18 21:03:33

您需要一个 CASE 表达式：

ALTER TABLE mytable
  ALTER COLUMN eating_habits TYPE diet
  USING ( 
      case eating_habits 
        when 1 then 'vegan'::diet 
        when 2 then 'vegetarian'::diet 
        else 'omni'::diet
      end
  )

You need a CASE expression:

ALTER TABLE mytable
  ALTER COLUMN eating_habits TYPE diet
  USING ( 
      case eating_habits 
        when 1 then 'vegan'::diet 
        when 2 then 'vegetarian'::diet 
        else 'omni'::diet
      end
  )

回复收藏 0 原文

~没有更多了~