How to show data in a table by using psql command line interface?

Question

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?

http://stackoverflow.com/questions/9178406/postgresql-command-to-see-the-table-data this might be helpful to you. — Wanna Coffee, Sep 25 '14 at 14:06

score 281 · Accepted Answer · edited Dec 03 '19 at 08:14

281

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.

edited Dec 03 '19 at 08:14

Vignesh Raja

7,927
1
33
42

answered Sep 25 '14 at 14:13

Craig Ringer

307,061
76
688
778

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) – JustBeingHelpful 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. – JustBeingHelpful 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
Is there a way to change display mode for one query only, temporarily? – Rafs Jul 28 '23 at 10:55
@Rafs Not conveniently. You can do something like `\x \\ select 1 \g \x` but it doesn't seem to play nice with non-interactive invocations like `psql -c` – Craig Ringer Jul 30 '23 at 21:53

Zan Zas · Answer 2 · 2023-08-03T13:34:01.650

46

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.

You can choose to use uppercase for great format looking.

TABLE users;

And you will get something like

edited Aug 03 '23 at 13:34

answered Mar 26 '21 at 03:54

Zan Zas

501
5
6

Why is UPPERCASE needed? – Rafs Jul 28 '23 at 10:56
just look better – Zan Zas Jul 30 '23 at 10:31
I agree, but it won't affect the functionality, hence my question. – Rafs Aug 02 '23 at 15:36
Ok, I fixed it. – Zan Zas Aug 03 '23 at 13:35

israteneda · Answer 3 · 2021-03-23T21:24:41.973

15

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

edited Mar 23 '21 at 21:24

answered Mar 21 '20 at 04:33

israteneda

705
1
14
26

I don't understand what benefit the "" will give when there are no spaces or capitalisation in the table names – Rafs Jul 28 '23 at 10:57

score 6 · Answer 4 · edited Feb 19 '22 at 08:12

6

you should use quotes

example =>

1) \c mytablename
2) SELECT * FROM "mytablename";  OR TABLE "mytablename";

edited Feb 19 '22 at 08:12

Saeed

3,294
5
35
52

answered Dec 14 '21 at 11:18

Luka Akhalbedashvili

127
2
3

score 4 · Answer 5 · answered Apr 13 '22 at 08:18

4

postgres commande line

to show databases : \l
to show tables : \dt
to show data in table x : SELECT * FROM "x";
to exit : \q

answered Apr 13 '22 at 08:18

Elhem Nouri

61
4

score 3 · Answer 6 · answered Jun 29 '22 at 13:12

3

If you use schemas, the following will be correct:

SELECT * FROM "schema-name"."table-name";

answered Jun 29 '22 at 13:12

t7e

322
1
3
9

How to show data in a table by using psql command line interface?

6 Answers6

Linked