SQL statement : list table details

On Thu, 2002-02-28 at 19:59, Samik Raychauhduri wrote:

Hi,
Thanks for this query BTW, I was looking for a query like this :)
Let's see if anybody comes up with any solution to the data types.

Do this:

$ psql -E
nconway=> \d my_table

This will display the queries that psql uses to determine the data types
of a table. The same holds true for all the other psql 'slash commands'.

Cheers,

Neil

--
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC

Hi,
Thanks for this query BTW, I was looking for a query like this :)
Let's see if anybody comes up with any solution to the data types.
-samik

Dino Hoboloney wrote:

Here are a few that may help you:

Getting database names:
SELECT datname AS database
FROM pg_database;

Getting table names for the current database:
SELECT tablename
FROM pg_tables
WHERE tablename !~* 'pg_*'

Getting the columns for a table ($table is tablename from above):
SELECT a.attnum, a.attname AS field, t.typname AS type,
a.attlen AS length, a.atttypmod AS length_var,
a.attnotnull AS not_null, a.atthasdef as has_default
FROM pg_class c, pg_attribute a, pg_type t
WHERE c.relname = '$table'
AND a.attnum > 0
AND a.attrelid = c.oid
AND a.atttypid = t.oid
ORDER BY a.attnum;

Get default value for a given field ($table is from 2 above, and $field is
from attnum above):
SELECT a.adsrc AS field_default
FROM pg_attrdef a, pg_class c
WHERE c.relname = '$table'
AND c.oid = a.adrelid
AND a.adnum = $field;

I hope this helps a bit.

Rob

-----Original Message-----
From: Samik Raychauhduri [mailto:samik@cae.wisc.edu]
Sent: Thursday, February 28, 2002 4:59 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] SQL statement : list table details

Hi,
Thanks for this query BTW, I was looking for a query like this :)
Let's see if anybody comes up with any solution to the data types.
-samik

Dino Hoboloney wrote:

I am looking for a SQL statement which would list table names,

columns,

and column types of a specific DB. So far I have managed to find a

statement

that lists all of the tables and columns in the DB with

SELECT a.attnum, a.attname AS field, c.relname AS table_name FROM pg_class
c, pg_attribute a WHERE c.relname NOT LIKE 'pg%' AND relkind = 'r' AND
a.attnum > 0 AND a.attrelid = c.oid ORDER BY table_name, attnum;

unfortunately I am unable to come up with a solution to listing the data
types for the columns listed. Any ideas?

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

On Thu, 28 Feb 2002, Samik Raychauhduri wrote:

Hi,
Thanks for this query BTW, I was looking for a query like this :)
Let's see if anybody comes up with any solution to the data types.
-samik

Dino Hoboloney wrote:

I am looking for a SQL statement which would list table names, columns,
and column types of a specific DB. So far I have managed to find a statement
that lists all of the tables and columns in the DB with

SELECT a.attnum, a.attname AS field, c.relname AS table_name FROM pg_class
c, pg_attribute a WHERE c.relname NOT LIKE 'pg%' AND relkind = 'r' AND
a.attnum > 0 AND a.attrelid = c.oid ORDER BY table_name, attnum;

unfortunately I am unable to come up with a solution to listing the data
types for the columns listed. Any ideas?

If you're running 7.2, I think format_type(a.atttypid, a.atttypmod) in
the select list will give you the human readable type.

Im using this one

SELECT pg_class.relname AS table_name,
pg_attribute.attname AS column_name,
pg_type.typname AS data_type
FROM pg_class,
pg_attribute,
pg_type
WHERE pg_class.relfilenode = pg_attribute.attrelid
AND pg_type.oid = pg_attribute.atttypid
AND pg_class.relname not like 'pg_%'
AND pg_attribute.atttypmod > 0
AND pg_class.reltype > 0
ORDER BY pg_class.relname, pg_attribute.attname

think I answered my own question ....

