Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>To my knowledge there is no built-in function to replace empty strings across all columns of a table. You can write a <a href="http://www.postgresql.org/docs/current/interactive/plpgsql.html" rel="nofollow noreferrer"><strong>plpgsql function</strong></a> to take care of that.</p> <p>The following function replaces empty strings <strong>in all basic character-type columns of a given table</strong> with <code>NULL</code>. You can then cast to <code>integer</code> if the remaining strings are valid number literals.</p> <pre><code>CREATE OR REPLACE FUNCTION f_empty2null(_tbl regclass, OUT updated_rows int) AS $func$ DECLARE -- basic char types, possibly extend with citext, domains or custom types: _typ CONSTANT regtype[] := '{text, bpchar, varchar, \"char\"}'; _sql text; BEGIN SELECT INTO _sql -- build command format('UPDATE %s SET %s WHERE %s' , _tbl , string_agg(format($$%1$s = NULLIF(%1$s, '')$$, col), ', ') , string_agg(col || $$ = ''$$, ' OR ')) FROM ( SELECT quote_ident(attname) AS col FROM pg_attribute WHERE attrelid = _tbl -- valid, visible, legal table name AND attnum &gt;= 1 -- exclude tableoid &amp; friends AND NOT attisdropped -- exclude dropped columns AND NOT attnotnull -- exclude columns defined NOT NULL! AND atttypid = ANY(_typ) -- only character types ORDER BY attnum ) sub; -- Test -- RAISE NOTICE '%', _sql; -- Execute IF _sql IS NULL THEN updated_rows := 0; -- nothing to update ELSE EXECUTE _sql; GET DIAGNOSTICS updated_rows = ROW_COUNT; -- Report number of affected rows END IF; END $func$ LANGUAGE plpgsql; </code></pre> <p>Call:</p> <pre><code>SELECT f_empty2null('mytable'); SELECT f_empty2null('myschema.mytable'); </code></pre> <p>To also get the column name <code>updated_rows</code>: </p> <pre><code>SELECT * FROM f_empty2null('mytable'); </code></pre> <p><a href="http://sqlfiddle.com/#!15/bff08/1" rel="nofollow noreferrer"><strong>SQL Fiddle.</strong></a></p> <h3>Major points</h3> <ul> <li><p>Table name has to be valid and visible and the calling user must have all necessary privileges. If any of these conditions are not met, the function will do nothing - i.e. nothing can be destroyed, either. I cast to the <a href="http://www.postgresql.org/docs/current/interactive/datatype-oid.html" rel="nofollow noreferrer">object identifier type <code>regclass</code></a> to make sure of it.<br> The table name can be supplied as is (<code>'mytable'</code>), then the <a href="https://stackoverflow.com/questions/9067335/how-to-create-table-inside-specific-schema-by-default-in-postgres/9067777#9067777"><code>search_path</code></a> decides. Or schema-qualified to pick a certain schema (<code>'myschema.mytable'</code>).</p></li> <li><p>Query the system catalog to get all (<a href="http://www.postgresql.org/docs/current/interactive/datatype-character.html" rel="nofollow noreferrer">character-type</a>) columns of the table. The provided function uses these basic <a href="http://www.postgresql.org/docs/current/interactive/datatype-character.html" rel="nofollow noreferrer">character types</a>: <code>text</code>, <code>bpchar</code>, <code>varchar</code>, <code>"char"</code>. Only relevant columns are processed.</p></li> <li><p>Use <a href="http://www.postgresql.org/docs/current/interactive/plpgsql-statements.html#PLPGSQL-QUOTE-LITERAL-EXAMPLE" rel="nofollow noreferrer"><code>quote_ident()</code></a> or <code>format()</code> to sanitize column names and <a href="https://stackoverflow.com/questions/10705616/table-name-as-a-postgresql-function-parameter/10711349#10711349">safeguard against SQLi</a>.</p></li> <li><p>The updated version uses the basic SQL aggregate function <code>string_agg()</code> to build the command string without looping, which is simpler and faster. And more elegant. :)</p></li> <li><p>Has to use <a href="http://www.postgresql.org/docs/current/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN" rel="nofollow noreferrer">dynamic SQL with <code>EXECUTE</code></a>.</p></li> <li><p>The updated version excludes columns defined <code>NOT NULL</code> and only updates each row <strong><em>once in a single statement</em></strong>, which is much faster for tables with multiple character-type columns.</p></li> <li><p>Should work with any modern version of PostgreSQL. Tested with Postgres 9.1, 9.3 and 9.5.</p></li> </ul>
    singulars
    1. This table or related slice is empty.
    1. POReplace empty strings with null values
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USCommunity
    1. USErwin Brandstetter
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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