Re: [SQL] Problems with PG_DUMP and restore
From: "Josh Berkus" <[EMAIL PROTECTED]> > Richard, > > Try having a look at the order the tables get inserted esp. with > > regard to > > any foreign keys etc. - I'm not sure pgdump is that clever about such > > things. > > Thanks. I did try that; however: > 1. Even trying re-loading the tables twice did not work, as it should > have with missing foriegn keys. > 2. I did not see any Unmatched Reference errors in the log, as I > typically do with missing keys. > > Further, I *was* able to re-load the database from a unitary pg_dump > file ... one that includes both DDL and data. It's just when I split > the pg_dump into two files -- one for DDL, one for data -- that it > fails. > > BTW, I'm using 7.1RC2 Was there not some fix to pgdump in 7.1.1? Yep - pg_dump fixes (Philip) pg_dump can dump 7.0 databases (Philip) Might be worth a quick upgrade & see what happens. Failing that, it isn't something to do with permissions and pgdump connecting as various users? - Richard Huxton ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] SQL - histogram
From: "Txugo" <[EMAIL PROTECTED]> > I have a table where one record represent a person, including his height. > I'd like to know how many person have more than 150 cm, more than 160 cm > and so on. > How can I do that using SQL? > > Example: > people > 150 - 1000 >> 160 - 850 >> 170 - 500 >> 180 - 200 >> 190 - 30 > thanks in advance richardh=> select * from people; id | height + 1 |150 2 |155 3 |160 4 |165 (4 rows) richardh=> select * from heights; cm - 150 160 (2 rows) richardh=> select cm,count(id) from people, heights where height>=cm group by cm; cm | count -+--- 150 | 4 160 | 2 (2 rows) HTH - Richard Huxton ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Group by date_part
From: "Roberto Mello" <[EMAIL PROTECTED]>
> On Tue, Jul 10, 2001 at 08:04:55PM +0100, Graham Vickrage wrote:
> >
> > The statement I have only selects the count if there is at least 1 order
for
> > a particular day, which make sense.
> >
> > I however need a count of 0 for days that don't have any. Can anyone
help?
> >
> > SQL:
> >
> > SELECT date_part('day', date), count(*)
> > FROM client_order WHERE (date >= '01/05/01' AND date < '01/06/01') AND
> > status = 'Processing'
> > GROUP BY date_part('day', date);
>
> Didn't look very hard, but can't you just add a:
>
> CASE WHEN count(*) > 0 THEN count(*) ELSE 0 END AS count
Don't think so - this one always catches me out, and I have to figure out
how to solve it each time. The problem is that if there aren't any records
with (eg) date = 2001-05-16 then there is simply nothing to return.
I've only ever come up with two solutions:
1. Create a temporary table with all the dates required and a total field
initialized to zero. Update the totals as desired then just read from the
table.
2. Create a temporary table with the dates and join against it. Then you can
use a CASE construct as above to get a zero.
Which one I select depends on usage patterns. If the dates don't change much
(e.g. weekending dates) then I'll use #2, otherwise #1.
I'd be very interested in any clever way of doing this without a temporary
table (or equivalent - if functions could return sets of values you could
use that, but it's basically the same thing).
- Richard Huxton
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] "Display of specified number of records."
From: "rajesh" <[EMAIL PROTECTED]> > Hi, > I have got following simple SQL. > Select TestID from test where testname = ' ' order by testdate. > > Suppose for argument sake there are 100 records and testID's are 1 > to 100. > Is it possible to modify this SQL so that it will display records > from 10 to 50 and not any other records. Robby Slaughter has given one solution in another reply. If that's not quite what you're after, and you want the 10th to 50th results from the above query you can do: ... order by testdate limit 40 offset 10; This calculates the results and then throws away the first nine and anything after the 50th. - Richard Huxton ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] can we write to a flat file from Postgresql procedure
R Vijayanath wrote: > Hi, > > I found your name in the Postgresql web site. > > It would be great if you can tell me if I can write a > procedure that can write the output to the OS(Linux > OS) file. > > Can you assist me on this if there is a way to do it. > > We are using Postgresql 7.1 running in Linux Redhat > 7.1. In version 7.1 is a procedural language PL/TclU. It's an unrestricted (and thus untrusted) version of PL/Tcl. Meaning, since it's possible to do any sort of OS level access from within the backend under the postgres UNIX user-ID, only Postgres superusers can create functions in that language. That's the only choice other than C you have. There are no plans yet to modify PL/pgSQL in that direction. > > Thanks in Advance. You're welcome. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] cumulative sum in aggregate query.
David Stanaway <[EMAIL PROTECTED]> writes: > I have a query that gives me x/y data for a graph, and at the moment, > the y data is relative. > EG: > x | y > 1.2 | +1 > 1.9 | 0 > 3.4 | +4 > 5.2 | -2 > 6.7 | -1 > 9.3 | 0 > 11.3 | -1 > Now, I want to convert this result into a result like this: > x | y > 1.2 | 1 > 1.9 | 1 > 3.4 | 5 > 5.2 | 3 > 6.7 | 2 > 9.3 | 0 > 11.3 | 1 > Does anyone have any suggestions as to how to do this? AFAIK the only way to do that in SQL is like so: SELECT x, (SELECT sum(y) FROM tab innertab WHERE innertab.x <= outertab.x) FROM tab outertab ORDER BY x; This will, of course, be horrendously inefficient for large numbers of rows, but given that SQL doesn't believe in ordered data inside a computation, I can't see any other way to do it in pure SQL. If you've got lots of data, you should consider just doing "SELECT x,y FROM tab ORDER BY x" and then forming the running sum on the application side. BTW, I didn't come up with that on the spur of the moment --- I got it from Joe Celko's "SQL For Smarties". Highly recommended book. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] can we write to a flat file from Postgresql procedure
R Vijayanath writes: > It would be great if you can tell me if I can write a > procedure that can write the output to the OS(Linux > OS) file. You could try out PL/sh for that. http://www.postgresql.org/~petere/plsh.html YMMV -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] can we write to a flat file from Postgresql procedure
On Wed, 11 Jul 2001, Peter Eisentraut wrote: > R Vijayanath writes: > > > It would be great if you can tell me if I can write a > > procedure that can write the output to the OS(Linux > > OS) file. > > You could try out PL/sh for that. > > http://www.postgresql.org/~petere/plsh.html Well, as long as we all keep plugging our favorite languages, in 7.2, you can use pl/perlu ;) -alex ---(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
[SQL] RE: can we write to a flat file from Postgresql procedure
I don't think there is any way to do this using any of the generally available tools. You could do a workaround using pl/pgsql, in the following way: * insert your data into a temp table * output the data to a file using COPY pl/pgsql allows INSERT statements, but does not allow CREATE/DROP TABLE or COPY statements. Those can be constructed using EXECUTE, which takes a string (which can be constructed using parameters) and passes it on to be executed as an SQL statement. For more information, check out the PostgreSQL documentation, especially on pl/pgsql (under "Procedural Languages") and COPY (under "SQL Commands"). > -Original Message- > From: R Vijayanath [SMTP:[EMAIL PROTECTED]] > Sent: Thursday, July 05, 2001 10:34 AM > To: [EMAIL PROTECTED] > Subject: can we write to a flat file from Postgresql procedure > > Hi, > > I found your name in the Postgresql web site. > > It would be great if you can tell me if I can write a > procedure that can write the output to the OS(Linux > OS) file. > > Can you assist me on this if there is a way to do it. > > We are using Postgresql 7.1 running in Linux Redhat > 7.1. > > Thanks in Advance. > > Vijay > > > __ > Do You Yahoo!? > Get personalized email addresses from Yahoo! Mail > http://personal.mail.yahoo.com/ > > ---(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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] RE: can we write to a flat file from Postgresql procedure
I haven't seen anything on this list to say that pl/perlu is being worked on. Is it? > -Original Message- > From: Alex Pilosov [SMTP:[EMAIL PROTECTED]] > Sent: Wednesday, July 11, 2001 10:03 AM > To: Peter Eisentraut > Cc: R Vijayanath; [EMAIL PROTECTED] > Subject: Re: can we write to a flat file from Postgresql procedure > > On Wed, 11 Jul 2001, Peter Eisentraut wrote: > > > R Vijayanath writes: > > > > > It would be great if you can tell me if I can write a > > > procedure that can write the output to the OS(Linux > > > OS) file. > > > > You could try out PL/sh for that. > > > > http://www.postgresql.org/~petere/plsh.html > Well, as long as we all keep plugging our favorite languages, in 7.2, you > can use pl/perlu ;) > -alex > > > ---(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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] File system level backup
Hi! I have an installation of PG7.0.2 in a disk, for some reasons I didn't use that disk anymore but right now I want to use the dbs that I have there, the point is that I've mount that disk in other file system, so I copied the old /usr/local/pgsql/data to the new /usr/local/pgsql/data and tried to access that DB using also PG7.0.2 but it didn't work. I followed the instructions at http://www.postgresql.org/idocs/index.php?backup-file.html Here's the log of the DB running the new copied /data: DEBUG: Data Base System is starting up at Wed Jul 11 05:24:07 2001 DEBUG: Data Base System was shut down at Tue Jul 10 18:20:05 2001 DEBUG: Data Base System is in production state at Wed Jul 11 05:24:07 2001 FATAL 1: Database "open" does not exist in the system catalog. I got the FATAL 1 when I do a psql open, that DB really exists in data/base by the way, the first PG installation was on RH6.2 the actual PG is on RH7.1, and both are PG7.0.2 installed from source (not rpms) What is the right way to do a dump of the DBs in this way?, I don't want to use pg_dump. Thank you, Rocael. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] RE: can we write to a flat file from Postgresql procedure
Its done and committed to CVS, will be in 7.2 release. Also done is DBD::PgSPI, interface to access postgres from within your stored procedure. (www.formenos.org/PgSPI) -alex On Wed, 11 Jul 2001, Jeff Eckermann wrote: > I haven't seen anything on this list to say that pl/perlu is being worked > on. Is it? > > > -Original Message- > > From: Alex Pilosov [SMTP:[EMAIL PROTECTED]] > > Sent: Wednesday, July 11, 2001 10:03 AM > > To: Peter Eisentraut > > Cc: R Vijayanath; [EMAIL PROTECTED] > > Subject:Re: can we write to a flat file from Postgresql procedure > > > > On Wed, 11 Jul 2001, Peter Eisentraut wrote: > > > > > R Vijayanath writes: > > > > > > > It would be great if you can tell me if I can write a > > > > procedure that can write the output to the OS(Linux > > > > OS) file. > > > > > > You could try out PL/sh for that. > > > > > > http://www.postgresql.org/~petere/plsh.html > > Well, as long as we all keep plugging our favorite languages, in 7.2, you > > can use pl/perlu ;) > > -alex > > > > > > ---(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 > > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Re: [BUGS] ERROR: deferredTriggerGetPreviousEvent: event for tuple(0,9) not found
On 11 Jul 2001 13:56:58 -0400, Tom Lane wrote: > > but if I want to clear the valuntil completely, how should I issue the > > ALTER USER statement? > > Offhand I don't believe ALTER USER can do that. Feel free to submit > a patch ;-). This seems to have a similar effect: ALTER USER test VALID UNTIL 'infinity'; I could stick to this for now. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Re: [BUGS] ERROR: deferredTriggerGetPreviousEvent: event for tuple(0,9) not found
> So that raises this from an unimplemented feature to a real bug, or at > least higher priority in my eyes. You can't do a CREATE/ALTER USER > followed by an UPDATE on pg_shadow in a single transaction: I'm glad I could be of some help. I don't know if the following means anything to you (or the pgsql-odbc folks), but in the past (I don't recall if that was postgres 7.0.3, 7.1.1 or 7.1.2) I used to get the following error message while issuing a "BEGIN, ALTER USER ..., COMMIT" block, coming in through an ODBC connection using VC++ 6 with the 7.01.00.05 driver: ERROR: ALTER USER: may not be called in a transaction block Doing a BEGIN, ALTER USER.., COMMIT (not through ODBC, through the psql client) now on 7.1.2 *seems* to work fine though. Should I take it that ALTER USER may indeed be used in a transaction block? I don't know if this was actually fixed or bugfixes to this might have something to do with the system trigger on pg_shadow, but I just thought I'll mention it in case it rings any more bells. I'm sure you know best. > The workaround I'd recommend to you for now is to use ALTER USER, not > UPDATE, to perform the second step in your function. So, how would I go about using the ALTER USER statement to clear out the valuntil value from the pg_shadow table? If I want to set an expiration date for the user, I can use a statement such as: ALTER USER test VALID UNTIL '2001-07-11 10:23:23-07' but if I want to clear the valuntil completely, how should I issue the ALTER USER statement? I have tried (with no success) the following so far: ALTER USER test VALID UNTIL NULL; ALTER USER test VALID UNTIL 'NULL'; ALTER USER test VALID UNTIL ''; Me failing to use the "ALTER USER" command successfully caused be to use the "UPDATE pg_shadow" instead. Thanks, -Kristis ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Re: [BUGS] ERROR: deferredTriggerGetPreviousEvent: event for tuple (0,9) not found
Kristis Makris <[EMAIL PROTECTED]> writes: > but if I want to clear the valuntil completely, how should I issue the > ALTER USER statement? Offhand I don't believe ALTER USER can do that. Feel free to submit a patch ;-). regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] trigger on DELETE
Hello everyone, I'm trying to define a trigger that copies the row to be deleted into another table (which is the inventory_audit table) before it does the delete from the original table (which is the inventory table). CREATE FUNCTION inv_audit_mod () RETURNS OPAQUE AS ' BEGIN NEW.user_aud := current_user; NEW.mod_time := ''NOW''; INSERT INTO inventory_audit SELECT * FROM inventory WHERE id=NEW.id; RETURN NEW; END; ' LANGUAGE 'plpgsql'; CREATE TRIGGER inv_audit_mod BEFORE DELETE ON inventory FOR EACH ROW EXECUTE PROCEDURE inv_audit_mod(); Ok, the function works only with a trigger that is defined as ON INSERT OR UPDATE. If I try to define a trigger for ON DELETE and then delete a row from the table, there is nothing in the 'NEW' variable to return. I get an error message. If I define the function to return NULL, 0, or nothing, then it comes up with a type mis-match error. Is there anyone who can help? Thanks. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Re: [GENERAL] trigger on DELETE
On Wed, 11 Jul 2001, Phuong Ma wrote: > I'm trying to define a trigger that copies the row to be deleted into > another table (which is the inventory_audit table) before it does the > delete from the original table (which is the inventory table). > > CREATE FUNCTION inv_audit_mod () RETURNS OPAQUE AS ' > BEGIN > > NEW.user_aud := current_user; > NEW.mod_time := ''NOW''; > > INSERT INTO inventory_audit > SELECT * FROM inventory WHERE id=NEW.id; > > RETURN NEW; > END; > ' LANGUAGE 'plpgsql'; > > CREATE TRIGGER inv_audit_mod BEFORE > DELETE ON inventory > FOR EACH ROW EXECUTE PROCEDURE inv_audit_mod(); > > Ok, the function works only with a trigger that is defined as ON INSERT > OR UPDATE. If I try to define a trigger for ON DELETE and then delete a > row from the table, there is nothing in the 'NEW' variable to return. I > get an error message. If I define the function to return NULL, 0, or > nothing, then it comes up with a type mis-match error. Is there anyone > who can help? Thanks. I believe you want to use OLD rather than NEW for a delete trigger. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] trigger on DELETE
> Ok, the function works only with a trigger that is defined as ON INSERT > OR UPDATE. If I try to define a trigger for ON DELETE and then delete a > row from the table, there is nothing in the 'NEW' variable to return. I > get an error message. If I define the function to return NULL, 0, or > nothing, then it comes up with a type mis-match error. Is there anyone > who can help? Thanks. Try using the OLD variable instead. ---(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
