CODEWITHSUNDEEP
sqlpostgresql
kernel-trick
kernel-trick

Reputation: 11

Remove the parenthesis in the output

Is there a way to do this except without the parenthesis in the output? The API that takes the output can't deal with them.

select a,b,c,d,e from (select n_dead_tup from pg_stat_user_tables where relname = 'table1') as a, 
(select n_dead_tup from pg_stat_user_tables where relname = 'table2') as b, 
(select n_dead_tup from pg_stat_user_tables where relname = 'table3') as c, 
(select n_dead_tup from pg_stat_user_tables where relname = 'table4') as d, 
(select n_dead_tup from pg_stat_user_tables where relname = 'table5') as e;

This output is something like this:

 a        |     b     |    c     |     d     |     e     
----------+-----------+----------+-----------+-----------
(7781834) | (7781834) | (483464) | (1426296) | (5452406)

Upvotes: 1

Views: 63

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

This is happening because you are selecting tuples via the table name rather than columns. You will see this if you select a.n_dead_tup instead. You can repeat this for all the tables, if you like, but I prefer the solutions below.

I would suggest a simple group by:

select relname, n_dead_tup
from pg_stat_user_tables
where relname in ('table1', 'table2', 'table3', 'table4', 'table5');

Or conditional aggregation:

select max(n_dead_tup) filter (relname = 'table1') as table1,
       max(n_dead_tup) filter (relname = 'table2') as table2,
       max(n_dead_tup) filter (relname = 'table3') as table3,
       max(n_dead_tup) filter (relname = 'table4') as table4,
       max(n_dead_tup) filter (relname = 'table5') as table5
from pg_stat_user_tables
where relname in ('table1', 'table2', 'table3', 'table4', 'table5')

Upvotes: 2

Whodi
Whodi

Reputation: 36

Regex replace would probably be the simplest approach: 

regexp_replace(a, '\(|\)', '', 'g');

You need the pipe in there for either left or right paren, and as a result, you'll need to escape the parens with the slashes. The g is a flag to remove all examples of them from the string

Upvotes: 0

Related Questions