[GENERAL] Viewing Database Scheme

[Rich Shepard]

  I'm trying to help the XRMS developers add postgres support ('cause that's
what I use). They've done well so far with help from other postgres users,
but now I've been asked to help getting the indices correct.

  When installing the application I specified the database name as 'contacts'
(not very innovative or clever, but descriptive). When I open the database
with 'psql contacts' and ask to have the tables dumped (with \d), they go
streaming by on the display. Of course, the bash 'tee' or 'less' commands
don't work to allow me to capture the stream to a file or page through the
output.

  I've looked in the 8.1 pdf manual and the Douglas*2 book without spotting
the command I need to get a list of all tables and their fields. When I've
developed postgres applications I know the schema so this has not been an
issue before.

  Please pass me a clue stick on how to view all the tables in this
application.

TIA,

Rich

--
Richard B. Shepard, Ph.D.   |   Author of Quantifying Environmental
Applied Ecosystem Services, Inc. (TM)   |  Impact Assessments Using Fuzzy Logic
http://www.appl-ecosys.com Voice: 503-667-4517 Fax: 503-667-8863

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Viewing Database Scheme

[Jim Buttafuoco]

use pg_dump --schema-only 




-- Original Message ---
From: Rich Shepard [EMAIL PROTECTED]
To: pgsql-general@postgresql.org
Sent: Sat, 28 Jan 2006 10:14:05 -0800 (PST)
Subject: [GENERAL] Viewing Database Scheme

 I'm trying to help the XRMS developers add postgres support ('cause that's
 what I use). They've done well so far with help from other postgres users,
 but now I've been asked to help getting the indices correct.
 
When installing the application I specified the database name as 'contacts'
 (not very innovative or clever, but descriptive). When I open the database
 with 'psql contacts' and ask to have the tables dumped (with \d), they go
 streaming by on the display. Of course, the bash 'tee' or 'less' commands
 don't work to allow me to capture the stream to a file or page through the
 output.
 
I've looked in the 8.1 pdf manual and the Douglas*2 book without spotting
 the command I need to get a list of all tables and their fields. When I've
 developed postgres applications I know the schema so this has not been an
 issue before.
 
Please pass me a clue stick on how to view all the tables in this
 application.
 
 TIA,
 
 Rich
 
 -- 
 Richard B. Shepard, Ph.D.   |   Author of Quantifying 
 Environmental
 Applied Ecosystem Services, Inc. (TM)   |  Impact Assessments Using Fuzzy 
 Logic
 http://www.appl-ecosys.com Voice: 503-667-4517 Fax: 503-667-8863
 
 ---(end of broadcast)---
 TIP 5: don't forget to increase your free space map settings
--- End of Original Message ---


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Viewing Database Scheme

[Ezra Taylor]

Can you use redirection.

On 1/28/06, Rich Shepard [EMAIL PROTECTED] wrote:
   I'm trying to help the XRMS developers add postgres support ('cause that's
 what I use). They've done well so far with help from other postgres users,
 but now I've been asked to help getting the indices correct.

   When installing the application I specified the database name as 'contacts'
 (not very innovative or clever, but descriptive). When I open the database
 with 'psql contacts' and ask to have the tables dumped (with \d), they go
 streaming by on the display. Of course, the bash 'tee' or 'less' commands
 don't work to allow me to capture the stream to a file or page through the
 output.

   I've looked in the 8.1 pdf manual and the Douglas*2 book without spotting
 the command I need to get a list of all tables and their fields. When I've
 developed postgres applications I know the schema so this has not been an
 issue before.

   Please pass me a clue stick on how to view all the tables in this
 application.

 TIA,

 Rich

 --
 Richard B. Shepard, Ph.D.   |   Author of Quantifying 
 Environmental
 Applied Ecosystem Services, Inc. (TM)   |  Impact Assessments Using Fuzzy 
 Logic
 http://www.appl-ecosys.com Voice: 503-667-4517 Fax: 503-667-8863

 ---(end of broadcast)---
 TIP 5: don't forget to increase your free space map settings



--
Ezra Taylor

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Viewing Database Scheme

[Doug McNaught]