SELECT a.attnum, a.attname AS field, a.atttypid AS column_type, a.atttypmod
AS type_specific, c.relname AS table_name FROM pg_class c, pg_attribute a
WHERE c.relname NOT LIKE 'pg%' AND relkind = 'r' AND a.attnum > 0 AND
a.attrelid = c.oid ORDER BY table_name, attnum;

this will now list column_type, and type_specific which is type-specific
data such as varchar length.

On Thu, 2002-02-28 at 18:57, Dino Hoboloney wrote:

I am looking for a SQL statement which would list table names, columns,
and column types of a specific DB. So far I have managed to find a statement
that lists all of the tables and columns in the DB with

SELECT a.attnum, a.attname AS field, c.relname AS table_name FROM pg_class
c, pg_attribute a WHERE c.relname NOT LIKE 'pg%' AND relkind = 'r' AND
a.attnum > 0 AND a.attrelid = c.oid ORDER BY table_name, attnum;

unfortunately I am unable to come up with a solution to listing the data
types for the columns listed. Any ideas?

SELECT a.attnum, a.attname AS field, t.typname AS type, c.relname AS
table_name FROM pg_class c, pg_attribute a, pg_type t WHERE c.relname
NOT LIKE 'pg%' AND relkind = 'r' AND a.attnum > 0 AND a.attrelid = c.oid
AND a.atttypid = t.oid ORDER BY table_name, attnum;

If you want refinements like the number of characters in a varchar, or
the precsion and scale of a numeric, you need to do things with
a.atttypmod which I can't currently remember.

--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C

"Give, and it will be given to you. A good measure,
pressed down, taken together and running over,
will be poured into your lap. For with the same
measure that you use, it will be measured to
you." Luke 6:38

Hi all,

I did not find any kind of print statement in psql or plpgsql.
So, how can I create basic report with psql and/or plpgsql?
Nothing fancy, page header/footer sub-totals per page.

JLL

On Tue, Mar 19, 2002 at 01:07:58PM -0500, Jean-Luc Lachance wrote:

I did not find any kind of print statement in psql or plpgsql.
So, how can I create basic report with psql and/or plpgsql?
Nothing fancy, page header/footer sub-totals per page.

Errr... psql is a database query tool. There's not much available
for doing that kind of thing. That said, why not just write a
script to do it:

#!/bin/sh
echo <<END
<html><head><title>This is a title</title></head>
<body>
<!-- header -->
END

# do the query
psql --html --command 'SELECT * FROM foo' my_dbase

echo <<END
<!-- footer -->
</body></html>
END

Of course if you really insist on doing it in psql, you could just
write SELECT statements that select a preset string. Ugly, but
it would work:

SELECT '<html><head><title>... '::text;

--
Andrew G. Hammond mailto:drew@xyzzy.dhs.org http://xyzzy.dhs.org/~drew/
56 2A 54 EF 19 C0 3B 43 72 69 5B E3 69 5B A1 1F 613-389-5481
5CD3 62B0 254B DEB1 86E0 8959 093E F70A B457 84B1
"To blow recursion you must first blow recur" -- me

Hello Andrew,

I do not need HTML, just plain text or maybe PDF.

It would be nice if there was one extra level for RAISE as in:
RAISE PRINT 'Whatever'
That would not send the message the log but only to the console.
One could do alot with that.

JLL

"Andrew G. Hammond" wrote:

On Tue, Mar 19, 2002 at 01:33:13PM -0500, Jean-Luc Lachance wrote:

I do not need HTML, just plain text

So just do it in plain text then:

#!/bin/sh
echo <<END
This is plain text. It's not that hard.
END

psql --command 'SELECT * FROM foo' my_dbase

echo <<END
This is more plain text. Still pretty simple, eh?
END

You may also want to RTFM in the psql manual about \pset and the -f
option.

or maybe PDF.

Please don't use bad words. PDF is a proprietary format. Beware.

