如何使用字符串更新text []字段 [英] How to update a text[] field with a string
问题描述
我有一个表,其中的字段称为标签,可以包含任意数量的字符串:
表 public.page
栏|类型修饰符
---------------------- + ---------------------- ---- + ----------------------------------
标签|文字[] |不是null默认值ARRAY [] :: text []
我想在标签字段中添加字符串-但我似乎无法让concat函数为我工作。我尝试过:
更新页面set tags = concat('My New String',tags);
错误:函数concat(unknown,text [])不存在
第1行:更新页面集tags = concat('My New String',tags)其中...
^
提示:没有函数与给定的名称和参数类型匹配。您可能需要添加显式类型转换。
和
更新页面集标签=('我的新字符串'||标签);
错误:运算符不是唯一的:未知|| text []
第1行:更新页面集标记=(我的新字符串 ||标记),其中...
^
提示:无法选择最佳候选运算符。您可能需要添加显式类型转换。
有什么想法吗?
在PostgreSQL的类型系统中,文字'My New String'
不是 varchar
或文本
值,但类型为 unknown
的文字,可以将其处理为任何类型。 (例如,日期
的文字可能是'2013-08-29'
;不会对此进行处理作为 varchar
,然后转换为 date
,则将其解释为 date
文字。)
通常,PostgreSQL可以自动推断类型,但是如果不能,则需要使用以下内容之一告诉您您希望将文字视为文本
:
-
文本'My New String'
(SQL标准文字语法) -
Cast('My New字符串'作为文本)
(SQL标准强制转换语法,但在这种情况下不是强制转换) -
'My New String': :text
(PostgreSQL非标准强制转换语法,但可读性强)
在您的情况下,错误消息 因此,您需要类似以下内容(我已删除了不必要的括号): I have a table with a field called tags which can contain any number of strings: I want to add a string to the tags field - but I can't seem to get the concat function to work for me. I've tried: and Any ideas? In PostgreSQL's type system, the literal Often, PostgreSQL can deduce the type automatically, but when it can't, you need to use one of the following to tell it that you want the literal to be treated as In your case , the error message You therefore need something like this (I've removed the unnecessary parentheses):
这篇关于如何使用字符串更新text []字段的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!操作符不是唯一的:未知|| text []
表示Postgres可以将文字解释为多种类型,每种类型都有其自己的 ||
更新页面设置标签=我的新字符串 :: text ||标签;
Table "public.page"
Column | Type | Modifiers
----------------------+--------------------------+----------------------------------
tags | text[] | not null default ARRAY[]::text[]
update page set tags=concat('My New String',tags);
ERROR: function concat(unknown, text[]) does not exist
LINE 1: update page set tags=concat('My New String',tags) where ...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
update page set tags=('My New String'||tags);
ERROR: operator is not unique: unknown || text[]
LINE 1: update page set tags = ('My New String' || tags) where w...
^
HINT: Could not choose a best candidate operator. You might need to add explicit type casts.
'My New String'
is not a varchar
or text
value, but a literal of type unknown
, which can be processed as any type. (For instance, the literal for a date
could be '2013-08-29'
; this would not be processed as a varchar
and then converted to date
, it would be interpreted as a "date
literal" at a very low level.)text
:
text 'My New String'
(SQL standard literal syntax)Cast('My New String' as text)
(SQL standard cast syntax, but not really a cast in this context)'My New String'::text
(PostgreSQL non-standard cast syntax, but quite readable)operator is not unique: unknown || text[]
is saying that there are multiple types that Postgres could interpret the literal as, each with their own definition of the ||
operator.update page set tags = 'My New String'::text || tags;