StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You can query the catalog table <a href="http://www.postgresql.org/docs/current/interactive/catalog-pg-attribute.html" rel="nofollow noreferrer"><code>pg_attribute</code></a> to get a list of columns which are not defined <code>NOT NULL</code> and therefore <em>can</em> hold <code>NULL</code> values:</p> <pre><code>SELECT quote_ident(attname) AS column_can_be_null FROM pg_attribute WHERE attrelid = 'tbl'::regclass -- valid, visible table name AND attnum >= 1 -- exclude tableoid & friends AND NOT attisdropped -- exclude dropped columns AND NOT attnotnull -- exclude columns defined NOT NULL! ORDER BY attnum; </code></pre> <p>Where <code>tbl</code> is your (optionally schema-qualified) table name.</p> <p>Doesn't say there are any actual NULL values in the column. You'd have to test each column. Like this:</p> <h3>Full automation with plpgsql function</h3> <pre><code>CREATE OR REPLACE FUNCTION f_all_null_columns_of_tbl(_tbl regclass) RETURNS SETOF text AS $func$ DECLARE _row_ct bigint; -- count rows in table $1 _sql text; -- SQL string to test for NULL values _cols text[]; -- array of candidate column names _nulls bool[]; -- array of test results BEGIN EXECUTE 'SELECT count(*) FROM ' || _tbl INTO _row_ct; IF _row_ct = 0 THEN RAISE EXCEPTION 'Table % has no rows!', _tbl; -- pointless for empty table ELSE RAISE NOTICE '% rows in table %.', _row_ct, _tbl; END IF; SELECT INTO _sql, _cols 'SELECT ARRAY[' || string_agg('bool_and(' || col || ' IS NULL)', ', ') || '] FROM ' || _tbl , array_agg(col) FROM ( SELECT quote_ident(attname) AS col FROM pg_attribute WHERE attrelid = _tbl -- valid, visible table name AND attnum >= 1 -- exclude tableoid & friends AND NOT attisdropped -- exclude dropped columns AND NOT attnotnull -- exclude columns defined NOT NULL! ORDER BY attnum ) sub; EXECUTE _sql INTO _nulls; FOR i IN 1 .. array_upper(_cols, 1) LOOP IF _nulls[i] THEN -- column is NULL in all rows RETURN NEXT _cols[i]; END IF; END LOOP; RETURN; END $func$ LANGUAGE plpgsql; </code></pre> <p>Call:</p> <pre><code>SELECT f_all_null_columns_of_tbl('my_schema.my_table'); </code></pre> <p>Tested with Postgres 9.1 and 9.3.<br> This uses a number of advanced plpgsql features.</p> <p><a href="http://sqlfiddle.com/#!15/ec8ed/1" rel="nofollow noreferrer"><strong>SQL Fiddle.</strong></a></p> <p>Related answer building SQL code and executing it, with modern syntax:</p> <ul> <li><a href="https://stackoverflow.com/questions/10621897/replace-blank-spaces-with-null-values/10686513#10686513">Replace empty strings with null values</a> </li> </ul> <p>About traversing a record:</p> <ul> <li><a href="https://stackoverflow.com/questions/13065774/pl-pgsql-loop-through-columns-of-record/13079081#13079081">Loop through columns of RECORD</a></li> </ul>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload