英文:
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;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论