It would be nice if there was one extra level for RAISE as in:
RAISE PRINT 'Whatever'
That would not send the message the log but only to the console.
One could do alot with that.

Something wrong with using
SELECT 'whatever';
or didn't you even read as far as the end of my first message?

"Andrew G. Hammond" wrote:

On Tue, Mar 19, 2002 at 01:07:58PM -0500, Jean-Luc Lachance wrote:

I did not find any kind of print statement in psql or plpgsql.
So, how can I create basic report with psql and/or plpgsql?
Nothing fancy, page header/footer sub-totals per page.

Errr... psql is a database query tool. There's not much available
for doing that kind of thing. That said, why not just write a
script to do it:

#!/bin/sh
echo <<END
<html><head><title>This is a title</title></head>
<body>
<!-- header -->
END

# do the query
psql --html --command 'SELECT * FROM foo' my_dbase

echo <<END
<!-- footer -->
</body></html>
END

--
Andrew G. Hammond mailto:drew@xyzzy.dhs.org http://xyzzy.dhs.org/~drew/
56 2A 54 EF 19 C0 3B 43 72 69 5B E3 69 5B A1 1F 613-389-5481
5CD3 62B0 254B DEB1 86E0 8959 093E F70A B457 84B1
"To blow recursion you must first blow recur" -- me

Jean-Luc Lachance wrote:

Hello Andrew,

I do not need HTML, just plain text or maybe PDF.

It would be nice if there was one extra level for RAISE as in:
RAISE PRINT 'Whatever'
That would not send the message the log but only to the console.
One could do alot with that.

7.3 will have that. It will be RAISE INFO, which goes only to the
client. RAISE LOG goes only to the server logs.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Jean-Luc Lachance wrote:

Hello Bruce,

How about simply PRINT; maybe something like a printf.

Just as long as there will be no prefix like 'INFO:' or 'NOTICE:'

Indeed, there will be a prefix if INFO: or NOTICE:. No one has asked
for those to be removed before.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Hello Bruce,

How about simply PRINT; maybe something like a printf.

Just as long as there will be no prefix like 'INFO:' or 'NOTICE:'

JLL

Bruce Momjian wrote:

Well, count my vote.
Any body else who see the usefullness of a PRINT statement?

Bruce Momjian wrote:

Wes, I Agree,

but I am thinking of psql here. PSQL is a front-end to PostgreSQL. Is it
not?
We could still use the RAISE NOTICE or RAISE INFO on the back-end, but
PSQL should strip
the INFO and/or NOTICE prefix.

Mind you, from the back-end perspective, a RAISE INFO or NOTICE is just
like a printf.

JLL

wsheldah@lexmark.com wrote:

On Tue, Mar 19, 2002 at 01:33:13PM -0500, Jean-Luc Lachance wrote:

Hello Andrew,

I do not need HTML, just plain text or maybe PDF.

It would be nice if there was one extra level for RAISE as in:
RAISE PRINT 'Whatever'
That would not send the message the log but only to the console.
One could do alot with that.

s/alot/a lot/

You could use one of the other PL languages to do that. I'm sure the PL/*U
languages do that, but perhaps the other regular PL languages would do as
well (Tcl, Python, Perl).

-Roberto

-- 
+----| http://fslc.usu.edu/ USU Free Software & GNU/Linux Club |------+
  Roberto Mello - Computer Science, USU - http://www.brasileiro.net/ 
       http://www.sdl.usu.edu/ - Space Dynamics Lab, Developer    

On Tue, Mar 19, 2002 at 02:42:52PM -0500, Jean-Luc Lachance wrote:

Well, count my vote.
Any body else who see the usefullness of a PRINT statement?

what's wrong with:

$ psql -t -c "select 'Hello World!'" template1
Hello World!

$

Jean-Luc Lachance writes:

I do not need HTML, just plain text or maybe PDF.

psql can generate LaTeX tables, which you can convert to PDF.

--
Peter Eisentraut peter_e@gmx.net