Thread: Newbie-question
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
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/
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
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.
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
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
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)
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
On Tue, 28 Oct 2003, [ISO-8859-1] 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. 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.
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
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. You seem to want to see the number of corresponding rows, not the stuff "swish" by - or at least, I would hope you are more interested in the count and not just text flying by... In that case, do a "select count(*) from" SQL select... Andrew Ayers Phoenix, Arizona -- CONFIDENTIALITY NOTICE -- This message is intended for the sole use of the individual and entity to whom it is addressed, and may contain informationthat is privileged, confidential and exempt from disclosure under applicable law. If you are not the intendedaddressee, nor authorized to receive for the intended addressee, you are hereby notified that you may not use, copy,disclose or distribute to anyone the message or any information contained in the message. If you have received thismessage in error, please immediately advise the sender by reply email, and delete the message. Thank you.