156

Is there a way to show all the content inside a table by using psql command line interface?

I can use \list to show all the databases, \d to show all the tables, but how can I show all the data in a table?

pinckerman
  • 3,999
  • 6
  • 30
  • 41
Lisa
  • 2,439
  • 3
  • 22
  • 37

5 Answers5

247

Newer versions: (from 8.4 - mentioned in release notes)

TABLE mytablename;

Longer but works on all versions:

SELECT * FROM mytablename;

You may wish to use \x first if it's a wide table, for readability.

For long data:

SELECT * FROM mytable LIMIT 10;

or similar.

For wide data (big rows), in the psql command line client, it's useful to use \x to show the rows in key/value form instead of tabulated, e.g.

 \x
SELECT * FROM mytable LIMIT 10;

Note that in all cases the semicolon at the end is important.

Vignesh Raja
  • 6,851
  • 1
  • 28
  • 39
Craig Ringer
  • 283,586
  • 65
  • 635
  • 730
  • 1
    A useful addition to the above answer, for when dealing with larger tables, is to add `LIMIT 100` to your command, so you can view just the first 100 (or as many as you want) rows. – Matt Jul 06 '16 at 11:49
  • Only capital letters work for the SQL command. So "select * from ..." won't work. You must use "SELECT * FROM ..." (well, in version 9.1 anyway) – MacGyver Jan 28 '17 at 12:30
  • 2
    @MacGyver That has not been true of any even vaguely recent PostgreSQL version. I used Pg as far back as 7.2 and it didn't care about capital vs lowercase (except for identifier-quoting rules around mixed-case identifier names like `"MyTable"` vs `MyTable`). – Craig Ringer Feb 01 '17 at 07:15
  • That was the reason I came to this question because I simply could not display data. I had been using the lowercase letters. When I tried the uppercase, I got it to work, as odd as it sounds. Although I cannot reproduce now, I will keep my comments and come back if I find it failing again. – MacGyver Feb 01 '17 at 09:50
  • Although `TABLE mytablename;` works, I can't seem to find information about it in the documentation (I can't find it at all), can you provide me with a link or a way to find more info about it? @CraigRinger – Anton Kahwaji Apr 05 '19 at 11:35
  • 1
    @AntonKahwaji Its available from version 8.4. Please refer [Official Docs](https://www.postgresql.org/docs/8.4/sql-select.html#SQL-TABLE) – Vignesh Raja Dec 03 '19 at 08:09
  • @MacGyver my queries returned nothing because I was forgetting the semicolon at the end. Maybe you were running into this same issue? – scottysseus Dec 05 '19 at 19:48
  • For a long table data (> 500 rows) how do I view all of the rows in my gitbash terminal? When I do ``SELECT * FROM`` It only displays 30 odd rows and then there is a ``--More--`` comment at the bottom. – Amrit Raj Mar 19 '21 at 07:29
  • 1
    @AmritRaj You are using a pager. See the documentation for `psql`. Typically the space or enter key advances the pager. You can disable use of the pager in `psql` per the documentation. – Craig Ringer Mar 23 '21 at 05:33
24

Step 1. Check the display mode is "on" by using

\x

Step 2. Don't forget the ;

I tried for fifteen minutes just because I forgot the semicolon.

AND USE UPPERCASE ENGLISH.

TABLE users;

And you will get something like

enter image description here

Zan Zas
  • 271
  • 2
  • 5
13

On Windows use the name of the table in quotes: TABLE "user"; or SELECT * FROM "user";

israteneda
  • 614
  • 1
  • 11
  • 21
2

you should use quotes

example =>

1) \c mytablename
2) SELECT * FROM "mytablename";  OR TABLE "mytablename";
Saeed
  • 2,748
  • 5
  • 29
  • 46
luka
  • 49
  • 2
0

postgres commande line

  1. to show databases : \l
  2. to show tables : \dt
  3. to show data in table x : SELECT * FROM "x";
  4. to exit : \q
Elhem Nouri
  • 11
  • 3