如何将一列值插入到已存在的 PostgreSQL 表中的新列中?

huangapple go评论62阅读模式
英文:

How I can insert a list of values into a new column of the already existing in PostgreSQL?

问题

例如,我有一些文本值:

('apple', 'orange', 'banana')

然后我有一个表格:

Column 1 Column 2
'some value' 'some value'
'some value' 'some value'
'some value' 'some value'

我需要让我的表格看起来像这样:

Column 1 Column2 New Column
'some value' 'some value' 'apple'
'some value' 'some value' 'orange'
'some value' 'some value' 'banana'

我该如何做到这一点?

英文:

For example, I have some text values:

( 'apple', 'orange', 'banana' )

And I have a table:

Column 1 Column 2
'some value' 'some value'
'some value' 'some value'
'some value' 'some value'

I need, that my table looking like this:

Column 1 Column2 New Column
'some value' 'some value' 'apple'
'some value' 'some value' 'orange'
'some value' 'some value' 'banana'

How can I do this?

I found only solutions, that can fill New Column with only one default value, random values and sequences like (1,2,3,4,5), but it isn't what I looking for.

答案1

得分: 0

这是一个可能满足您要求的方法:

UPDATE some_table
  SET column3 = new_values.column3
  FROM (SELECT column1,
               column2,
               ('{apple, orange, banana}'::text[])[row_number() OVER (ORDER BY column1, column2)] AS column3
          FROM some_table) new_values
  WHERE some_table.column1 = new_values.column1
    AND some_table.column2 = new_values.column2;

这种方法可以轻松地修改,以循环遍历新列的值,直到所有some_table的行都有新列中的值,方法如下:

WITH t(value_list) AS (
  VALUES ('{apple, orange, banana}'::text[]))
UPDATE some_table
  SET column3 = new_values.column3
  FROM (SELECT column1,
               column2,
               t.value_list[(row_number() OVER (ORDER BY column1, column2) - 1) % array_length(t.value_list, 1) + 1] AS column3
          FROM some_table CROSS JOIN t) new_values
  WHERE some_table.column1 = new_values.column1
    AND some_table.column2 = new_values.column2;
英文:

This is one approach that might satisfy your requirements:

UPDATE some_table
  SET column3 = new_values.column3
  FROM (SELECT column1,
               column2,
               ('{apple, orange, banana}'::text[])[row_number() OVER (ORDER BY column1, column2)] AS column3
          FROM some_table) new_values
  WHERE some_table.column1 = new_values.column1
    AND some_table.column2 = new_values.column2;

This approach can easily be modified to cycle through the new column values until all of some_table's rows have a value in the new column by doing the following:

WITH t(value_list) AS (
  VALUES ('{apple, orange, banana}'::text[]))
UPDATE some_table
  SET column3 = new_values.column3
  FROM (SELECT column1,
               column2,
               t.value_list[(row_number() OVER (ORDER BY column1, column2) - 1) % array_length(t.value_list, 1) + 1] AS column3
          FROM some_table CROSS JOIN t) new_values
  WHERE some_table.column1 = new_values.column1
    AND some_table.column2 = new_values.column2;

huangapple
  • 本文由 发表于 2023年7月17日 09:17:34
  • 转载请务必保留本文链接:https://go.coder-hub.com/76701013.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定