Postgres Pro
Downloads
Home   >   mailing lists

Thread: SHOW

SHOW

From
Stephan Bergmann
Date:
Hi!

What is the equivalent to the mysql's:
SHOW COLUMNS FROM [TABLENAME];

Greetings
Steve

Re: SHOW

From
bpalmer
Date:
> What is the equivalent to the mysql's:
> SHOW COLUMNS FROM [TABLENAME];

in psql,  \d will show you all tables and \d TABLE will show you the
columns in the table.  If you need it as a sql command,  you can check the
system tables.

- Brandon

----------------------------------------------------------------------------
 b. palmer,  bpalmer@crimelabs.net            pgp:crimelabs.net/bpalmer.pgp5

Re: SHOW

From
Fernando Schapachnik
Date:
En un mensaje anterior, Stephan Bergmann escribió:
> Hi!
>
> What is the equivalent to the mysql's:
> SHOW COLUMNS FROM [TABLENAME];

\dt <tablename>

See \h


Fernando P. Schapachnik
Planificación de red y tecnología
VIA NET.WORKS ARGENTINA S.A.
fschapachnik@vianetworks.com.ar
Tel.: (54-11) 4323-3381

Re: SHOW

From
Stephan Bergmann
Date:
Hi!
>
> > What is the equivalent to the mysql's:
> > SHOW COLUMNS FROM [TABLENAME];
>
> in psql,  \d will show you all tables and \d TABLE will show you the
> columns in the table.  If you need it as a sql command,  you can check the
> system tables.

OK. That works for ./bin/psql, but NOT on the libpq's PQexec.
What do I have to type here?

Thanx again and lots of greetings from cool Spain
Steve

Re: SHOW

From
"Joe Conway"
Date:
> > > What is the equivalent to the mysql's:
> > > SHOW COLUMNS FROM [TABLENAME];
> >
> > in psql,  \d will show you all tables and \d TABLE will show you the
> > columns in the table.  If you need it as a sql command,  you can check
the
> > system tables.
>
> OK. That works for ./bin/psql, but NOT on the libpq's PQexec.
> What do I have to type here?
>
> Thanx again and lots of greetings from cool Spain
> Steve

Start up psql with the -E on the command line. Then do \d TABLE. This will
show you the actual SQL commands used by psql.

See the psql man page for more details.

HTH,

-- Joe

Re: SHOW

From
Stephan Bergmann
Date:
Hi Joe.

>
> Start up psql with the -E on the command line. Then do \d TABLE. This will
> show you the actual SQL commands used by psql.

Doesn't change nothing.

> See the psql man page for more details.

No hints to find there respective to the libpq's PQexec.

Greetings
Steve

Re: SHOW

From
Doug McNaught
Date:
Stephan Bergmann <sb@ows.es> writes:

> Hi Joe.
>
> >
> > Start up psql with the -E on the command line. Then do \d TABLE. This will
> > show you the actual SQL commands used by psql.
>
> Doesn't change nothing.

Hmm, works for me.  What version of Postgres are you using?

Example:

[doug@scooby doug]$ psql -E template1
********* QUERY *********
SELECT usesuper FROM pg_user WHERE usename = 'doug'
*************************

Welcome to psql, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

template1=# \d
********* QUERY *********
SELECT c.relname as "Name", 'table'::text as "Type", u.usename as "Owner"
FROM pg_class c, pg_user u
WHERE c.relowner = u.usesysid AND c.relkind = 'r'
  AND c.relname !~ '^pg_'
UNION
SELECT c.relname as "Name", 'table'::text as "Type", NULL as "Owner"
FROM pg_class c
WHERE c.relkind = 'r'
  AND not exists (select 1 from pg_user where usesysid = c.relowner)
  AND c.relname !~ '^pg_'

UNION

[more query here...]


--
Free Dmitry Sklyarov!
http://www.freesklyarov.org/

We will return to our regularly scheduled signature shortly.

Re: SHOW

From
Stephan Bergmann
Date:
Hi!
>
> Hmm, works for me.  What version of Postgres are you using?

I'm using 7.1.3 on Linux (compiled using the sources of pg).

But thanx to the output list of Oliver I now have the result:
how to get the header names of a table using the libpq. It's
very interesting, that using PostgreSQL I need to build a huge
query string:

  sprintf(pgquery2, "SELECT a.attname FROM pg_class c, pg_attribute a
WHERE c.relname = '%s' AND a.attnum > 0 AND a.attrelid = c.oid;",
ThisDBFileName);
  pgres = PQexec(pgsock2, pgquery2);


...when using e.g. MySQL I only need 4 words:

  sprintf(myquery2, "SHOW COLUMNS FROM %s", ThisDBFileName);
  if (mysql_query(mysql2, myquery2)) (...)


Thanx
Steve

Re: SHOW

From
bpalmer
Date:
The easy answer:

> > > Start up psql with the -E on the command line. Then do \d TABLE. This will
> > > show you the actual SQL commands used by psql.


select relname from pg_class where relname not like 'pg_%%';

- Brandon

----------------------------------------------------------------------------
 b. palmer,  bpalmer@crimelabs.net            pgp:crimelabs.net/bpalmer.pgp5