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):

Answer 2

Not really psql, but everything is possible with code :)

This one turned out to be quite tricky, I'm not happy with the final solution as it is a bit clunky, but it gets the job done, still it should be treated as a proof of concept. There is always room to improve and streamline a bit :)

Anyway, as there is nothing built in and I don't like the idea of doing this outside of postgres, ie. PAGER output piped to awk than less and finally to you :) I went with functions.

Python is my weapon of choice most of the time, so I made a plpython function that does the truncating, and a plpgsql wrapper so that it can be nicely called, using all the lovely things SQL offers.

Lets start with a wrapper function:

create or replace function wrapper(t text, x anyelement, l integer) 
returns setof anyelement as
$$
  begin
  -- call the logic bearing function
  execute format($f$select truncate('%1$s', %2$s)$f$, t, l);
  -- return results
  return query execute format($f$select * from trunc_%1$s$f$, t);
  end;
$$ language plpgsql;

As you can see it is declared with polymorphic input so that it can work on all tables you give it, or rather it can return the same type of table you feed to it, keeping all your constraints, indexes and so on (this is accomplished by the plpython function)... So without further ado, let's see it:

create or replace function truncate(tbl text, l integer) returns void as
$$
  import arrow
  import json

  # Drops if needed and creates a table to hold your truncated results
  plpy.execute('drop table if exists trunc_{0}'.format(tbl))
  plpy.execute('create table trunc_{0} ( like {0} including defaults including    constraints including indexes )'.format(tbl))
  r = plpy.execute("select * from {}".format(tbl))
  for i in xrange(r.nrows()):
  # These two lists help us get the order of columns and values right
  ins = []
  cols = []
  for x in r[i]:
    if type(r[i][x]) is str:

      '''
      Two booleans below are used for type checking, I had an example table
      with some json, timestamps and integers lying around so used that for
      testing, this way we will truncate only text-like fields, but not json. 
      '''
      ts = False
      js = False

      '''
      Check if we can parse date or json, note that if you have a valid json
      stored in a text or varchar field it will still get marked as json, by 
      digging in the information_schema you could easily add better type   
      checking here.
      '''
      try: 
        arrow.get(r[i][x])
        ts = True
      except (arrow.parser.ParserError, UnicodeDecodeError):
        pass
      try:
        json.loads(r[i][x])
        js = True 
      except ValueError:
        pass
      # If it is a string and its not json or timestamp lets truncate it           
      # whatever you specify as the last argument in the call, `l`  
      if not ts and not js:
        r[i][x] = r[i][x][:l]
    # Additional check for nulls and ints, and appropriate appends
    if r[i][x] is None:
      ins.append("null")
    elif r[i][x] is int:
      ins.append(r[i[x]])
    ```
    Finally we can append our values to insert, this is done inefficiently as 
    each row will be inserted individually, again treat this as a POC, better 
    would be to first form a list of all inserts and then fire them in one statement.
    ```
    else:
      ins.append("'{}'".format(r[i][x]))
    cols.append(x)
  q = 'insert into trunc_{0}({2}) values({1})'.format(tbl, ','.join(ins), ','.join(cols))
  plpy.execute(q)
$$ language plpythonu;

Provided I managed to format this correctly you should be able to call this by running:

select * from wrapper(resources, null::resources, 50);

Again the clunkiness shows it ugly face, so you give the table name, table column types so it knows what to return and character limit for truncation, you should be able to use WHERE, GROUP BY and similar without any problems.

Obvious problem is the performance as you will essentially be reinserting your entire table and this could be problematic, but that at least is easily solvable.

As an afterthought, in case you are unfamiliar with plpython, you enable it by running create extension plpythonu from within psql. json module comes built in to python, whereas arrow can be installed by running pip install arrow from your shell of choice, provided pip and python are in order, in case they are not Google is your friend ;)

Hope this gets you at least part of the way to where you want to be :)