SHOW TABLES

Simon Riggs <simon@2ndQuadrant.com> writes:

The biggest turn off that most people experience when using PostgreSQL
is that psql does not support memorable commands.

I would like to implement the following commands as SQL, allowing them
to be used from any interface.

SHOW TABLES
SHOW COLUMNS
SHOW DATABASES

This has been discussed before, and rejected before. Please see
archives.

regards, tom lane

On Thu, 2010-07-15 at 11:05 -0400, Tom Lane wrote:

Simon Riggs <simon@2ndQuadrant.com> writes:

The biggest turn off that most people experience when using PostgreSQL
is that psql does not support memorable commands.

I would like to implement the following commands as SQL, allowing them
to be used from any interface.

SHOW TABLES
SHOW COLUMNS
SHOW DATABASES

This has been discussed before, and rejected before. Please see
archives.

Many years ago. I think it's worth revisiting now in light of the number
of people now joining the PostgreSQL community and the greater
prevalence other ways of doing it. The world has changed, we have not.

I'm not proposing any change in function, just a simpler syntax to allow
the above information to be available, for newbies.

Just for the record, I've never ever met anyone that said "Oh, this \d
syntax makes so much sense. I'm a real convert to Postgres now you've
shown me this". The reaction is always the opposite one; always
negative. Which detracts from our efforts elsewhere.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Training and Services

On 15 July 2010 16:20, Simon Riggs <simon@2ndquadrant.com> wrote:

On Thu, 2010-07-15 at 11:05 -0400, Tom Lane wrote:

Simon Riggs <simon@2ndQuadrant.com> writes:

The biggest turn off that most people experience when using PostgreSQL
is that psql does not support memorable commands.

I would like to implement the following commands as SQL, allowing them
to be used from any interface.

SHOW TABLES
SHOW COLUMNS
SHOW DATABASES

This has been discussed before, and rejected before.  Please see
archives.

Many years ago. I think it's worth revisiting now in light of the number
of people now joining the PostgreSQL community and the greater
prevalence other ways of doing it. The world has changed, we have not.

I'm not proposing any change in function, just a simpler syntax to allow
the above information to be available, for newbies.

Just for the record, I've never ever met anyone that said "Oh, this \d
syntax makes so much sense. I'm a real convert to Postgres now you've
shown me this". The reaction is always the opposite one; always
negative. Which detracts from our efforts elsewhere.

--

Looks like the last time this was discussed, there wasn't any clear
conclusion. Someone created a patch and it's still on the TODO list:
http://archives.postgresql.org/pgsql-hackers/2010-01/msg01845.php

Thom

On Jul 15, 2010, at 5:20 PM, Simon Riggs wrote:

On Thu, 2010-07-15 at 11:05 -0400, Tom Lane wrote:

Simon Riggs <simon@2ndQuadrant.com> writes:

The biggest turn off that most people experience when using PostgreSQL
is that psql does not support memorable commands.

I would like to implement the following commands as SQL, allowing them
to be used from any interface.

SHOW TABLES
SHOW COLUMNS
SHOW DATABASES

This has been discussed before, and rejected before. Please see
archives.

Many years ago. I think it's worth revisiting now in light of the number
of people now joining the PostgreSQL community and the greater
prevalence other ways of doing it. The world has changed, we have not.

I'm not proposing any change in function, just a simpler syntax to allow
the above information to be available, for newbies.

Just for the record, I've never ever met anyone that said "Oh, this \d
syntax makes so much sense. I'm a real convert to Postgres now you've
shown me this". The reaction is always the opposite one; always
negative. Which detracts from our efforts elsewhere.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Training and Services

simon is absolutely right here.
we should not mind being a little more user friendly in this area.
many people are simply used to this kind of stuff.

remember when you rejected something the last time (not necessarily software). was ist because you could not make it work in 2 min or was it because you did not like something else?
do you reject buying a car because of a non obvious screw in the engine or because "it somehow does not feel right"?

simon made an important point and i can simply agree - regardless of whether it has been discussed before or not.
if you die a beautiful death you are still dead after all.

regards,

hans

--
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt
Web: http://www.postgresql-support.de

On Thu, Jul 15, 2010 at 17:30, Thom Brown <thombrown@gmail.com> wrote:

On 15 July 2010 16:20, Simon Riggs <simon@2ndquadrant.com> wrote:

On Thu, 2010-07-15 at 11:05 -0400, Tom Lane wrote:

Simon Riggs <simon@2ndQuadrant.com> writes:

The biggest turn off that most people experience when using PostgreSQL
is that psql does not support memorable commands.

I would like to implement the following commands as SQL, allowing them
to be used from any interface.

SHOW TABLES
SHOW COLUMNS
SHOW DATABASES

This has been discussed before, and rejected before.  Please see
archives.

