Re: [SQL] a script that queries database periodically
On Mon, 27 Nov 2000, Bernie Huang wrote: > I was thinking of writing up a PHP script and put into crontab, which is > somehow easier than a shell script, but PHP is an apache module, so I > cannot execute PHP under crontab (it has to be executed in a browser > right?). I guess a shell script is necessary. So, is it possible to > call 'psql' and returning its query result and I can use sendmail to > email the result? Any other idea? Is there any reason to not use Perl & DBI or the Pg.pm module? Brett W. McCoy http://www.chapelperilous.net/~bmccoy/ --- Exhilaration is that feeling you get just after a great idea hits you, and just before you realize what is wrong with it.
Re: [SQL] postgres
On 13 Dec 2000, Marc Daoust wrote: > I in the search for a DB that would work with our product and have been told > to have a look at postgres. Would you be able to foward me any information on > your product and or point me to where I might be able to find some. You should start with www.postgresql.org -- Brett http://www.chapelperilous.net/~bmccoy/ --- So, is the glass half empty, half full, or just twice as large as it needs to be?
Re: [SQL] readline ??
On Fri, 15 Dec 2000, vs wrote: > Hope my message doesn't bother you. > I want to use readline with pgsql7.02 on mandrake 7.2. > LM7.2 installed both packages, readline/devel & postgres. > How to make psql know about readline? If you are using a binary installation of Postgres (you installed via RPM), it may not have the readline support compiled in, so you might want to grab the source and rebuild -- it'll pick up the readline stuff during the ./configure phase -- Brett http://www.chapelperilous.net/~bmccoy/ --- Democracy means simply the bludgeoning of the people by the people for the people. -- Oscar Wilde
Re: [SQL] replace??
On Tue, 19 Dec 2000, Bruno Boettcher wrote: > actually i look up if the corresponding entry exists (comparing > user-name and field-name) if yes i update, if no i insert > > this isn't very appealing, but i couldn't find another way yet to make > this shorter... > > would be nice if something like the following existed: > > replace settings set auser='toto',field='lang',data='fr' where > auser='toto' and field='lang'; > > update settings set auser='toto',field='lang',data='fr' where > auser='toto' and field='lang' REPLACE; What is the difference between replacing data and updating data in this sense? What you might want to do with your array (which in PHP can also function as a hash, with string indices) is create a wrapper class that kind of emulates a hash tied to the DB like one might do in Perl, so that whenever you 'store' a value in the array (via a method), it will automagically update or insert into the underlying database. PHP doesn't do ties explicitly, but you can emulate this behaviour with a PHP class. -- Brett http://www.chapelperilous.net/~bmccoy/ --- The price of greatness is responsibility.
Re: [SQL] Running a file
On Sun, 24 Dec 2000, Thomas SMETS wrote: > I'm runnin postgres 7.?? (Last RPM package available from the site). > > I want to create a few DB creation scripts so I can "publish" that > afterwards. > On Oracle there's a such possibility but I haven't seen anything > comaprable in the docs ... > I however think I'm missing one of the very first possibility of > "pgsql". > Could someone point me were I could find some more infos on that matter > ? Are you talking about writing external scripts and importing them into Postgres in a manner similar to the @ function in SQL*Plus? In psql, you can use \i to import scripts to do that. -- Brett http://www.chapelperilous.net/~bmccoy/ --- Reading is to the mind what exercise is to the body.
Re: [SQL] References to SERIAL
On Sat, 30 Dec 2000, Thomas SMETS wrote: > If i create a "internal pk" buy defining on a table a field SERIAL. > How do I reference this field in the other table to set the field > possible value ? > > > create table book ( > /* This is an internal primary key for the book description */ > book_pk serial, > // End of Book def > ); > > create table books_authors ( > ??? // I want to reference the book pk & the author pk to be able to > make the X-ref ? > ); You mean as a foreign key? You would do something like create table books_authors ( book integer references book(book_pk) on delete no action, author integer references author(author_pk) on delete no action, ... ); This forces integrity between the tables so the only allowable values in the books_authors table are those values in the referenced fields (foreign keys). You will probably want to look up the documentation on contraints and foreign keys (I believe they are under the CREATE TABLE documentation). -- Brett http://www.chapelperilous.net/~bmccoy/ --- Did you hear that there's a group of South American Indians that worship the number zero? Is nothing sacred?
Re: [SQL] AUTOINCREMENT--help
On Tue, 2 Jan 2001, Macky wrote: > Is there a function in SQL that does autoincrementing... http://www.postgresql.org/docs/faq-english.html#4.16.1 and http://www.postgresql.org/users-lounge/docs/7.0/postgres/sql-createsequence.htm will tell you how to do this. -- Brett http://www.chapelperilous.net/~bmccoy/ --- A thing is not necessarily true because a man dies for it. -- Oscar Wilde, "The Portrait of Mr. W.H."
Re: [SQL] PostgreSQL HOWTO
On Thu, 18 Jan 2001, Kaare Rasmussen wrote: > > I do not see how it puts the Postgres community in a bad light, although I > > do see how the author is a moron. > > People think that it's an official PostgreSQL document. It turned up in a > discussion (PostgreSQL vs. MySQL round 1000) as "the PostgreSQL docs". Even earlier on, a lot of people portested the document because it used to misleadingly be called the "Database HOWTO", even though it only specifically talked about PostgreSQL. However, that was the document that first led me to PostgreSQL, about 2 years before I even knew MySQL existed... -- Brett http://www.chapelperilous.net/~bmccoy/ --- Q: How many IBM CPU's does it take to do a logical right shift? A: 33. 1 to hold the bits and 32 to push the register.
Re: [SQL] PostgreSQL HOWTO
On Thu, 18 Jan 2001, Michael Richards wrote: > As I understand Zend is a compiler/interpreter that uses a optimised > bytecode to run a little faster than the normal apache/php. It shares > few of the features of perl, even fewer of Java. C++? Last time I > checked, PHP couldn't do OOP. Next thing we know it will be as > efficient as assembler and as object oriented as SmallTalk. Zend is the new engine that PHP4 is built on. It's supposed to be more optimised for heavy web stuff, kinda like mod_perl or a Java servlet engine. I've never used it, and haven't touched PHP in a year, so I can't vouch for what it really does. PHP sorta does objects, but don't expect Java or C++ (or even Perl) level of OO support. -- Brett http://www.chapelperilous.net/~bmccoy/ --- A kind of Batman of contemporary letters. -- Philip Larkin on Anthony Burgess
Re: [SQL] PostgreSQL HOWTO
On Thu, 18 Jan 2001, Poet/Joshua Drake wrote: > >it seems that the author never used any other think then PHP ... > > I am afraid I would disagree. I have used all of the languages he metions > and for the Web, PHP is the best. I think it all depends on what you are building. PHP is good for small projects, but I would go with something more scalable for large systems, like EJB/servlets or Mason -- something that has more content management & templating features. -- Brett http://www.chapelperilous.net/~bmccoy/ --- Did you know the University of Iowa closed down after someone stole the book?
Re: [SQL] abstract data types?
On Sat, 20 Jan 2001, Tom Lane wrote: > None, I fear. The stuff you are fooling with is leftover from the old > PostQuel language. Most of it is suffering from bit rot, because the > developers' focus has been on SQL92 compliance for the last six or seven > years. I hadn't realized that SQL99 had caught up to PostQuel in this > area ;-). Sounds like we will have to dust off some of that stuff and > get it working again. No promises about timeframe, unless someone > steps up to the plate to do the work... What goes around comes around. :-) -- Brett http://www.chapelperilous.net/~bmccoy/ --- mixed emotions: Watching a bus-load of lawyers plunge off a cliff. With five empty seats.
Re: [SQL] Re: Problem with Dates
On Thu, 25 Jan 2001, Glen and Rosanne Eustace wrote: > pressie# select '31/12/2000'::date + '1 year'::timespan; > ?column? > - > 01/01/2002 00:00:00.00 NZDT > (1 row) > > pressie=# > > Well I do :-( > > I vaguely remember someone else having the same problem and it was > something to do with daylight saving. I don't recall the solution > though, if there was one. It might also have something to do with your timezone. I did the exact same query on my server and got the correct result: cp=> select '31/12/2000'::date + '1 year'::timespan; ?column? 2001-12-31 00:00:00-05 (1 row) cp=> What happens if instead you add the days? -- Brett http://www.chapelperilous.net/~bmccoy/ --- We are what we are.
Re: [SQL] "'" in SQL INSERT statement
On Thu, 25 Jan 2001, Markus Wagner wrote: > I have some data that I wish to transfer into a database using perl/DBI. > Some of the data are strings containing the apostrophe "'" which I use > as string delimiter. > > How can I put these into my database using the INSERT statement? You will need to escape them with the \ character. So "Bill's Garage" will become "Bill\'s Garage". -- Brett http://www.chapelperilous.net/~bmccoy/ --- Romeo wasn't bilked in a day. -- Walt Kelly, "Ten Ever-Lovin' Blue-Eyed Years With Pogo"
Re: [SQL] Re: Problem with Dates
On Sat, 27 Jan 2001, Glen and Rosanne Eustace wrote: > If some one else is running 7.1 already and can just change their > timezone to New Zealand DT and report the results it would be great. Here ya are: cp=> set time zone 'NZ'; SET VARIABLE cp=> select '12/31/2000'::date + '1 year'::interval; ?column? 2001-12-31 00:00:00+13 (1 row) cp=> -- Brett http://www.chapelperilous.net/~bmccoy/ --- It's a good thing we don't get all the government we pay for.
Re: [SQL] Re: Problem with Dates
On Sat, 27 Jan 2001, Glen and Rosanne Eustace wrote: > Is 7.0.3 to 7.1B? simply a reinstall or do I need to unload/reload the > database. Yep, you need to do whole shebang of dumping and reloading. -- Brett http://www.chapelperilous.net/~bmccoy/ --- It's a good thing we don't get all the government we pay for.
Re: [SQL] Wild Cards
On Thu, 25 Jan 2001, wrote: > I am not able to get Wildcards in PostgreSQL, I know its * (asterisk), but > its not working. can someone show me a example or something? Wildcards where? You can use * to mean all the fields in a table in a SELECT statement, but if you are using LIKE in a WHERE clause, the wildcards are % to mean any group of characters and _ to mean any single character. -- Brett http://www.chapelperilous.net/~bmccoy/ --- It's a good thing we don't get all the government we pay for.
Re: [SQL] Search
On Mon, 5 Feb 2001, Sebastian --[ www.flashhilfe.de ]-- wrote: > I have make a search machine whit: > > LIKE '%$suchbegriffe[$i]%' > > but when I search Test - the search machine shows only entries > whit Test. But not test or tESt. LIKE is case-sensitive. You should convert your column to uppercase: WHERE UPPER(field) LIKE ... or use case-insensitive regular expression: WHERE field ~* '' -- Brett http://www.chapelperilous.net/~bmccoy/ --- The Angels want to wear my red shoes. -- E. Costello
Re: [GENERAL] Re: [SQL] Re: SQL Join - MySQL/PostgreSQL difference?
On Wed, 7 Feb 2001, Brice Ruth wrote: > Is there a simple (unix) command I can run on text files to convert > cr/lf to lf? The way I did it seemed pretty ass-backward to me (not to > mention time consuming). perl -pi -e 's/\cM\\g' will do the trick, assuming you have Perl instaleld on your system. -- Brett http://www.chapelperilous.net/~bmccoy/ --- Money will say more in one moment than the most eloquent lover can in years.
Re: [SQL] String Concatnation
On Sat, 10 Feb 2001, Najm Hashmi wrote: > How can I concatnate two varialbles, seperated by a |, that are type text > together? > v, v1 text; > some work > then > res:= v ||''|''|| v1; What error is it giving? Do you need to be using two single quotes in the statement? -- Brett http://www.chapelperilous.net/~bmccoy/ --- There is a certain impertinence in allowing oneself to be burned for an opinion. -- Anatole France
Re: [SQL] Quick question MySQL --> PgSQL
On Tue, 6 Mar 2001, Josh Berkus wrote: > Just a quick question ... I need to do a regular transfer (daily + on > demand) of data from a MySQL database to a PostgreSQL database and back > again. Can anybody steer me towards a good script for this, or do I > have to write my own in PHP? Don't think there is an actual migration script (I could be wrong, though), but a program using Perl DBI or JDBC would make the data access a bit easier -- if you wrote it generically enough, you could make the data transfer go both ways with just a command-line switch. -- Brett http://www.chapelperilous.net/~bmccoy/ Give all orders verbally. Never write anything down that might go into a "Pearl Harbor File". ---(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] Help
On Sat, 24 Mar 2001, Mohamed ebrahim wrote: > I am a user of postgresql. I want to know that it > is possible to call a jsp file in postgre > command.Please help me to know how to call a jsp file. What do you mean 'call a JSP file'? A JSP file is parsed and compiled by something like Jasper or JServ and and is served through a web server like Tomcat or Tomcat with Apache. Now, you CAN use jdbc in JSP files and access PostgreSQL databases that way. For that, you need to take a look at the jdbc documentation. -- Brett http://www.chapelperilous.net/btfwk/ There is no fear in love; but perfect love casteth out fear. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] New book on Postgres
On Tue, 6 Jun 2000 [EMAIL PROTECTED] wrote: > Hello! wers regarding a book proposal on > > Regarding the book proposal on Postgres: I can tell you that the world > needs a Postgres book! Bruce Momjian has almost completed his, and you can see it on the main PostgreSQL website. Brett W. McCoy http://www.chapelperilous.net --- Screw up your courage! You've screwed up everything else.
Re: [SQL] confused by select.
On Thu, 6 Jul 2000, John wrote: > I would like to get the id's where the customer has purchased an item of a > specific type. > > Problem A: most people order more than one item at a time. > So the 'items' field is a colon delimitted text field containing the >skus of the purchased items. > I don't understand why you are doing it this way? Why not create a history table with individual skus that are each part of an order? create table history (id int2, order int2, sku char(4)); You would, of course, put some constraints to make sure that skus in the history table actually exist in the inventory table (i.e., foreign key), and have the history id as a serial type to make the primary key. Then you can have the same order number reference multiple inventory items. Then you can do easier joins, search for unique orders with a count of items in each order, and so forth, all in SQL. Brett W. McCoy http://www.chapelperilous.net --- Virtue does not always demand a heavy sacrifice -- only the willingness to make it when necessary. -- Frederick Dunn
Re: [SQL] confused by select.
On Thu, 6 Jul 2000, Jan Wieck wrote: > IMHO the correct suggestion. Just want to underline it. > > A list of purchases is usually a subset of another relation. > Remember, RDBMS means RELATIONAL Database Management System! > So if you setup your tables with a relational angle of view, > the system will do well. I was going to say that, but assumed it would be obvious. :-) Brett W. McCoy http://www.chapelperilous.net --- Conscience is the inner voice that warns us somebody may be looking. -- H.L. Mencken, "A Mencken Chrestomathy"