Rich Shepard [EMAIL PROTECTED] writes:

When installing the application I specified the database name as 'contacts'
 (not very innovative or clever, but descriptive). When I open the database
 with 'psql contacts' and ask to have the tables dumped (with \d), they go
 streaming by on the display. Of course, the bash 'tee' or 'less' commands
 don't work to allow me to capture the stream to a file or page through the
 output.

You can either use 'pg_dump --schema-only' as another poster
suggested, or use the '\o' command in psql.

-Doug

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Viewing Database Scheme

[Tom Lane]

Rich Shepard [EMAIL PROTECTED] writes:
 When I open the database
 with 'psql contacts' and ask to have the tables dumped (with \d), they go
 streaming by on the display. Of course, the bash 'tee' or 'less' commands
 don't work to allow me to capture the stream to a file or page through the
 output.

Not sure why you say of course there.  \d output is properly paginated
for me, and I believe for most people.  What platform are you on, and
what do you have environment variable PAGER set to?  Is the output of
plain old SELECT commands paginated for you?

I've looked in the 8.1 pdf manual and the Douglas*2 book without spotting
 the command I need to get a list of all tables and their fields.

There is not a single command; you use queries against the system
catalogs for purposes like this.  The system catalogs chapter of
the manual gives the details, but you can get a leg up by looking
at the queries psql uses for whatever form of \d seems closest to
what you want.  Start psql with -E option to make it echo the
queries it's using.

regards, tom lane

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] Viewing Database Scheme

[Rich Shepard]

On Sat, 28 Jan 2006, Tom Lane wrote:


Not sure why you say of course there.  \d output is properly paginated
for me, and I believe for most people.  What platform are you on, and
what do you have environment variable PAGER set to?  Is the output of
plain old SELECT commands paginated for you?


Tom,

  Running Slackware-10.2 with 'less' as the pager.

  Here's what I'm seeing:

contacts=# \d | less
\d: extra argument less ignored

  I can, however, run '\dt' and have it page normally. But, I cannot write
that output to a file using redirection or the tee command:

contacts=# \dt  xrms.tables
No matching relations found.
\dt: extra argument xrms.tables ignored

and if I enter
contacts=# \dt | tee  xrms.tables

I see
 ...
 public | user_preference_type_options   | table | rshepard
 public | users  | table | rshepard
(57 rows)

\dt: extra argument tee ignored
\dt: extra argument  ignored
\dt: extra argument xrms.tables ignored

  Now, quitting postgres and reinvoking psql does fix the scroll-too-far
problem. But, something's not quite correct here; to wit:

contacts=# pg_dump --schema-only  xrms.txt;
contacts-# ;
ERROR:  syntax error at or near pg_dump at character 1
LINE 1: pg_dump


There is not a single command; you use queries against the system catalogs
for purposes like this. The system catalogs chapter of the manual gives
the details, but you can get a leg up by looking at the queries psql uses
for whatever form of \d seems closest to what you want. Start psql with -E
option to make it echo the queries it's using.


  I'll be sure to read that section. The \dt and \di commands show me what I
want, but I cannot redirect output to a file. What am I still missing,
please?

Thanks,

Rich
--
Richard B. Shepard, Ph.D.   |   Author of Quantifying Environmental
Applied Ecosystem Services, Inc. (TM)   |  Impact Assessments Using Fuzzy Logic
http://www.appl-ecosys.com Voice: 503-667-4517 Fax: 503-667-8863

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] Viewing Database Scheme

[Rich Shepard]

On Sat, 28 Jan 2006, Doug McNaught wrote:


You can either use 'pg_dump --schema-only' as another poster
suggested, or use the '\o' command in psql.


  When I try 'pg_dump --schema-only' I get a continuation prompt, even with a
semicolon at the end of the command line. A second semicolon produces this:

contacts=# pg_dump --schema-only;
contacts-# ;
ERROR:  syntax error at or near pg_dump at character 1
LINE 1: pg_dump
^
  If I use the \o command (with or without a file name) I get the command
prompt and no results.

Rich

--
Richard B. Shepard, Ph.D.   |   Author of Quantifying Environmental
Applied Ecosystem Services, Inc. (TM)   |  Impact Assessments Using Fuzzy Logic
http://www.appl-ecosys.com Voice: 503-667-4517 Fax: 503-667-8863

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [GENERAL] Viewing Database Scheme

[Rich Shepard]

On Sat, 28 Jan 2006, Ezra Taylor wrote:


Can you use redirection.


  No. I get an error message.

contacts=# \dt  xrms.tables
No matching relations found.
\dt: extra argument xrms.tables ignored

Rich

--
Richard B. Shepard, Ph.D.   |   Author of Quantifying Environmental
Applied Ecosystem Services, Inc. (TM)   |  Impact Assessments Using Fuzzy Logic
http://www.appl-ecosys.com Voice: 503-667-4517 Fax: 503-667-8863

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Viewing Database Scheme

[Eric B. Ridge]

On Jan 28, 2006, at 3:20 PM, Rich Shepard wrote:

contacts=# \d | less
\d: extra argument less ignored


You can't do this via the psql prompt.  A simple \d will output to  
the screen, automatically using your $PAGER if the output is too long  
to fit on your screen.


  I can, however, run '\dt' and have it page normally. But, I  
cannot write

that output to a file using redirection or the tee command:

contacts=# \dt  xrms.tables
No matching relations found.
\dt: extra argument xrms.tables ignored


Again, you can't use redirection via the psql prompt.  But you can do  
it via your shell command line:


$ psql -c \dt  xrms.tables

Alternatively, you can use psql's \o [FILE] command to redirect  
query results to a file:


contacts=# \o /tmp/xrms.tables
contacts=# \dt
contacts=#

That'll send all output to /tmp/xrms.tables.

  I'll be sure to read that section. The \dt and \di commands show  
me what I

want, but I cannot redirect output to a file. What am I still missing,
please?


You should also read the psql man page and the output of psql's \h  
command.


eric

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [GENERAL] Viewing Database Scheme

[Rich Shepard]

On Sat, 28 Jan 2006, Eric B. Ridge wrote:


You can't do this via the psql prompt.  A simple \d will output to the
screen, automatically using your $PAGER if the output is too long to fit on
your screen.


Eric,

  That's what I assumed; perhaps I misunderstood Tom Lane's what do you mean
'ofcourse'?.


Again, you can't use redirection via the psql prompt.  But you can do it
via your shell command line:

$ psql -c \dt  xrms.tables


  Well, that doesn't seem to be working here, either:

[EMAIL PROTECTED] ~]$ psql -c contacts \dt  xrms.tables
psql: FATAL:  database \dt does not exist

[EMAIL PROTECTED] ~]$ psql -c contacts \dt  xrms.tables
psql: FATAL:  database rshepard does not exist

[EMAIL PROTECTED] ~]$ psql -c contacts
psql: FATAL:  database rshepard does not exist

Alternatively, you can use psql's \o [FILE] command to redirect query 
results to a file:


contacts=# \o /tmp/xrms.tables
contacts=# \dt
contacts=#

That'll send all output to /tmp/xrms.tables.


  This creates the file, but it's empty.

  I'm curious what's gone wrong here. Nothing seems to be working as it
should.


You should also read the psql man page and the output of psql's \h command.


  I've done both and tried various combinations of syntax. For example:

[EMAIL PROTECTED] ~]$ psql -d contacts -c pg_dump  -o xrms.tables
ERROR:  syntax error at or near pg_dump at character 1
LINE 1: pg_dump

  All I get are error messages.

Thanks,

Rich

--
Richard B. Shepard, Ph.D.   |   Author of Quantifying Environmental
Applied Ecosystem Services, Inc. (TM)   |  Impact Assessments Using Fuzzy Logic
http://www.appl-ecosys.com Voice: 503-667-4517 Fax: 503-667-8863

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] Viewing Database Scheme

[Eric B. Ridge]

On Jan 28, 2006, at 4:12 PM, Rich Shepard wrote:

Please keep replies on the mailing list.

snip

Again, you can't use redirection via the psql prompt.  But you can  
do it

via your shell command line:

$ psql -c \dt  xrms.tables


  Well, that doesn't seem to be working here, either:

[EMAIL PROTECTED] ~]$ psql -c contacts \dt  xrms.tables
psql: FATAL:  database \dt does not exist

[EMAIL PROTECTED] ~]$ psql -c contacts \dt  xrms.tables
psql: FATAL:  database rshepard does not exist

[EMAIL PROTECTED] ~]$ psql -c contacts
psql: FATAL:  database rshepard does not exist


That's because you've used the wrong syntax.

$ psql contacts -c \dt  xrms.tables

This is why I suggested you read the psql man page.

Alternatively, you can use psql's \o [FILE] command to redirect  
query results to a file:

  This creates the file, but it's empty.

  I'm curious what's gone wrong here. Nothing seems to be working  
as it

should.


Likely, the output is buffered.  Did you try quitting psql (via \q)  
before checking the contents of the file.


You should also read the psql man page and the output of psql's  
\h command.


  I've done both and tried various combinations of syntax. For  
example:


[EMAIL PROTECTED] ~]$ psql -d contacts -c pg_dump  -o xrms.tables
ERROR:  syntax error at or near pg_dump at character 1
LINE 1: pg_dump


Dude, pg_dump is not a psql command, nor is it a SQL command.  It's  
a command-line program.  You run it from your shell:


$ pg_dump --schema-only pg_dump  xrms-schema.dmp


  All I get are error messages.


You continually do the wrong things.  Read the man pages.  Seriously.

eric

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] Viewing Database Scheme

[Eric B . Ridge]

On Jan 28, 2006, at 4:20 PM, Eric B. Ridge wrote:

Dude, pg_dump is not a psql command, nor is it a SQL command.   
It's a command-line program.  You run it from your shell:


$ pg_dump --schema-only pg_dump  xrms-schema.dmp


pardon my type-o.  This should read:

$ pg_dump --schema-only contacts  xrms-schema.dmp

eric

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] Viewing Database Scheme

[Doug McNaught]

Rich Shepard [EMAIL PROTECTED] writes:

 On Sat, 28 Jan 2006, Eric B. Ridge wrote:

 Again, you can't use redirection via the psql prompt.  But you can do it
 via your shell command line:
 $ psql -c \dt  xrms.tables

Well, that doesn't seem to be working here, either:

 [EMAIL PROTECTED] ~]$ psql -c contacts \dt  xrms.tables
 psql: FATAL:  database \dt does not exist

 [EMAIL PROTECTED] ~]$ psql -c contacts \dt  xrms.tables
 psql: FATAL:  database rshepard does not exist

 [EMAIL PROTECTED] ~]$ psql -c contacts
 psql: FATAL:  database rshepard does not exist

Eric left off the database argument (which defaults to your user
name), which was a little misleading, but his syntax does work:

[EMAIL PROTECTED]:~$ psql -c '\dt' gateway
List of relations
 Schema | Name | Type  |  Owner   
+--+---+--
...

Redirecting to a file is left as an exercise to the reader.

 Alternatively, you can use psql's \o [FILE] command to redirect
 query results to a file:
 contacts=# \o /tmp/xrms.tables
 contacts=# \dt
 contacts=#
 That'll send all output to /tmp/xrms.tables.

This creates the file, but it's empty.

When I do this, after exiting 'psql' the file is populated.  It may be
a buffering issue, as another poster has said.  What you can also do
is close the file by setting output back to the terminal:

gateway=# \o /tmp/foo
gateway=# \dt
gateway=# \!cat /tmp/foo   --- empty at this point
gateway=# \o   --- switch output to the terminal
gateway=# \!cat /tmp/foo   --- now it's there
List of relations
 Schema | Name | Type  |  Owner   
+--+---+--


I'm really surprised that you managed to think 'pg_dump --schema_only'
is an SQL command.  It's listed nowhere in the SQL syntax reference,
and is listed in the manual as one of the utility commands that are
run from the shell.

Your flailing about, randomly trying different argument combinations,
suggests that you aren't understanding what you read, and aren't
bothering to try to understand the error messages you get.  You could
have figured out the first issue, certainly, by reading manpages and
error messages.  The second one is a bit tricky unless you understand
Unix stdio buffering, which I wouldn't necessarily expect.  So I'll
give you that one.  :)

-Doug

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Viewing Database Scheme

[Roderick A. Anderson]

Rich Shepard wrote:
  I'm trying to help the XRMS developers add postgres support ('cause 
that's

what I use). They've done well so far with help from other postgres users,
but now I've been asked to help getting the indices correct.


Hi Rich.  Seems once again we're meeting in the same places.  The 
Internet sure seems small sometimes.


I saw all the other posts but you might look at dbwrench ( a Java 
application ).  Does a lot more than what you need ( plus after 30 days 
costs approx. $150 ) but our lead programmer is using it to reverse 
engineer a MySQL database he shoe-horned into PostgreSQL.  He loves it.


It has several output formats.

snip /


Rod
--

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] Viewing Database Scheme

[Rich Shepard]

On Sat, 28 Jan 2006, Eric B. Ridge wrote:


That's because you've used the wrong syntax.

$ psql contacts -c \dt  xrms.tables

This is why I suggested you read the psql man page.


  Well, the man page installed shows the -c option is to specify one command,
but when I try that:

[EMAIL PROTECTED] ~]$ psql -c \dt  xrms.tables
psql: FATAL:  database rshepard does not exist

  That's why I tried specifying the database name.

Likely, the output is buffered.  Did you try quitting psql (via \q) before 
checking the contents of the file.


  Yes.

Dude, pg_dump is not a psql command, nor is it a SQL command.  It's a 
command-line program.  You run it from your shell:


$ pg_dump --schema-only pg_dump  xrms-schema.dmp


[EMAIL PROTECTED] ~]$ pg_dump --schema-only pg_dump  xrms-schema.dmp
pg_dump: [archiver (db)] connection to database pg_dump failed: FATAL:
database pg_dump does not exist

  However, as the pg_dump man page specifies, the database name needs to be
listed on the coammand line. So,

$ pg_dump -s contacts  xrms.tables

works just fine.

  I'm still curious why I cannot do this within psql.

Rich

--
Richard B. Shepard, Ph.D.   |   Author of Quantifying Environmental
Applied Ecosystem Services, Inc. (TM)   |  Impact Assessments Using Fuzzy Logic
http://www.appl-ecosys.com Voice: 503-667-4517 Fax: 503-667-8863

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [GENERAL] Viewing Database Scheme

[Rich Shepard]

On Sat, 28 Jan 2006, Roderick A. Anderson wrote:


Hi Rich.  Seems once again we're meeting in the same places.  The Internet
sure seems small sometimes.


  Hello, again, Rod.


I saw all the other posts but you might look at dbwrench (a Java
application). Does a lot more than what you need (plus after 30 days costs
approx. $150) but our lead programmer is using it to reverse engineer a
MySQL database he shoe-horned into PostgreSQL. He loves it.


  There has been someone else working on this effort to move XRMS from
strictly MySQL to PostgreSQL, and he's been at it longer. But, it's almost
there now.

  The install script generated an error message with every create statement,
but the tables were created anyway. But, the indices are wrong and the dates
have screwy values. I printed the web page as a .ps file and sent that to the
developers. They told me what the most likely cause was, so I'll go through
the install.xml file and make their recommended changes, then re-run the
install script. So this is very close to working and it's a one-shot deal for
me.

  At OSCON last summer I spoke with the SugarCRM sales folks. They said that
they received many requiests to add PostgreSQL support, but management wants
to stick with MySQL. You'll recall from the other mail list that I had a time
getting MySQL properly installed and configured just to run this one
application. After trying it for a while I discovered that I really didn't
like it. I think XRMS will do much better.

  On the other side of this thread, the command line pg_dump worked with the
proper syntax. I think it was the two original replies (one with a command
line solution, the other with a psql solution) that confused me. Why none of
the psql commands are working for me I don't know. But, pg_dump works just
fine. Thanks to everyone.

Rich

--
Richard B. Shepard, Ph.D.   |   Author of Quantifying Environmental
Applied Ecosystem Services, Inc. (TM)   |  Impact Assessments Using Fuzzy Logic
http://www.appl-ecosys.com Voice: 503-667-4517 Fax: 503-667-8863

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org