Many years ago. I think it's worth revisiting now in light of the number
of people now joining the PostgreSQL community and the greater
prevalence other ways of doing it. The world has changed, we have not.

I'm not proposing any change in function, just a simpler syntax to allow
the above information to be available, for newbies.

Just for the record, I've never ever met anyone that said "Oh, this \d
syntax makes so much sense. I'm a real convert to Postgres now you've
shown me this". The reaction is always the opposite one; always
negative. Which detracts from our efforts elsewhere.

--

Looks like the last time this was discussed, there wasn't any clear
conclusion.  Someone created a patch and it's still on the TODO list:
http://archives.postgresql.org/pgsql-hackers/2010-01/msg01845.php

That one is about:
a) doing it in psql., not the backend
b) not actually implementing the command, but implementing hints for
the user telling them which is the correct command

Is there an actual common use-case for having these commands available
for *non-psql* interfaces?

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

On Thu, 2010-07-15 at 16:20 +0100, Simon Riggs wrote:

On Thu, 2010-07-15 at 11:05 -0400, Tom Lane wrote:

Simon Riggs <simon@2ndQuadrant.com> writes:

The biggest turn off that most people experience when using PostgreSQL
is that psql does not support memorable commands.

I would like to implement the following commands as SQL, allowing them
to be used from any interface.

SHOW TABLES
SHOW COLUMNS
SHOW DATABASES

This has been discussed before, and rejected before. Please see
archives.

Many years ago. I think it's worth revisiting now in light of the number
of people now joining the PostgreSQL community and the greater
prevalence other ways of doing it. The world has changed, we have not.

I'm not proposing any change in function, just a simpler syntax to allow
the above information to be available, for newbies.

Just for the record, I've never ever met anyone that said "Oh, this \d
syntax makes so much sense. I'm a real convert to Postgres now you've
shown me this". The reaction is always the opposite one; always
negative. Which detracts from our efforts elsewhere.

I have to agree with Simon here. \d is ridiculous for the common user.

SHOW TABLES, SHOW COLUMNS makes a lot of sense. Just has something like
DESCRIBE TABLE foo makes a lot more sense than \d.

Sincerely,

Joshua D. Drake

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering

On Thu, 2010-07-15 at 17:38 +0200, Magnus Hagander wrote:

Looks like the last time this was discussed, there wasn't any clear
conclusion. Someone created a patch and it's still on the TODO list:
http://archives.postgresql.org/pgsql-hackers/2010-01/msg01845.php

That one is about:
a) doing it in psql., not the backend
b) not actually implementing the command, but implementing hints for
the user telling them which is the correct command

Is there an actual common use-case for having these commands available
for *non-psql* interfaces?

Yes. We should provide a single, well described grammar for interacting
with objects in the database regardless of client. I should be able to
open ANY SQL terminal, and type SHOW ME THE MONEY and have Benjamins
fall out.

(O.k. I will take Euros too).

JD

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering

Thom Brown wrote:

Looks like the last time this was discussed, there wasn't any clear
conclusion. Someone created a patch and it's still on the TODO list:
http://archives.postgresql.org/pgsql-hackers/2010-01/msg01845.php

This is not at all what Simon proposed. He wants to make it a backend
command, not a psql command.

I don't have a horse in the race, particularly. If we really want more
utility commands, my preference would be to concentrate on those that
are hard rather than those that could be easily done, e.g. a command
that would give you the SQL necessary to create a given object.

cheers

andrew

Le 15/07/2010 17:48, Joshua D. Drake a écrit :

On Thu, 2010-07-15 at 16:20 +0100, Simon Riggs wrote:

On Thu, 2010-07-15 at 11:05 -0400, Tom Lane wrote:

Simon Riggs <simon@2ndQuadrant.com> writes:

The biggest turn off that most people experience when using PostgreSQL
is that psql does not support memorable commands.

I would like to implement the following commands as SQL, allowing them
to be used from any interface.

SHOW TABLES
SHOW COLUMNS
SHOW DATABASES

This has been discussed before, and rejected before. Please see
archives.

Many years ago. I think it's worth revisiting now in light of the number
of people now joining the PostgreSQL community and the greater
prevalence other ways of doing it. The world has changed, we have not.

I'm not proposing any change in function, just a simpler syntax to allow
the above information to be available, for newbies.

Just for the record, I've never ever met anyone that said "Oh, this \d
syntax makes so much sense. I'm a real convert to Postgres now you've
shown me this". The reaction is always the opposite one; always
negative. Which detracts from our efforts elsewhere.

I have to agree with Simon here. \d is ridiculous for the common user.

SHOW TABLES, SHOW COLUMNS makes a lot of sense. Just has something like
DESCRIBE TABLE foo makes a lot more sense than \d.

And would you add the complete syntax? I mean:

SHOW [OPEN] TABLES [FROM db_name] [LIKE 'pattern']

