Truncating display by default in postgres psql select statements

Question

I have a table with a long text column. I'd like to be able to select all of the columns but limit the text column without needing to write every column.

select * from resources;

Produces an output that is too long to display correctly in psql. I can get something to show up by using substr() or left() on the long column, but then I need to specify each column.

select id, left(data, 50), file_format_version, ... from resources;

Is there a way that I can just get psql to truncate long columns by default when I query the first select * from resources?

Answer 1

There is no way with built-in options of psql that I would know of.
You can achieve your goal with a function like @Drazen suggested - just much simpler:

CREATE OR REPLACE FUNCTION f_trunc_columns(_tbl anyelement, _len int = 25)
  RETURNS SETOF anyelement AS
$func$
DECLARE
   _typ  CONSTANT regtype[] := '{bpchar, varchar}';  -- types to shorten
BEGIN
   RETURN QUERY EXECUTE (
   SELECT format('SELECT %s FROM %s'
               , string_agg(CASE WHEN a.atttypid = 'text'::regtype  -- simple case text
                              THEN format('left(%I, %s)', a.attname, _len)
                            WHEN a.atttypid = ANY(_typ)             -- other short types
                              THEN format('left(%I::text, %s)::%s'
                                 , a.attname, _len, format_type(a.atttypid, a.atttypmod))
                            ELSE quote_ident(a.attname) END         -- rest
                          , ', ' ORDER BY a.attnum)
               , pg_typeof(_tbl))
   FROM   pg_attribute a
   WHERE  a.attrelid = pg_typeof(_tbl)::text::regclass
   AND    NOT a.attisdropped  -- no dropped (dead) columns
   AND    a.attnum > 0        -- no system columns
   );
END
$func$  LANGUAGE plpgsql;

Call examples:

SELECT * FROM f_trunc_columns(NULL::my_table);
SELECT * FROM f_trunc_columns(NULL::"MySchema"."My_funny_tbl", 11);

SQL Fiddle.

Notes

• Works for any table with columns of any data type.

• This builds and executes a query of the form:

  SELECT "FoO_id", left(c_text, 11), left(c_vc, 11)::character varying
  FROM   "FoO";
  

• It shortens only columns of chosen data types and leaves others alone. I included basic character types:
  bpchar is the internal name for character and all variants.
  varchar is the internal name for character varying and all variants.
  Extend to your needs.

• The function returns original column names and data types for all columns. I cast short columns to text before feeding to left(), which returns text, so text columns don't need another cast. All other shortened types need a cast back to the original type. Some types break if you truncate! So this does not work for all types.

• You can append LIMIT n to the function call, but the function can easily be extended with a built-in LIMIT - which is much more efficient for big tables, since the query inside the plpgsql function is planned independently.

• Performance is not much worse than a plain SELECT * FROM tbl - except for said LIMIT case or other cases where you nest the function. Set-returning PL/pgSQL functions are generally best not nested:

  • PostgreSQL Stored Procedure Performance

• I built in a default max. length of 25 characters, pass a custom length as 2nd parameter or adapt the default in the function header to your needs.

• This function is safe against possible SQL injection attacks via maliciously crafted identifiers.

Related answers with more explanation and links:

• Replace empty strings with null values
• Refactor a PL/pgSQL function to return the output of various SELECT queries
• Table name as a PostgreSQL function parameter
• Postgres data type cast
• Query the schema details of a table in PostgreSQL?
• How to check if a table exists in a given schema

pgAdmin

... has the feature you are asking for, btw (for all columns):