Postgresql autocomplete? [closed]

Question

Is there something that provides auto-completion for PostgreSQL? I'm looking for something similar to RedGate's SQLPrompt. Currently using pgAdmin III's query tool but willing to consider other query editors.

Moron

10 years later... There is plenty of DBA tools out there. Beyond personal tases, most of the IDEs have their own plugin for postgres, and some vendor (like IntelliJ with DataGrip or Microsoft with SQL Server Management Studio) also produced some really good commercial tool for database administration that works with Postgres and have really more than autocompletion, BUT I think one of the most complete free and opensource tool is DBEaver, which is based on Eclipse and I've been using in production for the last 5 years – Alister

Answer 1

psql has it. I'm using version 8.2.7 on server version 8.2.6

Mccord

Silly me. I never stopped to hit ctrl+space. – Moron

I am using psql 8.4.6 and server version 8.4 on Ubuntu. ctrl-space does not work for me, but ctrl-tab does. – Antineutrino

For mac users, readline library needs to be installed for autocomplete (BREW command: brew install readline) – While

psql has basic auto-completion. For example, if you type SEL and press tab, it will autocomplete to SELECT. However, it doesn't seem to be able to autocomplete column names. – Baronage

Not working on windows (dba.stackexchange.com/a/310774/206847) – Alister

CTL+space works quite nicely for pgAdmin query tool, but not for psql on windows – Broadcasting

Answer 2

You might like pgcli which provide completion for join and other nice features. However, it lacks some \* helpers like \dF....

cave> \d appellation
+-----------+---------+-----------------------------------------------------------+
| Column    | Type    | Modifiers                                                 |
|-----------+---------+-----------------------------------------------------------|
| id        | integer |  not null default nextval('appellation_id_seq'::regclass) |
| libelle   | text    |  not null                                                 |
| region_id | integer |                                                           |
+-----------+---------+-----------------------------------------------------------+
Indexes:
    "appellation_pkey" PRIMARY KEY, btree (id)
    "appellation_libelle_key" UNIQUE CONSTRAINT, btree (libelle)
Foreign-key constraints:
    "appellation_region_id_fkey" FOREIGN KEY (region_id) REFERENCES region(id) ON DELETE CASCADE
Referenced by:
    TABLE "vin" CONSTRAINT "vin_appellation_id_fkey" FOREIGN KEY (appellation_id) REFERENCES appellation(id) ON DELETE CASCADE

Time: 0.006s
cave> SELECT * FROM appellation JOIN  _
                                      region ON region.id = appellation.region_id     join      
                                      vin ON vin.appellation_id = appellation.id      join      

In this example two joins are proposed corresponding to foreign keys.

Shenika

Answer 3

Simple answer below:

I spent a long time trying to get tab-complete to work on the psql client AND the SQL shell (psql) client. Here's what you do.

Stop trying and use the client found here: pgcli

I installed using pip, opened the client using >pgcli -Upostgres, and entered the same password created with I installed Postgresql. Tab-completion works and I'm good to go.

Pesky

Welcome to SO! Please, before you post an answer, take a look if it is an existing one. (This option is already mention) – Jsandye

Answer 4

The support for autocomplete in NetBeans is amazing. Infinitely better than the basic psql imho. It understands table aliases so you can do something like:

select * from users u join posts p where p.AUTOCOMPLETE

No support for common table expressions sadly.

Kassi

breaking: Intellij supports CTEs and is simply better. I stand corrected. DataGrip is Intellij-lite if you only want the DB functionality. – Kassi

Answer 5

from help file:

To use autocomplete, begin typing your query; when you would like the Query editor to suggest object names or commands that might be next in your query, press the Control+Space key combination.

Punctilio

The help file is for pgAdmin not psql. But does work quite nicely in pgAdmin – Broadcasting

Answer 6

Autocompletion for keywords For keywords just type a few characters and press the Control + Space key combination.

Goldfish