convert int to string in postgresql?

Question

I want to convert result of select statement to string:

SELECT count(*) from pg_largeobject

the result of this query will be an integer, i wanna convert it to string.

Ex: result is 12451 i want it as "12451" and its type is string .

i tried these solutions but not work:

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

1- to_char()

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

but it require in second parameter the value that i dont know yet

2- cast(SELECT count(*) from pg_largeobject) as text; it gives me :

Query failed: ERROR: syntax error at or near "cast"

SELECT count(*)::varchar from pg_largeobject, where '::varchar' is shorthand for CAST to varchar. — Adrian Klaver
– Adrian Klaver, Commented Oct 21, 2021 at 21:07
I don't see why not? What is the command you are using and the error message? — Adrian Klaver
– Adrian Klaver, Commented Oct 21, 2021 at 21:23

kometen · Accepted Answer · 2021-10-21 21:15:49Z

5

You can use

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

edited Oct 21, 2021 at 21:15

answered Oct 21, 2021 at 21:08

kometen

8,1406 gold badges48 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Leearn2303 Over a year ago

hi man, it still consider the result as int , not string .

kometen Over a year ago

Must be the way you parse it. Output is left-aligned when using ::text.

richyen · Accepted Answer · 2021-10-21 21:13:44Z

You should be able to cast count(*) as text with ::text syntax:

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)

metehanX · Accepted Answer · 2021-10-21 21:43:24Z

1

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

answered Oct 21, 2021 at 21:43

metehanX

365 bronze badges

Collectives™ on Stack Overflow

convert int to string in postgresql?

3 Answers 3

2 Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Related