pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedpsql question
11+ messages / 9 participants
[nested] [flat]
* psql question
@ 2000-11-20 01:38 Joe Conway <joe@conway-family.com>
2000-11-23 18:31 ` Re: psql question Peter Eisentraut <peter_e@gmx.net>
2000-11-24 04:06 ` Re: psql question bmccoy@chapelperilous.net
0 siblings, 2 replies; 11+ messages in thread
From: Joe Conway @ 2000-11-20 01:38 UTC (permalink / raw)
To: pgsql-sql
Hello,
I've been searching off-and-on for an answer to this question, but I haven't
found anything but fleeting references.
Here's the problem/question:
On machines where I've installed PostgreSQL 7.0.2 from RPM, psql allows use
of the up arrow key for history and the escape/tab key for command
completion, but on my remote web host (webpipe.net) those keys don't work.
What do I need to do to get these features working on my remote web host?
I've seen reference to .psqlrc in the psql man file, but not much else (no
syntax, etc).
Thanks in advance!
Joe
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: psql question
2000-11-20 01:38 psql question Joe Conway <joe@conway-family.com>
@ 2000-11-23 18:31 ` Peter Eisentraut <peter_e@gmx.net>
2000-11-23 19:50 ` Re: psql question Mathijs Brands <mathijs@ilse.nl>
2000-11-23 22:01 ` Re: psql question Joe Conway <joe@conway-family.com>
1 sibling, 2 replies; 11+ messages in thread
From: Peter Eisentraut @ 2000-11-23 18:31 UTC (permalink / raw)
To: Joe Conway <joe@conway-family.com>; +Cc: pgsql-sql
Joe Conway writes:
> On machines where I've installed PostgreSQL 7.0.2 from RPM, psql allows use
> of the up arrow key for history and the escape/tab key for command
> completion, but on my remote web host (webpipe.net) those keys don't work.
> What do I need to do to get these features working on my remote web host?
> I've seen reference to .psqlrc in the psql man file, but not much else (no
> syntax, etc).
If you compiled from source, make sure you had the readline-devel package
installed.
$ man psql
<snip>
If you have the readline library installed but psql does
not seem to use it, you must make sure that Postgres's
top-level configure script finds it. configure needs to
find both the library libreadline.a (or a shared library
equivalent) and the header files readline.h and history.h
(or readline/readline.h and readline/history.h) in appro
priate directories. If you have the library and header
files installed in an obscure place you must tell config
ure about them, for example:
$ ./configure --with-includes=/opt/gnu/include --with-libs=/opt/gnu/lib ...
Then you have to recompile psql (not necessarily the
entire code tree).
The GNU readline library can be obtained from the GNU pro
ject's FTP server at ftp://ftp.gnu.org
<URL:ftp://ftp.gnu.org;.
--
Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: psql question
2000-11-20 01:38 psql question Joe Conway <joe@conway-family.com>
2000-11-23 18:31 ` Re: psql question Peter Eisentraut <peter_e@gmx.net>
@ 2000-11-23 19:50 ` Mathijs Brands <mathijs@ilse.nl>
1 sibling, 0 replies; 11+ messages in thread
From: Mathijs Brands @ 2000-11-23 19:50 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: Joe Conway <joe@conway-family.com>; pgsql-sql
On Thu, Nov 23, 2000 at 07:31:36PM +0100, Peter Eisentraut allegedly wrote:
> Joe Conway writes:
>
> > On machines where I've installed PostgreSQL 7.0.2 from RPM, psql allows use
> > of the up arrow key for history and the escape/tab key for command
> > completion, but on my remote web host (webpipe.net) those keys don't work.
> > What do I need to do to get these features working on my remote web host?
> > I've seen reference to .psqlrc in the psql man file, but not much else (no
> > syntax, etc).
>
> If you compiled from source, make sure you had the readline-devel package
> installed.
Also, check the version of the psql shell. 6.x won't do a lot of those neat
little things that make life easier in 7.x.
Mathijs
--
It's not that perl programmers are idiots, it's that the language
rewards idiotic behavior in a way that no other language or tool has
ever done.
Erik Naggum
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: psql question
2000-11-20 01:38 psql question Joe Conway <joe@conway-family.com>
2000-11-23 18:31 ` Re: psql question Peter Eisentraut <peter_e@gmx.net>
@ 2000-11-23 22:01 ` Joe Conway <joe@conway-family.com>
2000-11-23 23:43 ` Re: psql question clayton cottingham <clayton@marketingchallenge.com>
1 sibling, 1 reply; 11+ messages in thread
From: Joe Conway @ 2000-11-23 22:01 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: pgsql-sql
> > On machines where I've installed PostgreSQL 7.0.2 from RPM, psql allows
use
> > of the up arrow key for history and the escape/tab key for command
> > completion, but on my remote web host (webpipe.net) those keys don't
work.
> > What do I need to do to get these features working on my remote web
host?
> > I've seen reference to .psqlrc in the psql man file, but not much else
(no
> > syntax, etc).
>
> If you compiled from source, make sure you had the readline-devel package
> installed.
>
Thanks for your responses, Peter & Mathijs.
Actually, since the issue is on a hosted system, I don't have access to
recompile. I was hoping that maybe there would be something I could
configure from my own (non-root and non-postgres) account.
My question actaully took several days just to make it to the list, so in
the meantime I found my own work-around -- I copied local copies of psql and
libpq up to my account on the web host. Then I put my own account folder to
the front of the PATH and added a LD_LIBRARY_PATH. Now when I run 'psql' it
works just like on my own machine!
Thanks again for the response.
Joe
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: psql question
2000-11-20 01:38 psql question Joe Conway <joe@conway-family.com>
2000-11-23 18:31 ` Re: psql question Peter Eisentraut <peter_e@gmx.net>
2000-11-23 22:01 ` Re: psql question Joe Conway <joe@conway-family.com>
@ 2000-11-23 23:43 ` clayton cottingham <clayton@marketingchallenge.com>
2000-11-24 16:50 ` Re: psql question Adrian Phillips <adrianp@powertech.no>
0 siblings, 1 reply; 11+ messages in thread
From: clayton cottingham @ 2000-11-23 23:43 UTC (permalink / raw)
To: Joe Conway <joe@conway-family.com>; +Cc: Peter Eisentraut <peter_e@gmx.net>; pgsql-sql
Joe Conway wrote:
>
> > > On machines where I've installed PostgreSQL 7.0.2 from RPM, psql allows
> use
> > > of the up arrow key for history and the escape/tab key for command
> > > completion, but on my remote web host (webpipe.net) those keys don't
> work.
> > > What do I need to do to get these features working on my remote web
> host?
> > > I've seen reference to .psqlrc in the psql man file, but not much else
> (no
> > > syntax, etc).
> >
> > If you compiled from source, make sure you had the readline-devel package
> > installed.
> >
>
> Thanks for your responses, Peter & Mathijs.
>
> Actually, since the issue is on a hosted system, I don't have access to
> recompile. I was hoping that maybe there would be something I could
> configure from my own (non-root and non-postgres) account.
>
> My question actaully took several days just to make it to the list, so in
> the meantime I found my own work-around -- I copied local copies of psql and
> libpq up to my account on the web host. Then I put my own account folder to
> the front of the PATH and added a LD_LIBRARY_PATH. Now when I run 'psql' it
> works just like on my own machine!
>
> Thanks again for the response.
>
> Joe
you could use a version of pgsql on your own machine and use the host
switch to connect to an extrenal db
of course your db isp will have to allow your ip to connect, not an easy
thing if your dial up!!
this way you could recompile your own ver of psql
hope that helps
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: psql question
2000-11-20 01:38 psql question Joe Conway <joe@conway-family.com>
2000-11-23 18:31 ` Re: psql question Peter Eisentraut <peter_e@gmx.net>
2000-11-23 22:01 ` Re: psql question Joe Conway <joe@conway-family.com>
2000-11-23 23:43 ` Re: psql question clayton cottingham <clayton@marketingchallenge.com>
@ 2000-11-24 16:50 ` Adrian Phillips <adrianp@powertech.no>
0 siblings, 0 replies; 11+ messages in thread
From: Adrian Phillips @ 2000-11-24 16:50 UTC (permalink / raw)
To: clayton cottingham <clayton@marketingchallenge.com>; +Cc: Joe Conway <joe@conway-family.com>; pgsql-sql
>>>>> "Clayton" == clayton cottingham <clayton@marketingchallenge.com> writes:
Clayton> you could use a version of pgsql on your own machine and
Clayton> use the host switch to connect to an extrenal db
Clayton> of course your db isp will have to allow your ip to
Clayton> connect, not an easy thing if your dial up!!
Or, assuming you have ssh access, use ssh to setup a tunnel between
the two.
Sincerely,
Adrian Phillips
--
Your mouse has moved.
Windows NT must be restarted for the change to take effect.
Reboot now? [OK]
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: psql question
2000-11-20 01:38 psql question Joe Conway <joe@conway-family.com>
@ 2000-11-24 04:06 ` bmccoy@chapelperilous.net
1 sibling, 0 replies; 11+ messages in thread
From: bmccoy@chapelperilous.net @ 2000-11-24 04:06 UTC (permalink / raw)
To: Joe Conway <joe@conway-family.com>; +Cc: pgsql-sql
On Sun, 19 Nov 2000, Joe Conway wrote:
> On machines where I've installed PostgreSQL 7.0.2 from RPM, psql allows use
> of the up arrow key for history and the escape/tab key for command
> completion, but on my remote web host (webpipe.net) those keys don't work.
> What do I need to do to get these features working on my remote web host?
> I've seen reference to .psqlrc in the psql man file, but not much else (no
> syntax, etc).
*sigh* another candidate for the FAQ, I think.
The history keys rely on the GNU readline library. If that library is not
available on your system, the history keys don't work. If you compile
postgres from scratch, make sure you have also installed the
readline-devel rpm as well, so you get the header files, else the
configure phase of the compile will not properly detect and use the
library.
Brett W. McCoy
http://www.chapelperilous.net/~bmccoy/
---------------------------------------------------------------------------
Criticism comes easier than craftsmanship.
-- Zeuxis
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: psql question
@ 2002-03-07 20:30 Josh Berkus <josh@agliodbs.com>
2002-03-08 02:31 ` Re: psql question Christopher Kings-Lynne <chriskl@familyhealth.com.au>
0 siblings, 1 reply; 11+ messages in thread
From: Josh Berkus @ 2002-03-07 20:30 UTC (permalink / raw)
To: Charles Hauser <chauser@acpub.duke.edu>; +Cc: pgsql-sql
CHarles,
> When I created TABLE clone I initially defined a column:
>
> gb_accessions_id INTEGER REFERENCES gb_accessions(gb_accessions_id)
> NULL
>
> I have renamed TABLE gb_accessions to TABLE gb, and its column
> gb_accessions_id to gb_id.
>
> I next renamed the column 'gb_accessions_id' in TABLE clone to
> 'gb_id'.
> In renaming this column in TABLE clone I appear to have lost the
> reference to TABLE gb (see below).
>
> Questions:
>
> 1. During which step in my renaming did this occur?
> 2. How should I have carried this out?
> 3. Can I add back REFERENCES gb(gb_id)?
I generally find that it is much easier, over all, to dump the database
to a pgdump sql-text file, and do my editing there whenever I need to
make significant exits to important database tables. Otherwise, it is
difficult to make sure that all of your constraints, rules, triggers,
and FKs update correctly.
You can add back the REFERENCES constraints. First, check the table
definitions of the child tables to see if they still have orphaned
constraints pointing to gb_accessions. (do this through psql's \d
table_name) If so, drop those constraints.
Unfortunately, I do not know the syntax for ALTER CONSTRAINT ...
FORIEGN KEY .... Post it as a question to the list.
-Josh
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: psql question
2002-03-07 20:30 Re: psql question Josh Berkus <josh@agliodbs.com>
@ 2002-03-08 02:31 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
0 siblings, 0 replies; 11+ messages in thread
From: Christopher Kings-Lynne @ 2002-03-08 02:31 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; Charles Hauser <chauser@acpub.duke.edu>; +Cc: pgsql-sql
> Unfortunately, I do not know the syntax for ALTER CONSTRAINT ...
> FORIEGN KEY .... Post it as a question to the list.
ALTER TABLE foo ADD FOREIGN KEY (field) REFERENCES table(field);
Chris
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: psql: question
@ 2002-05-08 15:23 Josh Berkus <josh@agliodbs.com>
2002-05-09 05:21 ` Re: psql: question Rajesh Kumar Mallah. <mallah@trade-india.com>
0 siblings, 1 reply; 11+ messages in thread
From: Josh Berkus @ 2002-05-08 15:23 UTC (permalink / raw)
To: Charles Hauser <chauser@acpub.duke.edu>; +Cc: pgsql-sql
Chuck,
Please cc: one of the Postgres lists when you query me. If you're
getting Q&A support just from me, I'll have to start charging you!
> Trying to work out details of how to let users page back and forth
> among a multi-page list of results.
>
> Problem: I need to know the total number of tuples the query would
> return in order to decide if there are more to display.
>
> 1) Use CURSOR and FETCH
>
> $res= $con->exec(
> "BEGIN WORK;
> DECLARE gene_result CURSOR FOR
> SELECT blah blah
> ORDER BY blah;
> FETCH $offset IN gene_result
> ");
>
> $result->ntuple: returns only the number set by $offset, not TOTAL
> for query.
>
> 2) use LIMIT OFFSET
>
> same problem,
> $result->ntuple: returns only the number set by LIMIT, OFFSET, not
> TOTAL for query.
>
> So there has to be a way to glean both the TOTAL and the SUBSET
> returned?
>
> Searching the postgresql archives obviously can deal with this
> http://archives.postgresql.org/pgsql-sql/
> 'Displaying documents 11-20 of total 243 found.'
Personally, I don't know any way to do this without running the query
twice.
Least inefficient approach: If your interface allows seperating the
SELECT clause from the rest of the query, then run first:
SELECT COUNT(*)
FROM rest-of-query ...
Check that value. If it's 0, then return a "no records found" message
to the user. Otherwise, run the query broken into LIMIT and OFFSET
blocks.
More inefficient approach: if the way the query is being generated
does not allow you to break off the SELECT clause, then you need to
subselect a count:
SELECT COUNT(*) FROM (
SELECT blah FROM rest_of_query) query_count;
This will also give you a count, buut be slightly slower than the
above.
If anybody knows a way to get a count *without* running the query
twice, I'd be glad to hear it!
-Josh Berkus
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: psql: question
2002-05-08 15:23 Re: psql: question Josh Berkus <josh@agliodbs.com>
@ 2002-05-09 05:21 ` Rajesh Kumar Mallah. <mallah@trade-india.com>
0 siblings, 0 replies; 11+ messages in thread
From: Rajesh Kumar Mallah. @ 2002-05-09 05:21 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; Charles Hauser <chauser@acpub.duke.edu>; +Cc: pgsql-sql
Hi
I run the query twice only the first time the query is requested.
In a web context i can keep passing the total number of
record as a query parameter for displaying the subsequent
pages
for the first request is 50% efficient but the remaining are 100%
but the above works under the assumption that you do not add
record while the end user is browsing the pages which is mostly safe.
regds
mallah.
On Wednesday 08 May 2002 08:53 pm, Josh Berkus wrote:
> Chuck,
>
> Please cc: one of the Postgres lists when you query me. If you're
> getting Q&A support just from me, I'll have to start charging you!
>
> > Trying to work out details of how to let users page back and forth
> > among a multi-page list of results.
> >
> > Problem: I need to know the total number of tuples the query would
> > return in order to decide if there are more to display.
> >
> > 1) Use CURSOR and FETCH
> >
> > $res= $con->exec(
> > "BEGIN WORK;
> > DECLARE gene_result CURSOR FOR
> > SELECT blah blah
> > ORDER BY blah;
> > FETCH $offset IN gene_result
> > ");
> >
> > $result->ntuple: returns only the number set by $offset, not TOTAL
> > for query.
> >
> > 2) use LIMIT OFFSET
> >
> > same problem,
> > $result->ntuple: returns only the number set by LIMIT, OFFSET, not
> > TOTAL for query.
> >
> > So there has to be a way to glean both the TOTAL and the SUBSET
> > returned?
> >
> > Searching the postgresql archives obviously can deal with this
> > http://archives.postgresql.org/pgsql-sql/
> > 'Displaying documents 11-20 of total 243 found.'
>
> Personally, I don't know any way to do this without running the query
> twice.
>
> Least inefficient approach: If your interface allows seperating the
> SELECT clause from the rest of the query, then run first:
>
> SELECT COUNT(*)
> FROM rest-of-query ...
>
> Check that value. If it's 0, then return a "no records found" message
> to the user. Otherwise, run the query broken into LIMIT and OFFSET
> blocks.
>
> More inefficient approach: if the way the query is being generated
> does not allow you to break off the SELECT clause, then you need to
> subselect a count:
>
> SELECT COUNT(*) FROM (
> SELECT blah FROM rest_of_query) query_count;
>
> This will also give you a count, buut be slightly slower than the
> above.
>
> If anybody knows a way to get a count *without* running the query
> twice, I'd be glad to hear it!
>
> -Josh Berkus
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
--
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)
Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2002-05-09 05:21 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-11-20 01:38 psql question Joe Conway <joe@conway-family.com>
2000-11-23 18:31 ` Peter Eisentraut <peter_e@gmx.net>
2000-11-23 19:50 ` Mathijs Brands <mathijs@ilse.nl>
2000-11-23 22:01 ` Joe Conway <joe@conway-family.com>
2000-11-23 23:43 ` clayton cottingham <clayton@marketingchallenge.com>
2000-11-24 16:50 ` Adrian Phillips <adrianp@powertech.no>
2000-11-24 04:06 ` bmccoy@chapelperilous.net
2002-03-07 20:30 Re: psql question Josh Berkus <josh@agliodbs.com>
2002-03-08 02:31 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-05-08 15:23 Re: psql: question Josh Berkus <josh@agliodbs.com>
2002-05-09 05:21 ` Re: psql: question Rajesh Kumar Mallah. <mallah@trade-india.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox