Thread: finding a column by name in psql

Hi everyone,

I'm now used to using psql to manage my databases into a terminal, and
I found the very convenient "\g |" command that pipes query result in
any shell tool I want. But I still have hard time finding some columns
in some tables (that may have 300+ cols). I would like to pipe the
result of "\d" mytable in some grep but it doesn't work. I have done
it once or twice by SELECTing pg_attrib but this requires a lot of
typing. I tried to store a procedure but apparently I don't have any
language allowed... I'm sure it could do it but and I wondering if I
missed some simpler command that would allow me to conveniently search
into database structure. i.e. something like "\d mytable *_ts" that
could display all cols in mytable ending with "_ts".

Any advice ?

I'm still on postgresql 7 and will upgrade for new projects.

Guillaume

On 3/12/07, Guillaume Bog <guibog@gmail.com> wrote:
> Hi everyone,
>
> I'm now used to using psql to manage my databases into a terminal, and
> I found the very convenient "\g |" command that pipes query result in
> any shell tool I want. But I still have hard time finding some columns
> in some tables (that may have 300+ cols). I would like to pipe the
> result of "\d" mytable in some grep but it doesn't work. I have done
> it once or twice by SELECTing pg_attrib but this requires a lot of
> typing. I tried to store a procedure but apparently I don't have any
> language allowed... I'm sure it could do it but and I wondering if I
> missed some simpler command that would allow me to conveniently search
> into database structure. i.e. something like "\d mytable *_ts" that
> could display all cols in mytable ending with "_ts".

Have you tried configuring your pager?  I use:
env:
PAGER=less
LESS='-iMSx4 -FX'
psql:
/pset pager=always

this allows for spreadsheet style navigation of psql results and you
can use search feature of less (slash) to highlight columns/data.

merlin

On 13/03/07, Merlin Moncure <mmoncure@gmail.com> wrote:
> On 3/12/07, Guillaume Bog <guibog@gmail.com> wrote:
> > Hi everyone,
> >
> > I'm now used to using psql to manage my databases into a terminal, and
> > I found the very convenient "\g |" command that pipes query result in
> > any shell tool I want. But I still have hard time finding some columns
> > in some tables (that may have 300+ cols). I would like to pipe the
> > result of "\d" mytable in some grep but it doesn't work. I have done
> > it once or twice by SELECTing pg_attrib but this requires a lot of
> > typing. I tried to store a procedure but apparently I don't have any
> > language allowed... I'm sure it could do it but and I wondering if I
> > missed some simpler command that would allow me to conveniently search
> > into database structure. i.e. something like "\d mytable *_ts" that
> > could display all cols in mytable ending with "_ts".
>
> Have you tried configuring your pager?  I use:
> env:
> PAGER=less
> LESS='-iMSx4 -FX'
> psql:
> /pset pager=always
>
> this allows for spreadsheet style navigation of psql results and you
> can use search feature of less (slash) to highlight columns/data.

Excellent! Exactly what I was looking for, thanks a lot.