Thread

Newbie-question

Victor Spång Arthursson <victor@tosti.dk> — 2003-10-28T13:14:51Z

Are presently converting from mysql to postgresql, and my first 
newbiequestion is how to make all the rows in a result from a select 
just "swosh" by? That is, I dont want to see them page for page; just 
to scroll by so I can se the last line with the number of corresponding 
rows.

And is there a way to see how long time a query took to execute without 
running a EXPLAIN ANALYSE on the query?

Sincerely

Victor - Copenhagen/Malmoe

Re: Newbie-question

Jeff Trout <threshar@torgo.978.org> — 2003-10-28T13:33:57Z

On Tue, 28 Oct 2003 14:14:51 +0100
Victor Spång Arthursson <victor@tosti.dk> wrote:

> Are presently converting from mysql to postgresql, and my first 
> newbiequestion is how to make all the rows in a result from a select 
> just "swosh" by? That is, I dont want to see them page for page; just 
> to scroll by so I can se the last line with the number of corresponding 
> rows.

1. in psql, \pset pager will turn paging off. 
Although, if you really want a row count, a much better way to do that is select count(*) from [rest of select statement]

> 
> And is there a way to see how long time a query took to execute without 
> running a EXPLAIN ANALYSE on the query?
> 

in psql, use \timing and it will print how long each query you type in took.



-- 
Jeff Trout <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/

Re: Newbie-question

Victor Spång Arthursson <victor@tosti.dk> — 2003-10-28T14:10:44Z

2003-10-28 kl. 14.33 skrev Jeff:

> 1. in psql, \pset pager will turn paging off.
> Although, if you really want a row count, a much better way to do that 
> is select count(*) from [rest of select statement]

Thanks.

I'm not new to SQL, just to postgresql, so I know about the 
Count-function ;)

Reason for the question is that I want to know the number of 
corresponding rows cause I'm in a developing phase and due to needs to 
validate the result the number of rows are important.

>
> in psql, use \timing and it will print how long each query you type in 
> took.

Perfekt!

Next question:

what is the command in postresql that matches the DESCRIBE-command in 
mysql? That's, to get the fieldnames and additional info about them…

Best regards,

Victor

Re: Newbie-question

Bruno Wolff III <bruno@wolff.to> — 2003-10-28T14:11:20Z

On Tue, Oct 28, 2003 at 15:10:44 +0100,
  Victor Spång Arthursson <victor@tosti.dk> wrote:
> 
> what is the command in postresql that matches the DESCRIBE-command in 
> mysql? That's, to get the fieldnames and additional info about them

If you are using psql, then use \d tablename . \? will tell you about
other \ commands.

Re: Newbie-question

Brendan Jurd <blakjak@blakjak.sytes.net> — 2003-10-28T14:24:56Z

If you're querying from within the psql client program (and it seems 
from your question that you are), then the results are reported back via 
the utility "less" (or a less-like program internal to psql, can someone 
else clarify?) if the results are more than a single page in length.  I 
don't think you can make the results "swosh", but you can instantly move 
to the bottom of the output by hitting "G".  That's uppercase "G", not 
to be confused with lowercase "g", which is a different command and will 
take you to the *top* of the output.

I hope that answers your first question.

As for getting timing information on your queries, the psql client will 
do this for you if you enable the option by entering "\timing".  Query 
timing is off by default, so ordinarily you'd need to enable it every 
time you start a new psql session.

Good hunting

BJ

Victor Spång Arthursson wrote:

> Are presently converting from mysql to postgresql, and my first 
> newbiequestion is how to make all the rows in a result from a select 
> just "swosh" by? That is, I dont want to see them page for page; just 
> to scroll by so I can se the last line with the number of 
> corresponding rows.
>
> And is there a way to see how long time a query took to execute 
> without running a EXPLAIN ANALYSE on the query?
>
> Sincerely
>
> Victor - Copenhagen/Malmoe
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faqs/FAQ.html

Re: Newbie-question

Victor Spång Arthursson <victor@tosti.dk> — 2003-10-28T15:22:13Z

2003-10-28 kl. 16.02 skrev Tino Wildenhain:

> Most prominent example is PgAdmin http://pgadmin.postgresql.org/
> or pgacess, and more...

Thanks for the fast answer! There doesn't seem to be any pgadmin for OS 
X, but perhaps it's possible to run in X11… I also tried out the 
phppgadmin, which I found lacking some important features that are 
availible in its counterpart for mysql, the phpmyadmin; for example the 
possibility to set autoincrement on fields.

Anyway, thats possible to do in the client, of course, so I wont 
complain ;)

Going to check the man and help right away for the DESCRIBE-command.

Sincerely,

/.v

Re: Newbie-question

Alvaro Herrera <alvherre@dcc.uchile.cl> — 2003-10-28T15:27:46Z

On Wed, Oct 29, 2003 at 01:24:56AM +1100, Brendan Jurd wrote:
> If you're querying from within the psql client program (and it seems 
> from your question that you are), then the results are reported back via 
> the utility "less" (or a less-like program internal to psql, can someone 
> else clarify?) if the results are more than a single page in length.

I think it's whatever you set PAGER to; if it is unset, it will use
"more".

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Llegará una época en la que una investigación diligente y prolongada sacará
a la luz cosas que hoy están ocultas" (Séneca, siglo I)

Re: Newbie-question

Andrew Sullivan <andrew@libertyrms.info> — 2003-10-28T15:28:41Z

On Wed, Oct 29, 2003 at 01:24:56AM +1100, Brendan Jurd wrote:
> from your question that you are), then the results are reported back via 
> the utility "less" (or a less-like program internal to psql, can someone 
> else clarify?) 

Actually, it's your $PAGER environment variable (and there's the
usual UNIX-y ways of handling it if $PAGER is not set).  So you can
make it anything you want.

In 7.3.x and later, you can also do \pset pager to turn off the pager
in psql.

A

-- 
----
Andrew Sullivan                         204-4141 Yonge Street
Afilias Canada                        Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110

Re: Newbie-question

Stephan Szabo <sszabo@megazone.bigpanda.com> — 2003-10-28T15:38:28Z

On Tue, 28 Oct 2003, [ISO-8859-1] Victor Spng Arthursson wrote:

> Are presently converting from mysql to postgresql, and my first
> newbiequestion is how to make all the rows in a result from a select
> just "swosh" by? That is, I dont want to see them page for page; just
> to scroll by so I can se the last line with the number of corresponding
> rows.

I believe
\pset pager off
will turn off the pager in psql

> And is there a way to see how long time a query took to execute without
> running a EXPLAIN ANALYSE on the query?

In psql you can also use \timing to turn on/off a printing of how many ms
the query took (including data transfer time iirc) after the result set is
printed.

Re: Newbie-question

Douglas McNaught <doug@mcnaught.org> — 2003-10-28T15:38:43Z

Victor Spång Arthursson <victor@tosti.dk> writes:

> Thanks for the fast answer! There doesn't seem to be any pgadmin for
> OS X, but perhaps it's possible to run in X11 I also tried out the
> phppgadmin, which I found lacking some important features that are
> availible in its counterpart for mysql, the phpmyadmin; for example
> the possibility to set autoincrement on fields.

That's done by declaring the column as type SERIAL in the first
place.  You can add it after the fact by creating a sequence and doing 
ALTER TABEL ALTER COLUMN ADD DEFAULT nextval('myseq')--check the docs
for the exact syntax.  I don't know if phppgadmin lets you modify
defaults easily, but you can always just send the above query
directly...

-Doug