Re: [SQL] reply to setting
Joe Conway <[EMAIL PROTECTED]> writes: > This is very true. In fact, I get mildly annoyed when people *don't* include > the direct reply to me, because I very actively filter/redirect my mail. > Replies directly to me are pretty much guaranteed to be seen quickly, but the > ones that go to the list might get lost among the hundreds of posts that go > into my "postgres" inbox every day. I think many other people do something > similar. Just as a side comment, one trick I found very helpful in my mail filters is to treat any message with one of my message-ids in the references as a personal message as far as mail notifications. This way I get notifications for any message on a thread following a post of my own. This is easy in Gnus since the message id has the sending hostname and also the first few characters has a base64 encoded copy of the unix userid. You would have to figure out how to recognize message-ids from your MUA. -- greg ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] pg_dump/pg_restore question
Freinds I am new to this list, and this is my first message. I hope this is the correct forum, and the question not too stupid/simple. I have a database on a debian stable system... dpkg -l postgresql [snip] ii postgresql 7.2.1-2woody5 Object-relational SQL database, descended fr When I dump a database (as from the man page for pg_restore) pg_dump mydb > mydb.out OK. Get an SQL dump in mydb.out Create a new database creatdb myotherdb Load the dump psql -d myotherdb -f mydb.out psql:mydb.out:4: \connect: FATAL 1: IDENT authentication failed for user "postgres" I have tried adding the line host all 127.0.0.1 255.255.255.255trust to pg_hba.conf but it makes no difference. The only thing I can do is edit the dump file into the part that wants to connect as postgres and the part that wants to connect as worik and run them under their respective logins. How can I set it up so I do not have to do that? What documentation should I be reading? Worik ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] sleep function
John DeSoi <[EMAIL PROTECTED]> writes: > On Aug 10, 2004, at 10:57 AM, Bruce Momjian wrote: > > > I can't think of one, no. I think you will have to use one of the > > server-side languages and call a sleep in there. > > This is no good in the real world since it pounds the CPU, but it worked well > enough for my testing purposes. You went the long way around. I think what he meant was something as simple as: bash-2.05b$ /usr/lib/postgresql/bin/createlang -U postgres plperlu test bash-2.05b$ psql -d test -U postgres Welcome to psql 7.4.2, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit test=# create or replace function sleep(integer) returns integer as 'return sleep(shift)' language plperlu; CREATE FUNCTION test=# \timing Timing is on. test=# select sleep(10) ; sleep --- 10 (1 row) Time: 10002.493 ms -- greg ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] only last records in subgroups
Dino Vliet <[EMAIL PROTECTED]> writes: > x,0 and y,4 but how do I manage this in sql? I was > hoping for a keyword LAST or so, where I can specify > that when I've ordered my results with order by, I > could only get the last of the subgroups (the first > one is easy because I could use limit 1) There's no concept of "first" and "last" in SQL outside of the ORDER BY clause of your query. And you can easily reverse the order of the ORDER BY sort by putting "DESC" after the columns you're sorting on. But I don't understand how you intend to use "LIMIT 1" to solve your problem. As you describe the problem you want the last (or first) record of *each* *group*. Solving that using LIMIT would require a complex query with a subquery in the column list which would be quite a pain. As the other poster suggested, if you're just looking to fetch a single column you can just use min() or max() to solve this. If you're looking to fetch more than one column Postgres provides a non-standard SQL extension for dealing with this situation, "DISTINCT ON". SELECT DISTINCT ON (id) id,day,other,columns FROM tab ORDER BY id,day That gets the lowest value of "day". Using "ORDER BY id, day DESC" to get the greatest value of "day". -- greg ---(end of broadcast)--- TIP 3: 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: [SQL] pg_dump/pg_restore question
Hi Worik, I can't address the specifics of your problem but I'll give you an example of I do it on a 7.4 db. I dunno if all options are available on your version. To get the backup: pg_dump --format=c -v DBNAME > FILENAME.dump To restore you have to create a new DB (drop the old one first if neccessary): createdb -T template0 [-E ENCODING] DBNAME Note that this is template 0 and not template 1. (also, don't forget to set your database encoding if the default isn't what you want). Then create a contents list: pg_restore --list DBNAME.dump > DBNAME.list You can edit the list file to rearrange the restore order or omit items. Finally, do the restore: pg_restore --use-list=DBNAME.list --dbname=DBNAME -v DBNAME.dump This is all documented fairly well in the online documentation, you just need to spend a bit of time going through it. As I recall, some information is in the admin section and some is in the documentation of the client programs (pg_dump and pg_restore are client programs iirc). Also, consider upgrading to 7.4 to ensure you get the best support. Good luck, Iain - Original Message - From: "Worik" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, August 23, 2004 11:59 AM Subject: [SQL] pg_dump/pg_restore question > Freinds > > I am new to this list, and this is my first message. > > I hope this is the correct forum, and the question not too stupid/simple. > > I have a database on a debian stable system... > > dpkg -l postgresql > [snip] > ii postgresql 7.2.1-2woody5 Object-relational SQL database, > descended fr > > When I dump a database (as from the man page for pg_restore) > pg_dump mydb > mydb.out > > OK. Get an SQL dump in mydb.out > > Create a new database > > creatdb myotherdb > > Load the dump > > psql -d myotherdb -f mydb.out > psql:mydb.out:4: \connect: FATAL 1: IDENT authentication failed for > user "postgres" > > I have tried adding the line > host all 127.0.0.1 255.255.255.255trust > > to pg_hba.conf but it makes no difference. > > The only thing I can do is edit the dump file into the part that wants > to connect as postgres and the part that wants to connect as worik and > run them under their respective logins. > > How can I set it up so I do not have to do that? > > What documentation should I be reading? > > Worik > > ---(end of broadcast)--- > TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