I'm wondering what one can do with the [FROM db_name] clause :)

--
Guillaume
http://www.postgresql.fr
http://dalibo.com

On 15 July 2010 16:52, Andrew Dunstan <andrew@dunslane.net> wrote:

Thom Brown wrote:

Looks like the last time this was discussed, there wasn't any clear
conclusion.  Someone created a patch and it's still on the TODO list:
http://archives.postgresql.org/pgsql-hackers/2010-01/msg01845.php

This is not at all what Simon proposed. He wants to make it a backend
command, not a psql command.

My bad. But I find the following slightly odd:

"The biggest turn off that most people experience when using PostgreSQL
is that psql does not support memorable commands.

I would like to implement the following commands as SQL, allowing them
to be used from any interface."

If it's only a psql problem, why implement it as SQL? Is it just so
we're not adding keywords specifically to psql? In that case, it
shouldn't support QUIT.

But I agree with the principal of improving usability. There's the
issue of schema with SHOW TABLES though. It would either have to show
tables and their associated schema in separate columns, or have an
extended "SHOW TABLES IN [SCHEMA] my_schema" syntax.

I personally think LIST <object type> makes more sense, although I
guess the point is that SHOW would be familiar to MySQL defectors ;)

Thom

On Thu, 2010-07-15 at 18:02 +0200, Guillaume Lelarge wrote:

I have to agree with Simon here. \d is ridiculous for the common user.

SHOW TABLES, SHOW COLUMNS makes a lot of sense. Just has something like
DESCRIBE TABLE foo makes a lot more sense than \d.

And would you add the complete syntax? I mean:

SHOW [OPEN] TABLES [FROM db_name] [LIKE 'pattern']

I'm wondering what one can do with the [FROM db_name] clause :)

Well I hadn't thought it out fully. I was just shutting down somebody
elses idea that the feature had no legs. Which is obviously, not true.

Let the discussion bloom :D

JD

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering

As a common user -- probably a bit more than that now -- I'd have to say my
reaction to '\d' instead of 'SHOW DATABASES;' was more of a "meh" moment for
me. Furthermore, '\d' is much quick to type than 'SHOW DATABASES;', and much
less likely to suffer typos.

As for '\d' not being memorable: It sure as heck is! I think the real problem
here is that there's a little effort required in learning a new set of
commands when switching from a competing database.

'SHOW . . . .' cannot be implemented in psql alone. It would have to supported
in the backend. So that other drivers are able to understand it as well. If it
implemented in psql only, we will be bombarded with "I don't understand this!
'SHOW . . . .' works when I do it at the command line, but not in my script!
WTF?'

The best solution is to offer a hint to the user in psql when they submit
'SHOW . . . .' with a response like: SHOW . . . . is not a valid command.
Perhaps you mean \d . . . .

Sincerely,
Aaron

On Thu, 15 Jul 2010, Thom Brown wrote:

If it's only a psql problem, why implement it as SQL? Is it just so
we're not adding keywords specifically to psql? In that case, it
shouldn't support QUIT.

Personally, I think this is somethign that should go into the backend ...
I'd like to be able to write perl scripts that talk to the backend without
having to remember all the various system tables I need to query / join to
get the same results as \d gives me in psql ... same for any interface
language, really ...

----
Marc G. Fournier Hub.Org Hosting Solutions S.A.
scrappy@hub.org http://www.hub.org

Yahoo:yscrappy Skype: hub.org ICQ:7615664 MSN:scrappy@hub.org

On 15 July 2010 17:07, Marc G. Fournier <scrappy@hub.org> wrote:

On Thu, 15 Jul 2010, Thom Brown wrote:

If it's only a psql problem, why implement it as SQL?  Is it just so we're
not adding keywords specifically to psql?  In that case, it shouldn't
support QUIT.

Personally, I think this is somethign that should go into the backend ...
I'd like to be able to write perl scripts that talk to the backend without
having to remember all the various system tables I need to query / join to
get the same results as \d gives me in psql ... same for any interface
language, really ...

Isn't that what the information_schema catalog is for?

Thom

On Jul 15, 2010, at 10:50 AM, "Joshua D. Drake" <jd@commandprompt.com> wrote:

On Thu, 2010-07-15 at 17:38 +0200, Magnus Hagander wrote:

Looks like the last time this was discussed, there wasn't any clear
conclusion. Someone created a patch and it's still on the TODO list:
http://archives.postgresql.org/pgsql-hackers/2010-01/msg01845.php

That one is about:
a) doing it in psql., not the backend
b) not actually implementing the command, but implementing hints for
the user telling them which is the correct command

Is there an actual common use-case for having these commands available
for *non-psql* interfaces?

Yes. We should provide a single, well described grammar for interacting
with objects in the database regardless of client. I should be able to
open ANY SQL terminal, and type SHOW ME THE MONEY and have Benjamins
fall out.

