搜索表中的所有列 [英] Searching through all columns in a table
问题描述
我有一个包含300多个列的表,其中许多列中没有数据。我可以使用查询来查找这些列的名称,以便为表删除它们。另外,如果这很重要,我也会在redshift服务器上使用PostgreSQL数据库
I have a table with 300+ columns, many of these columns have no data in them. is there a query I can use to find out the names of these columns so I can remove them for the table. Also I am using a postgresql database on a redshift server if that matters
推荐答案
让我们说您的表声明为
create table t as (x int, y varchar, z numeric);
首先,我们将表格的行转换为jsonb。很简单:
First of all lets convert table's rows to jsonb. It is simple:
select to_jsonb(t.*) from t;
结果(用于测试数据)
to_jsonb
--------------------------
{"x":1,"y":"a","z":null}
{"x":2,"y":"b","z":null}
接下来,我们将使用另一个json函数将这些结果转换为(key,value)之类的行:
Next we will convert those result to rows like (key,value) using another json function:
select jsonb_each(to_jsonb(t.*)) from t;
结果:
jsonb_each
-------------
(x,1)
(y,"""a""")
(z,null)
(x,2)
(y,"""b""")
(z,null)
这几乎是我们所需要的。下一步:
It is almost what we need. Next step:
select (w).key, (w).value from (select jsonb_each(to_jsonb(t.*)) as w from t) tt;
结果
key | value
-----+-------
x | 1
y | "a"
z | null
x | 2
y | "b"
z | null
此处我们使用 [w)
最后一步:
select
(w).key
from
(select jsonb_each(to_jsonb(t.*)) as w from t) tt
group by
(w).key
having
count(*) filter (where((w).value != 'null')) = 0;
结果
key
-----
z
尝试使用最后一个查询只是将 t
替换为表名。
Try to use the last query just replacing t
to your table name.
更新:
也可以尝试使用PostgreSQL统计信息:
Also you can try use PostgreSQL statistics info:
analyse yourtable;
select
pg_class.relname,
pg_attribute.attname,
pg_statistic.stanullfrac
from
pg_class join
pg_statistic on (pg_class.oid = pg_statistic.starelid) join
pg_attribute on (pg_class.oid = pg_attribute.attrelid and pg_statistic.staattnum = pg_attribute.attnum)
where
pg_class.relname = 'yourtable';
在 stanullfrac
列中,您会看到每个表的列的空值的相对数量,其中1表示所有nul(但我不确定它的准确性如何)
In the stanullfrac
column you will see the relative amount of nulls for each table's column where 1 means all nuls (but I am not sure how it accurate)
这篇关于搜索表中的所有列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!