Show type in psql SELECT

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:

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)

or in expanded form:

my_db=# \x
my_db=# SELECT * FROM my_table;
-[ RECORD 1 ]------
gid : integer | 1
description : character varying(255) | Hello
width : numeric(6,3) | 3.220

Has anyone else thought this was a missing feature?

-Mike

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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)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 9:55 AM, David Fetter <david(at)fetter(dot)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.

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

> 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.

Cheers,

Jeff

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(at)fetter(dot)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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)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(at)fetter(dot)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(at)gmx(dot)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 <craig(at)2ndquadrant(dot)com> wrote:
> 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'

But is the derived typmod always available? For example, with PostGIS:

postgis=# SELECT g, ST_Centroid(g) INTO TEMP t
postgis-# FROM (SELECT 'POLYGON((0 0, 1 1, 0 1, 0 0))'::geometry(Polygon)
g) p;
SELECT 1
postgis=# \d t
Table "pg_temp_15.t"
Column | Type | Modifiers
-------------+-------------------+-----------
g | geometry(Polygon) |
st_centroid | geometry |

-Mike

On 02/25/2013 08:54 AM, Mike Toews wrote:
> On 25 February 2013 12:48, Craig Ringer <craig(at)2ndquadrant(dot)com
> <mailto:craig(at)2ndquadrant(dot)com>> wrote:
> > 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'
>
> But is the derived typmod always available?

No, it usually isn't. I doubt this patch can provide that information
because the server doesn't keep track of derived typmods through a lot
of internal operations. If it can I'll jump for joy.

I was just pointing out that you can already get something similar to
this patch's effects with pg_typeof . It's a nice convenience not to
have to wrap everything in pg_typeof, though, and this patch seems useful.

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