Monday, November 15, 2010

Productivity tip: psql tab completion and history

Like many other shells, psql has tab completion. The ability to tab complete commands and relations such as tablenames can be a huge productivity boost.

Type 'S' and hit tab and the commands starting with 'S' appear:

SuperFoods=# S
SELECT     SET        SHOW       START      SAVEPOINT 

Type the next letter say 'e' and only the subset of commands appear:

SuperFoods=# SE

(psql automatically changes the case to upper case on these primary commands and this can take an extra tab which is a little annoying and unnecessary.)

This continues until there is no ambiguity and the command completes.

Type 'set sea' and tab and it completes to

SuperFoods-# set search_path

Type space and then tab again and it completes to

SuperFoods-# set search_path TO

and so on.

The same behavior is true with tablenames. If you have a table called 'employees' and no other table starts with 'e' then tab will complete the whole tablename. If there are two tables starting with 'e' then psql will list those tables in the same manner as the commands above.

It is not always obvious what will and will not complete . If one types 'SEL' and tab it completes to 'SELECT'. Then typing ' * FR' and then tab one might expect it to complete the FROM but it does not. The psql documentation does however state that "the completion logic makes no claim to be an SQL parser".

If you have a blank line and type tab twice it brings up the primary commands:

ABORT        ALTER        ANALYZE      BEGIN        CHECKPOINT   CLOSE        CLUSTER      COMMENT      COMMIT       COPY        
CREATE       DEALLOCATE   DECLARE      DELETE FROM  DISCARD      DO           DROP         END          EXECUTE      EXPLAIN     
FETCH        GRANT        INSERT       LISTEN       LOAD         LOCK         MOVE         NOTIFY       PREPARE      REASSIGN    
REINDEX      RELEASE      RESET        REVOKE       ROLLBACK     SAVEPOINT    SELECT       SET          SHOW         START       
TABLE        TRUNCATE     UPDATE       VACUUM       VALUES       WITH         UNLISTEN    

Finally, like other shells one can scroll through the command history using page up/down or cursor up/down keys. History retains up to the last 500 commands.

1 comment: