Thread: Show type in psql SELECT

On Sat, February 23, 2013 00:09, Mike Toews wrote:
>
> Type info can be viewed with "\d mytable", however often I'd like to see
> the type (and typmod) info in SELECT queries with psql, similar to pgAdmin
> III. For example:
>
> my_db=# \pset type
> my_db=# SELECT * FROM my_table;
>  gid     | description            | width
>  integer | character varying(255) | numeric(6,3)
> ---------+------------------------+--------------
>        1 | Hello                  | 3.220
> (1 row)
>
> Has anyone else thought this was a missing feature?
>

As an occasional pgAdmin user, I have often thought that this feature should be added to psql.  It
would save time going back and forth between different views (especially with broad tables).  I
would be glad to have the possibility. It would have to be optional, of course.

In short: +1 from me.


thanks,

Erik Rijkers

On Sat, Feb 23, 2013 at 12:09:51PM +1300, Mike Toews wrote:
> Hi hackers,
> 
> Type info can be viewed with "\d mytable", however often I'd like to
> see the type (and typmod) info in SELECT queries with psql, similar
> to pgAdmin III. For example:

I'm thinking we should add it as a SET parameter and expose it to all
SQL.  The next client program(s) shouldn't have to re-invent this
separately.

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

On Sat, Feb 23, 2013 at 12:07:35PM -0800, Jeff Janes wrote:
> On Sat, Feb 23, 2013 at 9:55 AM, David Fetter <david@fetter.org> wrote:
> > On Sat, Feb 23, 2013 at 12:09:51PM +1300, Mike Toews wrote:
> > > Hi hackers,
> > >
> > > Type info can be viewed with "\d mytable", however often I'd
> > > like to see the type (and typmod) info in SELECT queries with
> > > psql, similar to pgAdmin III. For example:
> >
> > I'm thinking we should add it as a SET parameter and expose it to
> > all SQL.
> 
> This information is already provided through libpq, see PQftype.

Not everyone uses libpq, so my argument for making it available at the
SQL level stands.

> It is merely a matter of making psql present the data it already has
> in a way people find convenient.

With utmost respect for your talent and contributions, what you're
calling, "merely," here is one of the main barriers to PostgreSQL
adoption.  It's our attitude--fortunately not the dominant one--that
if it's available through some API, however obscure or complicated,
our job is done.

That may have been so in 2001, but even then we were getting our rear
ends handed to us by an outfit that, despite its massive technical
inferiority, took end-user usability very carefully into account.

The way I look at it, easy things should be easy, and this is an easy
thing.

> > The next client program(s) shouldn't have to re-invent this
> > separately.
> 
> The client has to decide what to do with this information, I don't
> see any way around that.  The server can't make that decision for
> it.

I don't know how you got the idea that the server should decide this
from what I wrote.  What I suggested was that we make this
available--not mandatory or auto-detected--via the SQL API, namely
with a SET command.

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

David,

* David Fetter (david@fetter.org) wrote:
> Not everyone uses libpq, so my argument for making it available at the
> SQL level stands.

Ok, if they're not using libpq then presumably they're using some
custom-written app which speaks the PostgreSQL protocol- and guess what,
this information is there too.

> That may have been so in 2001, but even then we were getting our rear
> ends handed to us by an outfit that, despite its massive technical
> inferiority, took end-user usability very carefully into account.

If you'd like to propose something concrete around this, please do so.
Thus far, it looks like pure hand-waving to me.

> The way I look at it, easy things should be easy, and this is an easy
> thing.

Then please make a specific proposal.  What would this "SET" option do?
How would an application make use of it?  Certainly, psql would have no
need of it to do exactly what's proposed here.

> I don't know how you got the idea that the server should decide this
> from what I wrote.

Because you suggested a server-side SET parameter?  What else would one
presume from what you've written?

> What I suggested was that we make this
> available--not mandatory or auto-detected--via the SQL API, namely
> with a SET command.

It's available through the PG frontend/backend protocol and available
through libpq.  In fact, you can't turn off getting the information.  If
you're curious about the data types of a table but don't want to
actually query the table, you can query it through information_schema
and/or pg_catalog.

A specific proposal around what is missing would be much more useful to
this discussion than complaining about people trying to make sense of
hand waving.
Thanks,        Stephen

On Sat, 2013-02-23 at 12:09 +1300, Mike Toews wrote:
> Has anyone else thought this was a missing feature?

I have.

