英文:
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_tableSET column3 = new_values.column3FROM (SELECT column1,column2,('{apple, orange, banana}'::text[])[row_number() OVER (ORDER BY column1, column2)] AS column3FROM some_table) new_valuesWHERE some_table.column1 = new_values.column1AND some_table.column2 = new_values.column2;
这种方法可以轻松地修改，以循环遍历新列的值，直到所有some_table的行都有新列中的值，方法如下：
WITH t(value_list) AS (VALUES ('{apple, orange, banana}'::text[]))UPDATE some_tableSET column3 = new_values.column3FROM (SELECT column1,column2,t.value_list[(row_number() OVER (ORDER BY column1, column2) - 1) % array_length(t.value_list, 1) + 1] AS column3FROM some_table CROSS JOIN t) new_valuesWHERE some_table.column1 = new_values.column1AND some_table.column2 = new_values.column2;
英文:
This is one approach that might satisfy your requirements:
UPDATE some_tableSET column3 = new_values.column3FROM (SELECT column1,column2,('{apple, orange, banana}'::text[])[row_number() OVER (ORDER BY column1, column2)] AS column3FROM some_table) new_valuesWHERE some_table.column1 = new_values.column1AND 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_tableSET column3 = new_values.column3FROM (SELECT column1,column2,t.value_list[(row_number() OVER (ORDER BY column1, column2) - 1) % array_length(t.value_list, 1) + 1] AS column3FROM some_table CROSS JOIN t) new_valuesWHERE some_table.column1 = new_values.column1AND some_table.column2 = new_values.column2;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论