Обсуждение: psql \d option list overloaded
D. Dante Lorenso wrote:
> > When I started with PostgreSQL and MySQL, MySQL was far easier
> > to use
>
> I started with MySQL and it WAS easier to use. It was easier because
> the manual essentially reads:
>
> -- we didn't implement anything complicated that's why
> -- we are fast.
>
> The only SQL customizations that MySQL has that I really miss in PostgreSQL
> are the commands:
>
> SHOW DATABASES;
> SHOW TABLES;
> DESC table;
>
> That was ubber simple to do in MySQL. To this day, I have trouble with
> that in PostgreSQL. I'm constantly doing:
>
> psql> \?
> psql> help;
> ERROR: syntax error at or near "help" at character 1
> psql> \h
> ...
> * damnit, that's not it...*
> psql> \?
> psql> \d
> * ok, now which flag do I use for tables vs functions..etc?*
>
> I finally figure it out, I just end up forgetting again later. I still
> have no clue how I'd find the same data without using psql. In MySQL
> I can run those queries from PHP, PERL...etc. I know you can find that
> data in system tables in PostgreSQL, but I don't wanna muck around with
> all that. I just wanna do something as simple as MySQL.
[ Moved to hackers.]
I am starting to agree that our \d* handling is just too overloaded.
Look at the option list from \?:Informational \d [NAME] describe table, index, sequence, or view \d{t|i|s|v|S}
[PATTERN](add "+" for more detail) list tables/indexes/sequences/views/system tables \da [PATTERN]
listaggregate functions \dc [PATTERN] list conversions \dC list casts \dd [PATTERN] show comment for
object \dD [PATTERN] list domains \df [PATTERN] list functions (add "+" for more detail) \dg [PATTERN] list groups
\dn [PATTERN] list schemas \do [NAME] list operators \dl list large objects, same as \lo_list \dp
[PATTERN] list table access privileges \dT [PATTERN] list data types (add "+" for more detail) \du [PATTERN] list
users \l list all databases (add "+" for more detail) \z [PATTERN] list table access privileges (same as
\dp)
Can anyone remember all those? With the single-letter options, once the
list got too long, we started encouraging long option names. It seems
the same is true of \d.
I like the idea of adding a new syntax to show that information using
simple SQL command syntax, and putting it in the backend so all
applications can access it. I know we have information schema, and
maybe that can be used to make this simpler.
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square,
Pennsylvania19073
Couldn't agree more - syntax like SHOW TABLES; is inituitive and somehow "right" - [chuckles] - Mysql does not have *everything* wrong! regards Mark Bruce Momjian wrote: >I like the idea of adding a new syntax to show that information using >simple SQL command syntax, and putting it in the backend so all >applications can access it. I know we have information schema, and >maybe that can be used to make this simpler. > > >
Bruce Momjian wrote: > I am starting to agree that our \d* handling is just too overloaded. > Look at the option list from \?: > Can anyone remember all those? Yes. > I like the idea of adding a new syntax to show that information using > simple SQL command syntax, and putting it in the backend so all > applications can access it. I know we have information schema, and > maybe that can be used to make this simpler. That's right.
On Sat, Jan 03, 2004 at 08:25:21PM -0500, Bruce Momjian wrote: > > I finally figure it out, I just end up forgetting again later. I still > > have no clue how I'd find the same data without using psql. In MySQL > > I can run those queries from PHP, PERL...etc. I know you can find that > > data in system tables in PostgreSQL, but I don't wanna muck around with > > all that. I just wanna do something as simple as MySQL. > > [ Moved to hackers.] > > I am starting to agree that our \d* handling is just too overloaded. > Look at the option list from \?: > I like the idea of adding a new syntax to show that information using > simple SQL command syntax, and putting it in the backend so all > applications can access it. I know we have information schema, and > maybe that can be used to make this simpler. Bruce, while I agree with you about \d (and all its children), as well as the querying we talked about on irc, I disagree with the notion of a "SHOW DATABASES" query. This is one of the things that irritates me about mysql is the pseudo-sql that everyone has come to accept (through learning sql on mysql) as "sql". Things such as the '#' comments, and the various SHOW DATABASES, I feel, detract from the "look and feel" of the database. That look and feel is one of the reasons I am so loyal to postgres (and indeed why some people are so loyal to mysql). It doesn't make sense to create pseudo-sql, when all you're abstracting is function-macros. I think the backslash syntax is fine. If you really wanted to change it, you might consider a different syntax for it. Many of us are familiar with slash/bang/colon/backslash commands in interfacing with the programs we use regularly (vi, shells, irc clients). Why not a /functions as a long syntax for \df? Would there be a direct problem using the forward slash as a command indicator? This way you could give people like the original poster something they were looking for, eg: /functions /databases and what I was looking for: /functions timestamp It also allows us a lot more freedom in changing the syntax, as the expression of the commands is english (or, pick your language). I seem to recall Neil mentioning to me that was a problem with internationalization, but that's over my head. I don't have any particular allegiance to the forward slash over anything else. My chief concern is that what we're abstracting here are macros, and as such, they should not be treated as sql. Because they aren't sql. If you want to find out how to show the databases in sql, use psql -E. Alex -- alex@posixnap.net Alex J. Avriette, Solaris Systems Masseur "I ... remain against the death penalty because I feel that eternal boredom with no hope of parole is a much worse punishmentthan just ending it all mercifully with that quiet needle." - Rachel Mills, NC Libertarian Gubernatorial Candidate
On Sun, Jan 04, 2004 at 07:59:02PM -0600, D. Dante Lorenso wrote: > Anything other than simple, short commands is a waste, IMHO. I can easily > remember SHOW DATABASES and SHOW TABLES and DESC <table>, because they > reflect > my intensions directly and 'make sense'. What makes sense to me in csh doesn't make sense in a bourne shell. You can't expect all applications to work correctly. I'd like to second Peter's "yep" when asked if he could remember all the various \d* commands. It really comes down to whether you're trying. New software (even though you may have been using it for a year) requires some adjustment. > Using the slash commands works if you are familiar with them ... sorta > like 'ls' switches (I type 'ls -alF' without thinking about what those > switches do because it's embedded in my head from years of repetition. > Any other flags to 'ls', and I gotta go hit the man pages.) So, hit the documentation when you can't find what you need. Postgres has superb documentation. Or, as a counterpoint, ls -alF doesn't always work on every operating system (such as old IRIX and old SunOS), and is sometimes even a shell builtin. The flawed assumption, again, is that because /your/ environment supports something, that another environment should. > What's more important is the ability to use these commands from any > interface not just 'psql' client. I think 'psql' already has the slash > commands. No need to create NEW slash commands there... > > >If you want to find out how to show the databases in sql, use psql -E. > > > > > Have you actually done that? OMG! Yes, I do it frequently. You may notice a recent post of mine used exactly that output. > 1) Using System Catalogs ... (from psql -E) > > SELECT n.nspname as "Schema", > c.relname as "Name", > CASE c.relkind -snip- > ORDER BY 1,2; If the point here was to show that it was a complex query, then the only real reply is "This is why we have macros!". > 3) like MySQL does it... > > SHOW TABLES; Should postgres also support the '#' comment? What other non-sql sqlisms should we support? > There's something to be said about the 'SHOW'and 'DESC' sql-extensions > added into MySQL. Newbies can really 'get' it quickly. It's what really I would argue that these are not "sql extensions" at all. If you like, I can go over the source to verify this myself, but my guess is that MySQL is doing exactly what postgres is doing, and evaluating this as a macro. Furthermore, databases are not designed for "newbies" to jump right in with both feet. They are designed to be robust and stable. Additionally, some SQL compliance is nice. After that, you work on features. Changing the interface so that you or others don't have to read the documentation smacks of laziness. Somebody like Bruce, Peter, or Tom (or indeed somebody else) is going to waste yet more time making things like this available to somebody who probably won't read any of the other documentation either, and will wind up on irc pestering somebody like myself, Dave, or Neil. Why is this progress? > sold me on MySQL when I first learned it. For me, it's like: > > 'dir' in DOS, > 'ls' in Unix > 'SHOW' in MySQL > ??? in PostgreSQL ? We've been over this. It's \d*. > Sure, with time as my database needs grew and I matured as a developer, > I eventually gained more respect for PostgreSQL and have made the switch > even without this feature, but to this day, I really think MySQL *did it > right* with those extensions. You can't become a PostgreSQL guru without > being a newbie first. I vote we make it easier for newbies. What really frightens me here is that I know of several applications (shudder, "LAMP" applications) which use the output of "show tables" or other of your "extensions." The problem with this is precisely that it /isn't/ sql, and it can't be supported as a static command. It is intended to be there for people to use interactively. Making "pseudo sql" will encourage more developers to (and I'd apologize for this if it weren't true) code in Postgres the same lazy way they code in MySQL. Alex -- alex@posixnap.net Alex J. Avriette, Unix Systems Engineer "It's computationally FEROCIOUS." - Steve Jobs, discussing 64-bit computing
Alex J. Avriette wrote: > On Sun, Jan 04, 2004 at 07:59:02PM -0600, D. Dante Lorenso wrote: > > > Anything other than simple, short commands is a waste, IMHO. I can easily > > remember SHOW DATABASES and SHOW TABLES and DESC <table>, because they > > reflect > > my intensions directly and 'make sense'. > > What makes sense to me in csh doesn't make sense in a bourne shell. > You can't expect all applications to work correctly. I'd like to second > Peter's "yep" when asked if he could remember all the various \d* > commands. It really comes down to whether you're trying. New software > (even though you may have been using it for a year) requires some > adjustment. OK, I will drop the idea. Thanks. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Am Sonntag, 4. Januar 2004 20:13 schrieb Alex J. Avriette: > On Sat, Jan 03, 2004 at 08:25:21PM -0500, Bruce Momjian wrote: > > > I finally figure it out, I just end up forgetting again later. I still ... > > /functions > /databases > ... Long options sounds really good. It is like GNU-tools. A single - for single character options and a double -- for long options. Ah - a single \ for short options in postgresql and a double \\ for long? What do you think? -- Dr. Eckhardt + Partner GmbH http://www.epgmbh.de
I think moving the \d and simliar features in psql to SQL is a good idea. That will make the features available in any client library. As for the syntax, maybe a investigation is needed.
Alex J. Avriette wrote:
>On Sat, Jan 03, 2004 at 08:25:21PM -0500, Bruce Momjian wrote:
>
>
>>>I finally figure it out, I just end up forgetting again later. I still
>>>have no clue how I'd find the same data without using psql. In MySQL
>>>I can run those queries from PHP, PERL...etc. I know you can find that
>>>data in system tables in PostgreSQL, but I don't wanna muck around with
>>>all that. I just wanna do something as simple as MySQL.
>>>
>>>
>>[ Moved to hackers.]
>>
>>I am starting to agree that our \d* handling is just too overloaded.
>>Look at the option list from \?:
>>
>>
>>I like the idea of adding a new syntax to show that information using
>>simple SQL command syntax, and putting it in the backend so all
>>applications can access it. I know we have information schema, and
>>maybe that can be used to make this simpler.
>>
>>
>Bruce, while I agree with you about \d (and all its children), as well
>as the querying we talked about on irc, I disagree with the notion of a
>"SHOW DATABASES" query. This is one of the things that irritates me
>about mysql is the pseudo-sql that everyone has come to accept ... It doesn't
>make sense to create pseudo-sql, when all you're abstracting is function-macros...
>
Anything other than simple, short commands is a waste, IMHO. I can easily
remember SHOW DATABASES and SHOW TABLES and DESC <table>, because they
reflect
my intensions directly and 'make sense'.
Using the slash commands works if you are familiar with them ... sorta
like 'ls' switches (I type 'ls -alF' without thinking about what those
switches do because it's embedded in my head from years of repetition.
Any other flags to 'ls', and I gotta go hit the man pages.)
What's more important is the ability to use these commands from any
interface not just 'psql' client. I think 'psql' already has the slash
commands. No need to create NEW slash commands there...
>If you want to find out how to show the databases in sql, use psql -E.
>
>
Have you actually done that? OMG!
1) Using System Catalogs ... (from psql -E)
SELECT n.nspname as "Schema", c.relname as "Name", CASE c.relkind WHEN 'r' THEN 'table'
WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN
'special'END as "Type", u.usename as "Owner" FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_user u ON
u.usesysid= c.relowner LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN
('r','') AND n.nspname NOT IN ('pg_catalog', 'pg_toast') AND pg_catalog.pg_table_is_visible(c.oid) ORDER
BY1,2;
or ...
2) (using information schema ... little better)
SELECT table_name FROM information_schema.tables WHERE table_schema
= 'public';
or ...
3) like MySQL does it...
SHOW TABLES;
Lemme think about which one I prefer ;-) Uh, Ok, I'm done thinking
now. hehe.
There's something to be said about the 'SHOW'and 'DESC' sql-extensions
added into MySQL. Newbies can really 'get' it quickly. It's what really
sold me on MySQL when I first learned it. For me, it's like:
'dir' in DOS, 'ls' in Unix 'SHOW' in MySQL ??? in PostgreSQL ?
Sure, with time as my database needs grew and I matured as a developer,
I eventually gained more respect for PostgreSQL and have made the switch
even without this feature, but to this day, I really think MySQL *did it
right* with those extensions. You can't become a PostgreSQL guru without
being a newbie first. I vote we make it easier for newbies.
Dante
> Anything other than simple, short commands is a waste, IMHO. I can easily > remember SHOW DATABASES and SHOW TABLES and DESC <table>, because they > reflect > my intensions directly and 'make sense'. Can you remember how to get a list of indexes on a particular table? How about a specific indexes build? I ask, because I constantly forgot both of those (don't like FROM). > 2) (using information schema ... little better) > > SELECT table_name FROM information_schema.tables WHERE table_schema > = 'public'; > > or ... > > 3) like MySQL does it... > > SHOW TABLES; > > Lemme think about which one I prefer ;-) Uh, Ok, I'm done thinking > now. hehe. I actually prefer #2 myself. It works on a number of databases aside from just PostgreSQL. So, as a user who worked in a mixed environment it was easier to remember. But I get your point. > Sure, with time as my database needs grew and I matured as a developer, > I eventually gained more respect for PostgreSQL and have made the switch > even without this feature, but to this day, I really think MySQL *did it > right* with those extensions. You can't become a PostgreSQL guru without I agree with the simple SHOW TABLES command but disagree with: SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [LIKE wild] I much prefer: SELECT * FROM COLUMNS WHERE table LIKE '%tab%' AND database = 'billing'; It's not much longer, certainly more natural to those that know SQL, and infinitely more useful since you can create result sets that the programmer of SHOW hadn't considered. A perfect example is the addition of the FULL clause in SHOW. The above select does not need additional keywords for different formatting options as it can simply use "natural" SQL styling.
Bruce Momjian wrote:
>Alex J. Avriette wrote:
>
>
>>On Sun, Jan 04, 2004 at 07:59:02PM -0600, D. Dante Lorenso wrote:
>>
>>
>>
>>>Anything other than simple, short commands is a waste, IMHO. I can easily
>>>remember SHOW DATABASES and SHOW TABLES and DESC <table>, because they
>>>reflect
>>>my intensions directly and 'make sense'.
>>>
>>>
>>What makes sense to me in csh doesn't make sense in a bourne shell.
>>You can't expect all applications to work correctly. I'd like to second
>>Peter's "yep" when asked if he could remember all the various \d*
>>commands. It really comes down to whether you're trying. New software
>>(even though you may have been using it for a year) requires some
>>adjustment.
>>
>>
>
>OK, I will drop the idea. Thanks.
>
>
>
Bruce,
The idea is not without merit. What you are looking at is a way to get
this information as a query without having to know all the intricasies
of all the pg_* internals or duplicating complex queries. "psql -E"
shows you just how tricky this is. Secondly, if this information
changes in a release, then the end user has to rewrite all of the
queries to work. Being able to issue a query to the dbms and get the
information as a normal SQL result makes sense and is definately convenient.
The \d* commands work from psql but not from anywhere else. Try
getting the information from a PHP script by sending a "\dS" query. It
doesn't work. If the same queries were stored in the backend and
referenced by psql and also could be referenced by other scripts, this
would be a good thing and keep the work centralized. If the queries
were in the backend, the psql users could keep the \dS command but it
would call an internal function or execute a queried stored in the
system tables.
One option is to get the information via a function like
SELECT * FROM pg_info('tables');
SELECT * FROM pg_info('indexes');
"psql -E" would show the same query being executed for "\dt"
Another option if no one wanted a language construct, perhaps one option
would be to store the queries themselves in a table like pg_queries.
This also has the advantage of exposing the queries used so that they
can used as examples for other purposes.
+------------+------------------------------------------+
|pg_info_type|pg_query |
+------------+------------------------------------------+
|tables |SELECT n.nspname as "Schema", c.relname |
| |as "Name", CASE c.relkind WHEN 'r' THEN |
| |'table' WHEN 'v' THEN 'view' WHEN 'i' THEN|
| |'index' WHEN 'S' THEN 'sequence' WHEN 's' |
| |THEN 'special' END as "Type", u.usename as|
| |"Owner" FROM pg_catalog.pg_class c LEFT |
| |JOIN pg_catalog.pg_user u ON u.usesysid = |
| |c.relowner LEFT JOIN |
| |pg_catalog.pg_namespace n ON n.oid = |
| |c.relnamespace WHERE c.relkind IN ('r','')|
| |AND n.nspname NOT IN ('pg_catalog', |
| |'pg_toast') AND |
| |pg_catalog.pg_table_is_visible(c.oid) |
| |ORDER BY 1,2; |
+------------+------------------------------------------+
|indexes |SELECT n.nspname as "Schema", c.relname as|
| |"Name", CASE c.relkind WHEN 'r' THEN |
| |'table' WHEN 'v' THEN 'view' WHEN 'i' THEN|
| |'index' WHEN 'S' THEN 'sequence' WHEN 's' |
| |THEN 'special' END as "Type", u.usename as|
| |"Owner", c2.relname as "Table" FROM |
| |pg_catalog.pg_class c JOIN |
| |pg_catalog.pg_index i ON i.indexrelid = |
| |c.oid JOIN pg_catalog.pg_class c2 ON |
| |i.indrelid = c2.oid LEFT JOIN |
| |pg_catalog.pg_user u ON u.usesysid = |
| |c.relowner LEFT JOIN |
| |pg_catalog.pg_namespace n ON n.oid = |
| |c.relnamespace WHERE c.relkind IN ('i','')|
| |AND n.nspname NOT IN ('pg_catalog', |
| |'pg_toast') AND |
| |pg_catalog.pg_table_is_visible(c.oid) |
| |ORDER BY 1,2; |
+------------+------------------------------------------+
Again, this is just food for thought. Perhaps it is a way to satisfy
both arguments.
Thomas
Hi, > > 2) (using information schema ... little better) > > SELECT table_name FROM information_schema.tables WHERE table_schema > = 'public'; > > or ... > ... I just looked at the information_schema. It is a very nice feature, but difficult to use in psql. I just wanted to see, what I can find here. After trying and rtfm I ended in '\d information_schema.*'. I get a very large page wich is quite unreadable. '\d' is normally very usable. It would be better not to show the view-definition. What if \d on views just show the column, type and attribute. \d+ would show the full view-definition. Tommi -- Dr. Eckhardt + Partner GmbH http://www.epgmbh.de
Thomas Swan wrote: > >The \d* commands work from psql but not from anywhere else. Try >getting the information from a PHP script by sending a "\dS" query. It >doesn't work. If the same queries were stored in the backend and >referenced by psql and also could be referenced by other scripts, this >would be a good thing and keep the work centralized. If the queries >were in the backend, the psql users could keep the \dS command but it >would call an internal function or execute a queried stored in the >system tables. > > > > <leibnitz-mode> I just independently had this idea, so I like it :-) </liebnitz-mode> cheers andrew
Alex J. Avriette wrote:
> On Sun, Jan 04, 2004 at 07:59:02PM -0600, D. Dante Lorenso wrote:
>
> > Anything other than simple, short commands is a waste, IMHO. I can easily
> > remember SHOW DATABASES and SHOW TABLES and DESC <table>, because they
> > reflect
> > my intensions directly and 'make sense'.
>
> What makes sense to me in csh doesn't make sense in a bourne shell.
And yet, bash has !$ and job control just like csh, even though they're
not standard Bourne-shell features.
It's not a bad thing to adopt good ideas from other projects.
> You can't expect all applications to work correctly.
You can't expect this anyway, at least when dealing with cross-database
applications. The intersection of the SQL feature sets across all the
major database engines is pretty limited -- small enough that you'll
almost certainly end up using something database-specific when attempting
to do anything truly nontrivial.
> I'd like to second
> Peter's "yep" when asked if he could remember all the various \d*
> commands. It really comes down to whether you're trying. New software
> (even though you may have been using it for a year) requires some
> adjustment.
This is true, but it's no argument against implementing "show
databases", "show tables", and "describe".
Every database engine is different, but in the case of PG it makes sense
to adopt the best methods we can find. A consistent and easy to
remember way of showing the various entities in psql (at the very least)
would be of great advantage. It's something that MySQL gets right. As
it turns out, we already have "SHOW" in psql and it's used for something
else. So we might instead use something else (e.g. "VIEW") instead.
Either way, a single command that takes as its argument the type of entity
you want to see would be extremely useful, and much easier to remember
than what we currently have -- because the names of the entities that
are available are already well-defined and are likely known to the user
already.
> > What's more important is the ability to use these commands from any
> > interface not just 'psql' client. I think 'psql' already has the slash
> > commands. No need to create NEW slash commands there...
> >
> > >If you want to find out how to show the databases in sql, use psql -E.
> > >
> > >
> > Have you actually done that? OMG!
>
> Yes, I do it frequently. You may notice a recent post of mine used
> exactly that output.
Now do it from within psql.
It's \l, as it turns out. This violates the principle of least surprise
because psql generally uses \d* to show entities.
> > 3) like MySQL does it...
> >
> > SHOW TABLES;
>
> Should postgres also support the '#' comment? What other non-sql
> sqlisms should we support?
PG already has a number of PG-specific features. Adding more,
*especially* if they happen to be compatible with other databases, isn't
going to hurt much.
No, the thing to worry about here is whether or not these commands
("SHOW", for instance) will appear in the SQL spec and will have a
completely different meaning from the meaning in PG. Also of concern is
that "SHOW" is already reserved and used for something else. We'd have
to use something other than "SHOW" for the purpose being discussed.
> > There's something to be said about the 'SHOW'and 'DESC' sql-extensions
> > added into MySQL. Newbies can really 'get' it quickly. It's what really
>
> I would argue that these are not "sql extensions" at all. If you like, I
> can go over the source to verify this myself, but my guess is that MySQL
> is doing exactly what postgres is doing, and evaluating this as a macro.
No, they are built into MySQL's backend parser. You can easily verify
this by executing these commands from within Perl or Python. They
return a table just like any other SQL command that returns data.
> Furthermore, databases are not designed for "newbies" to jump right in
> with both feet. They are designed to be robust and stable.
Now this is ludicrous. Yes, they're designed to be robust and stable,
but that has absolutely nothing to do with how easy they are to use.
> Additionally,
> some SQL compliance is nice. After that, you work on features.
If we were talking about something that went against the SQL standard
then I would agree with you. But we're talking about something that,
as far as I know, isn't in the SQL standard at all. Implementing it
won't make us noncompliant with the SQL standard any more than the
implementation of CREATE INDEX has.
> Changing the interface so that you or others don't have to read the
> documentation smacks of laziness.
Really? One could make the same argument for standards of any kind,
yes? :-)
> Somebody like Bruce, Peter, or Tom (or indeed somebody else) is going
> to waste yet more time making things like this available to somebody
> who probably won't read any of the other documentation either, and will
> wind up on irc pestering somebody like myself, Dave, or Neil. Why is
> this progress?
It's progress because it will keep those people from pestering someone
in the know about how to show the available databases, or how to
describe a table.
> > sold me on MySQL when I first learned it. For me, it's like:
> >
> > 'dir' in DOS,
> > 'ls' in Unix
> > 'SHOW' in MySQL
> > ??? in PostgreSQL ?
>
> We've been over this. It's \d*.
For listing databases it's \l. Not exactly consistent with the rest of
the related psql commands.
> > Sure, with time as my database needs grew and I matured as a developer,
> > I eventually gained more respect for PostgreSQL and have made the switch
> > even without this feature, but to this day, I really think MySQL *did it
> > right* with those extensions. You can't become a PostgreSQL guru without
> > being a newbie first. I vote we make it easier for newbies.
>
> What really frightens me here is that I know of several applications (shudder,
> "LAMP" applications) which use the output of "show tables" or other of your
> "extensions." The problem with this is precisely that it /isn't/ sql, and it
> can't be supported as a static command.
Of course not. But applications which rely on information such as that
provided by "show tables" will typically not be possible to write while
adhering to the feature intersection of all major databases anyway.
> It is intended to be there for people
> to use interactively.
Nonsense. It's there to be used. Whether it's used interactively or
not is irrelevant. The command provides useful information. But see
below.
> Making "pseudo sql" will encourage more developers to
> (and I'd apologize for this if it weren't true) code in Postgres the same
> lazy way they code in MySQL.
This is a strawman argument, although I understand your concern here.
To be honest, for application development I'd much rather see people
use information_schema, but that's only because information_schema is
in the SQL standard and as such should be the preferred way to retrieve
the information that the "SHOW" commands in MySQL return. That said,
the inclusion of information_schema is a very recent development on the
PostgreSQL side of things, and doesn't even exist on some other major
databases such as MSSQL.
Of course, a PG equivalent to MySQL's "show" would be an even more
recent development... :-)
--
Kevin Brown kevin@sysexperts.com
Kevin Brown wrote: > Every database engine is different, but in the case of PG it makes > sense to adopt the best methods we can find. A consistent and easy > to remember way of showing the various entities in psql (at the very > least) would be of great advantage. It's something that MySQL gets > right. As it turns out, we already have "SHOW" in psql and it's used > for something else. So we might instead use something else (e.g. > "VIEW") instead. What is wrong with SELECT * FROM information_schema.tables; ? If it's too much to type, put information_schema in the path. This syntax has the advantage that you can use qualifications and other SQL features. And you can build customized views on top of it. Does SHOW TABLES or whatever it might be called support that?
On Sat, 10 Jan 2004, Peter Eisentraut wrote: > > to remember way of showing the various entities in psql (at the very > > least) would be of great advantage. It's something that MySQL gets > > right. As it turns out, we already have "SHOW" in psql and it's used > > for something else. > > What is wrong with > > SELECT * FROM information_schema.tables; The result is very hard to read since it's so much of it (try column instead of tables). The \xx commands do some nice formatting you don't get from the above. I would rather have long commands so one can write \describe_table foo and have the tab completion work for these of course (only for the long commands, the \dt and such does not belong in completion). The information schema is nice, but it's not what I want to use at the prompt to view the content of the database. -- /Dennis Björklund
Dennis Björklund <db@zigo.dhs.org> writes: > I would rather have long commands so one can write > > \describe_table foo I would think it would be better to keep everything under a single command and have a 1-1 correspondence to \d. Ie, just add a long form syntax following the existing \d. \d would become just an obvious set of abbreviations. So for example: \describe table foo => \dt foo \describe index foo => \di foo \describe aggregate foo => \da foo \describe operator foo => \do foo ... -- greg
Greg Stark <gsstark@mit.edu> writes:
> So for example:
> \describe table foo => \dt foo
> \describe index foo => \di foo
> \describe aggregate foo => \da foo
> \describe operator foo => \do foo
It doesn't seem to me that this buys much except verboseness, though.
ISTM there are three fundamental problems with \d and friends:
1. Some people have a hard time remembering the commands.2. Some people aren't using psql.3. psql keeps breaking across
backendversions because the needed commands change.
I don't see a lot of value in addressing just one of these problem
areas, when we could instead do something that addresses all three.
regards, tom lane
On Sat, 10 Jan 2004, Tom Lane wrote: > ISTM there are three fundamental problems with \d and friends: > > 1. Some people have a hard time remembering the commands. > 2. Some people aren't using psql. > 3. psql keeps breaking across backend versions because the > needed commands change. > > I don't see a lot of value in addressing just one of these problem > areas, when we could instead do something that addresses all three. I agree, at least for #2 and #3. But I just don't understand #1. Anything is hard to remember when you're just starting to learn it. But it's still faster to type \? <CR> then \dt than it is to type "show tables". And "show tables" is hard (relatively speaking) for me to remember because I'm used to psql's way of doing things, since I mostly use it. Jon
Tom Lane wrote: > 2. Some people aren't using psql. I don't see why this is an issue. People not using psql are either using a GUI, which presumably supports plenty of "show" and "describe" functionality, or they're writing their own program, in which case it doesn't really matter how short or easy to remember the commands are.
Dennis Björklund wrote: > > What is wrong with > > > > SELECT * FROM information_schema.tables; > > The result is very hard to read since it's so much of it (try column > instead of tables). The \xx commands do some nice formatting you > don't get from the above. This is an interesting point to remember for those that are advocating pushing psql's queries into the backend. psql's queries are optimized for monospaced text screens of limited size. Unless someone else is writing a command-line client, there would be little reuse effect, because any given application will have different display requirements. (Another problem with pushing psql's queries into the backend is that much of the output that psql makes is not a single table. Sometimes there is more than one table, or the information is in the table footers. It'd be quite complicated to make the backend produce those kinds of displays.)
On Sun, 11 Jan 2004, Peter Eisentraut wrote: > Another problem with pushing psql's queries into the backend is that > much of the output that psql makes is not a single table. Sometimes > there is more than one table, or the information is in the table > footers. Yes, pushing the \xx commands into the server makes no sense to me at all. The commands in psql are very specific for psql. I don't see why you ever want to do SHOW TABLES except at the command line in psql. If your application wants to find all tables in the database, then we have the standard sql way, which is the information schema. The argument that "show tables" is easier to remember then \dt might be true, but to me that just means that we should make psql better by adding \describe_table and such, not to push psql code into the server. Making a couple of views that are pg specific to make it easier to get information out could be good however. The information schema does not always contain all information one might want. Making specialised SQL commands for it I'm not in favor of at all. -- /Dennis Björklund
Peter Eisentraut <peter_e@gmx.net> writes:
> Tom Lane wrote:
>> 2. Some people aren't using psql.
> I don't see why this is an issue. People not using psql are either
> using a GUI, which presumably supports plenty of "show" and "describe"
> functionality, or they're writing their own program, in which case it
> doesn't really matter how short or easy to remember the commands are.
But this interacts with point 3 (psql breaks on every new backend
version). It's not desirable to have every GUI and large custom program
implementing its own set of metadata inquiry commands: they all have
to go through the same update pain as psql. Perhaps if people start to
rely on information_schema for those things, life will get better,
but I'm unconvinced that will happen. psql itself certainly hasn't
moved in that direction.
regards, tom lane
On Saturday 10 January 2004 19:16, Jon Jensen wrote:
> On Sat, 10 Jan 2004, Tom Lane wrote:
> > ISTM there are three fundamental problems with \d and friends:
> >
> > 1. Some people have a hard time remembering the commands.
> > 2. Some people aren't using psql.
> > 3. psql keeps breaking across backend versions because the
> > needed commands change.
> >
> > I don't see a lot of value in addressing just one of these problem
> > areas, when we could instead do something that addresses all three.
>
> I agree, at least for #2 and #3. But I just don't understand #1. Anything
> is hard to remember when you're just starting to learn it. But it's still
> faster to type \? <CR> then \dt than it is to type "show tables". And
> "show tables" is hard (relatively speaking) for me to remember because I'm
> used to psql's way of doing things, since I mostly use it.
>
I'd second this point; I've certainly stumbled over the "show" syntax when
trying to get anything other than tables in mysql.
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
Tom Lane wrote:
> But this interacts with point 3 (psql breaks on every new backend
> version). It's not desirable to have every GUI and large custom
> program implementing its own set of metadata inquiry commands: they
> all have to go through the same update pain as psql. Perhaps if
> people start to rely on information_schema for those things, life
> will get better, but I'm unconvinced that will happen. psql itself
> certainly hasn't moved in that direction.
IIRC, the two killers in psql compatibility have been outer joins and
schemas. I don't see how we could have avoided that, except with
highly specialized and static (parameter-less) commands. There have
been additional minor issues, but I suppose we could have avoided those
if we had cared to do so at all.
Several people have in the past proposed to keep psql backward
compatible, even if only by means of
if (version =x) { ...
}
else if (version = y) { ...
}
(which would be fine by me), but apparently no one has felt pressed
enough yet.
I have added this psql backslash discussion to TODO.detail.
---------------------------------------------------------------------------
Peter Eisentraut wrote:
> Tom Lane wrote:
> > But this interacts with point 3 (psql breaks on every new backend
> > version). It's not desirable to have every GUI and large custom
> > program implementing its own set of metadata inquiry commands: they
> > all have to go through the same update pain as psql. Perhaps if
> > people start to rely on information_schema for those things, life
> > will get better, but I'm unconvinced that will happen. psql itself
> > certainly hasn't moved in that direction.
>
> IIRC, the two killers in psql compatibility have been outer joins and
> schemas. I don't see how we could have avoided that, except with
> highly specialized and static (parameter-less) commands. There have
> been additional minor issues, but I suppose we could have avoided those
> if we had cared to do so at all.
>
> Several people have in the past proposed to keep psql backward
> compatible, even if only by means of
>
> if (version =x) {
> ...
> }
> else if (version = y) {
> ...
> }
>
> (which would be fine by me), but apparently no one has felt pressed
> enough yet.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square,
Pennsylvania19073