On 02/25/2013 07:14 AM, Peter Eisentraut wrote:
> On Sat, 2013-02-23 at 12:09 +1300, Mike Toews wrote:
>> Has anyone else thought this was a missing feature?
> I have.
As have I, repeatedly. This would be a nice convenience, though
pg_typeof can be used to achieve a similar effect a bit more clumsily.

However, the thing I want most couldn't be provided by this patch
because it seems to be a deeper server limitation: the ability to get
typmod data from calculation results like
   NUMERIC(8,3) '11.131' + NUMERIC(8,3) '12.123'

The ability to get types for select results is already available, it's
just a bit clumsier:
   select pg_typeof('134'), pg_typeof(1234);

It can't report typmods for calculation results. Can your proposed
change? See eg:

regress=> select pg_typeof(NUMERIC(8,3) '11.131'),
pg_typeof(NUMERIC(8,3) '12.123'), pg_typeof(NUMERIC(8,3) '11.131' +
NUMERIC(8,3) '12.123');pg_typeof | pg_typeof | pg_typeof
-----------+-----------+-----------numeric   | numeric   | numeric
(1 row)

-- Craig Ringer                   http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services

On Sun, Feb 24, 2013 at 11:14 PM, Peter Eisentraut <peter_e@gmx.net> wrote:
> On Sat, 2013-02-23 at 12:09 +1300, Mike Toews wrote:
>> Has anyone else thought this was a missing feature?
>
> I have.

It never occurred to me before what exactly was missing but I did
regularly have to do CREATE TABLE AS (query...) just to see what types
would result. It does seem like having a way to et psql to print that
info without doing any DDL would be appropriate.

I don't have a clear idea how to format it though. It could be in a
second header row under the column names? Or it could be in a separate
block following or preceding the result set? Or it could be only in \x
mode as a third column in addition to the name and value?

-- 
greg

On 25 February 2013 12:48, Craig Ringer <<a href="mailto:craig@2ndquadrant.com">craig@2ndquadrant.com</a>>
wrote:<br/>> However, the thing I want most couldn't be provided by this patch<br />> because it seems to be a
deeperserver limitation: the ability to get<br /> > typmod data from calculation results like<br />><br />>  
 NUMERIC(8,3) '11.131' + NUMERIC(8,3) '12.123'<br /><br />But is the derived typmod always available? For example, with
PostGIS:<br/><br /><font face="courier new, monospace">postgis=# SELECT g, ST_Centroid(g) INTO TEMP t<br /> postgis-#
FROM(SELECT 'POLYGON((0 0, 1 1, 0 1, 0 0))'::geometry(Polygon) g) p;<br />SELECT 1<br />postgis=# \d t<br />           
Table"pg_temp_15.t"<br />   Column    |       Type        | Modifiers<br
/>-------------+-------------------+-----------<br/>  g           | geometry(Polygon) |<br /> st_centroid | geometry  
      |<br /></font><br />-Mike  

<div class="moz-cite-prefix">On 02/25/2013 08:54 AM, Mike Toews wrote:<br /></div><blockquote
cite="mid:CAM2FmMowmELKBBEmiFfP0ax9-8zRPF-Be2mV_htRUw7RUM7o0w@mail.gmail.com"type="cite">On 25 February 2013 12:48,
CraigRinger <<a href="mailto:craig@2ndquadrant.com" moz-do-not-send="true">craig@2ndquadrant.com</a>> wrote:<br
/>> However, the thing I want most couldn't be provided by this patch<br /> > because it seems to be a deeper
serverlimitation: the ability to get<br /> > typmod data from calculation results like<br /> ><br /> >    
NUMERIC(8,3)'11.131' + NUMERIC(8,3) '12.123'<br /><br /> But is the derived typmod always available?<br
/></blockquote><br/> No, it usually isn't. I doubt this patch can provide that information because the server doesn't
keeptrack of derived typmods through a lot of internal operations. If it can I'll jump for joy.<br /><br /> I was just
pointingout that you can already get something similar to this patch's effects with pg_typeof . It's a nice convenience
notto have to wrap everything in pg_typeof, though, and this patch seems useful.<br /><pre class="moz-signature"
cols="72">--Craig Ringer                   <a class="moz-txt-link-freetext"
href="http://www.2ndQuadrant.com/">http://www.2ndQuadrant.com/</a>PostgreSQLDevelopment, 24x7 Support, Training &
Services</pre>