Damn straight. I like \d as well as anyone but there are real problems with it. Perhaps when we add \dxrvbfqS$: we'll stop to reflect on what they are.

Having said that, I want to urge that we spend a suitable amount of time and thought and care designing this, lest it turn into a mess. I have no interest in slamming something through without adequate consideration.

...Robert

On Thu, 15 Jul 2010, Thom Brown wrote:

On 15 July 2010 17:07, Marc G. Fournier <scrappy@hub.org> wrote:

On Thu, 15 Jul 2010, Thom Brown wrote:

If it's only a psql problem, why implement it as SQL? �Is it just so we're
not adding keywords specifically to psql? �In that case, it shouldn't
support QUIT.

Personally, I think this is somethign that should go into the backend ...
I'd like to be able to write perl scripts that talk to the backend without
having to remember all the various system tables I need to query / join to
get the same results as \d gives me in psql ... same for any interface
language, really ...

Isn't that what the information_schema catalog is for?

I'd rather write:

SHOW TABLES;

then:

SELECT table_name
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
AND table_schema NOT IN
('pg_catalog', 'information_schema');

And, the latter, unless I'm doing it regularly, is alot harder to remember
then the former ...

----
Marc G. Fournier Hub.Org Hosting Solutions S.A.
scrappy@hub.org http://www.hub.org

Yahoo:yscrappy Skype: hub.org ICQ:7615664 MSN:scrappy@hub.org

On Thu, 2010-07-15 at 13:16 -0300, Marc G. Fournier wrote:

Isn't that what the information_schema catalog is for?

I'd rather write:

SHOW TABLES;

then:

SELECT table_name
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
AND table_schema NOT IN
('pg_catalog', 'information_schema');

And, the latter, unless I'm doing it regularly, is alot harder to remember
then the former ...

Thank you Marc. That is an excellent description of the problem that is
being ignored. We are no longer the academia database. We need to think
of real users here.

JD

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering

On 15 July 2010 17:16, Marc G. Fournier <scrappy@hub.org> wrote:

On Thu, 15 Jul 2010, Thom Brown wrote:

On 15 July 2010 17:07, Marc G. Fournier <scrappy@hub.org> wrote:

On Thu, 15 Jul 2010, Thom Brown wrote:

If it's only a psql problem, why implement it as SQL?  Is it just so
we're
not adding keywords specifically to psql?  In that case, it shouldn't
support QUIT.

Personally, I think this is somethign that should go into the backend ...
I'd like to be able to write perl scripts that talk to the backend
without
having to remember all the various system tables I need to query / join
to
get the same results as \d gives me in psql ... same for any interface
language, really ...

Isn't that what the information_schema catalog is for?

I'd rather write:

SHOW TABLES;

then:

SELECT  table_name
 FROM information_schema.tables
 WHERE table_type = 'BASE TABLE'
  AND table_schema NOT IN
      ('pg_catalog', 'information_schema');

And, the latter, unless I'm doing it regularly, is alot harder to remember
then the former ...

Yes, I see what you mean now. That would simplify things greatly.

Thom

On Thu, Jul 15, 2010 at 05:38:35PM +0200, Magnus Hagander wrote:

On Thu, Jul 15, 2010 at 17:30, Thom Brown <thombrown@gmail.com> wrote:

On 15 July 2010 16:20, Simon Riggs <simon@2ndquadrant.com> wrote:

On Thu, 2010-07-15 at 11:05 -0400, Tom Lane wrote:

Simon Riggs <simon@2ndQuadrant.com> writes:

The biggest turn off that most people experience when using PostgreSQL
is that psql does not support memorable commands.

I would like to implement the following commands as SQL, allowing them
to be used from any interface.

SHOW TABLES
SHOW COLUMNS
SHOW DATABASES

This has been discussed before, and rejected before. �Please see
archives.

Many years ago. I think it's worth revisiting now in light of the number
of people now joining the PostgreSQL community and the greater
prevalence other ways of doing it. The world has changed, we have not.

I'm not proposing any change in function, just a simpler syntax to allow
the above information to be available, for newbies.

Just for the record, I've never ever met anyone that said "Oh, this \d
syntax makes so much sense. I'm a real convert to Postgres now you've
shown me this". The reaction is always the opposite one; always
negative. Which detracts from our efforts elsewhere.

--

Looks like the last time this was discussed, there wasn't any clear
conclusion. �Someone created a patch and it's still on the TODO list:
http://archives.postgresql.org/pgsql-hackers/2010-01/msg01845.php

That one is about:
a) doing it in psql., not the backend
b) not actually implementing the command, but implementing hints for
the user telling them which is the correct command

Is there an actual common use-case for having these commands available
for *non-psql* interfaces?

In a word, YES!

In two words, HELL, YES!

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate