转换成字符串在postgresql?

Question

我想将select statement的结果转换为字符串:

SELECT count(*) from pg_largeobject

这个查询的结果将是一个整数，我想把它转换成字符串。

示例:result is 12451 i want it as "12451"它的类型是string

我尝试了这些解决方案，但不工作:

https://www.postgresql.org/docs/9.3/functions-formatting.html

1 - to_char ()

to_char(SELECT count(*) from pg_largeobject, '')

但是它要求第二个参数的值我还不知道

2- cast(SELECT count(*) from pg_largeobject)为text;它给了我:

查询失败:ERROR:语法错误在&;cast&;或附近

Answer 1

可以使用

select count(*)::TEXT from pg_catalog.pg_am;
count 
-------
7

Answer 2

您应该能够使用::text语法将count(*)转换为文本:

postgres=# create extension lo;
CREATE EXTENSION
postgres=# SELECT count(*) from pg_largeobject;
count 
-------
0
(1 row)
postgres=# SELECT pg_typeof(count(*)) from pg_largeobject;
pg_typeof 
-----------
bigint
(1 row)
postgres=# SELECT pg_typeof(count(*)::text) from pg_largeobject;
pg_typeof 
-----------
text
(1 row)
postgres=# select version();
version                                                  
----------------------------------------------------------------------------------------------------------
PostgreSQL 11.11 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)

Answer 3

SELECT cast ( count(*)  as VARCHAR(10)) AS varchar_expression from pg_largeobject