[SQL] BirthDay SQL Issue
Hi there
This is my first posting here, please forgive me if I make any
mistakes here.
Ok
I have the structure
{CLIENTS} = Client ID, First Name, Surname, DOB, Address, Home Phone
No, Mobile Phone No
As one relation / table (There are several others in this db that are
not related to this issue)
My problem is this
I need to be able to select two dates on my webby so I have a range of
dates
E.G. (using English date format DD/MM/)
01/09/2005 to 01/10/2005
Then I want to be able to get, using some nifty query, everyone's
birthday that falls between those two dates. So if someone's birthday
was 04/09/79 it would return that tuple
I have been stuck on this for ages. Any my search for help has brought
me here.
If anyone out there can help me, in any way, I would be very grateful.
Kind regards
Brett Halligan
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[SQL] FUNCTION returing multiple rows
I have seen in the postings that it is not possible to return multiple rows from a function. However, when I look at the programmers guide (Ch. 4), it almost leads me to believe otherwise: -8<--- The following more interesting example takes a single argument of type EMP, and retrieves multiple results: select function hobbies (EMP) returns set of HOBBIES as 'select (HOBBIES.all) from HOBBIES where $1.name = HOBBIES.person' language 'sql'; -8<--- I know that it only says 'multiple results' but with the 'HOBBIES.all', it almost leads me to believe that it is retrieving all of the fields. Is 'all' a keyword, or is this just one field in HOBBIES? Also, is there any type of function return multiple rows (plpgsql, pltcl, etc)? If not, then is there anyway to fake it out to do this. Could I concatenate the fields together to form one field per row. This would look like multiple results of 1 field to PG. Also, if I did this, is there a delimiter I should use? for example: select field1 || ':' || field2 || ':' || field3 from some_table; Where ":" is the delimiter to send back. Sorry for this question, but I am porting over an app from Oracle, and they do not want to change the java code that calls this; so I need to make this work somehow. thanks, --brett
[SQL] Rules and transactions
If I have a rule, is the rule inside a tranaction along with the table that it references. For example, if I have a rule that deletes an entry from table B, whenever an entry in table A gets deleted, then is the delete for table A and table B wrapped inside the same transaction? Same question I guess goes for triggers. thanks, --brett
Re: [SQL] Doing a regexp-based search/replace?
You could write a Tcl (i.e. pltcl) function, and use that to do what you want:
CREATE FUNCTION remove(varchar) RETURNS varchar AS '
set input $1
regsub -- {-.*$} $input {} output
return $output
' language 'pltcl';
[NOTE: untested]
you may have to monkey with the regexp to get exactly what you want...
--brett
On Thu, 18 Oct 2001 12:03:28 -0400 (EDT)
Steve Frampton <[EMAIL PROTECTED]> wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> Hello:
>
> I've got a table containing property_id's with values of the form
> ###-. I would like to discard the slash onwards (and I can't use a
> substr() because I am not guaranteed if a) the - portion exists, b)
> what position it exists from.
>
> If this were a text file, I would use a sed expression such as:
>
> cat textfile | sed 's/-.*$//'
>
> I've been looking for a way to do this with PostgreSQL but so far haven't
> found a function that seems to be suitable. I thought maybe I could do it
> with translate, but translate doesn't appear to work with regular
> expressions. So far I've tried things like:
>
> select translate(property_id, '-.*', '') from mytable;
>
> I need to do this, because the -.* portion of my property_id was entered
> in error, and I would like to do an update on the entire table and just
> have the left-hand side of the property_id column remaining.
>
> Any ideas? Thank you in advance.
>
> - ---< LINUX: The choice of a GNU generation. >-
> Steve Frampton <[EMAIL PROTECTED]> http://www.LinuxNinja.com
> GNU Privacy Guard ID: D055EBC5 (see http://www.gnupg.org for details)
> GNU-PG Fingerprint: EEFB F03D 29B6 07E8 AF73 EF6A 9A72 F1F5 D055 EBC5
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.0.0 (GNU/Linux)
> Comment: For info see http://www.gnupg.org
>
> iD8DBQE7zv1TmnLx9dBV68URAisEAJ4nNYz4lxpgWojULE/Xo9SUb5IexgCfS9At
> J6kAVn/3vFHeJkl9bjr4AcQ=
> =W4xQ
> -END PGP SIGNATURE-
>
>
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster
_
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Granting database level permissions...
have a look at pg_hba.conf in your data dir. it's all in there. Steve "Thomas Swan" <[EMAIL PROTECTED]> wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > Is it possible to grant database level access to a user in PostgreSQL? > > I have created a user and database, and I want the user to have full > control over that database. I have been through the online docs and > news groups but have not found a statement or answer to the question. > > Thomas > > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Holiday Calculations?
I couldn't resist, so I went ahead and did all of them. I might be using
these in a future app anyways. Again these are Tcl procs, but you may be
able to transfer the logical to another language, if you want.
proc getTG {year} {
set di \
[clock format [clock scan $year-11-30] -format "%w"]
return [expr {30 - ((($di+1)%7)+2)}]
}
proc getMem {year} {
set di \
[clock format [clock scan $year-05-31] -format "%w"]
return [expr {31 - ((($di+6)%7))}]
}
proc getLabor {year} {
set di \
[clock format [clock scan $year-09-01] -format "%w"]
return [expr {(((8-$di)%7) + 1)}]
}
Josh Berkus wrote:
>
> Folks,
>
> I'm spec'ing a calendar app for PostgreSQL, and was wondering if anyone
> had already solved the following problem:
>
> How can I calculate the dates of American holidays?
>
> Obviously, Christmas & New Year's are easy. As is July 4.
>
> However, Thanksgiving is the last Thursday in November, unless the month
> ends on a Thursday or Friday, in which case it is the next-to-last.
> Memorial Day and Labor Day are simpler, but also use the "First or Last
> Monday in x month" idea.
>
> I was wondering if anyone had already figured out these calculations, in
> any language (SQL would be terrific).
>
> Thanks!
>
> -Josh
>
> __AGLIO DATABASE SOLUTIONS___
>Josh Berkus
> Complete information technology [EMAIL PROTECTED]
>and data management solutions (415) 565-7293
> for law firms, small businessesfax 621-2533
> and non-profit organizations. San Francisco
>
>
>Name:
>Type: Plain Text (text/plain)
>Encoding: base64
>
>Name:
>Type: Plain Text (text/plain)
>Encoding: base64
>
>Name:
>Type: Plain Text (text/plain)
>Encoding: base64
>
>
>
> ---(end of broadcast)---
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
_
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Holiday Calculations?
Based on your criteria (I never really had thought about Thanksgiving
before, as far as when it hits), but here is a small Tcl proc that I
think will do it. You may need to add error checking. Takes the year
(i.e. 2001), and returns the day date (i.e. 22).
---
proc getTG {year} {
set di \
[clock format [clock scan $year-11-30] -format "%w"]
return [expr {30 - ((($di+1)%7)+2)}]
}
-
I think this will do it (It might not work for esoteric cases...I didn't
check all). Sorry, I don't know SQL well enough to give an answer in
SQL...maybe someone else will though...or maybe you can extrapolate this
proc into SQL.
I think you may be able to modify this for Memorial and Labor day as
well.
--brett
Josh Berkus wrote:
>
> Folks,
>
> I'm spec'ing a calendar app for PostgreSQL, and was wondering if anyone
> had already solved the following problem:
>
> How can I calculate the dates of American holidays?
>
> Obviously, Christmas & New Year's are easy. As is July 4.
>
> However, Thanksgiving is the last Thursday in November, unless the month
> ends on a Thursday or Friday, in which case it is the next-to-last.
> Memorial Day and Labor Day are simpler, but also use the "First or Last
> Monday in x month" idea.
>
> I was wondering if anyone had already figured out these calculations, in
> any language (SQL would be terrific).
>
> Thanks!
>
> -Josh
>
> __AGLIO DATABASE SOLUTIONS___
>Josh Berkus
> Complete information technology [EMAIL PROTECTED]
>and data management solutions (415) 565-7293
> for law firms, small businessesfax 621-2533
> and non-profit organizations. San Francisco
>
>
>Name:
>Type: Plain Text (text/plain)
>Encoding: base64
>
>Name:
>Type: Plain Text (text/plain)
>Encoding: base64
>
>Name:
>Type: Plain Text (text/plain)
>Encoding: base64
>
>
>
> ---(end of broadcast)---
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
_
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] list of tables ?
can anyone point me in the right direction ? i need to list all the tables in a database. i've looked at pgadmin_tables which is empty and pga_schema whihc contains a sinlge row i don't want to parse ... is there an easier way t get a list of tables ? i'm on 7.2 ta, Steve Brett ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] list of tables ? -update to question ...
sorry ... i didn't make myself clear ... i have of course come across \dt before ... what i meant was via sql as in 'select tablelist from ' Steve > -Original Message- > From: Stephane Schildknecht [mailto:[EMAIL PROTECTED]] > Sent: 11 July 2002 15:06 > To: Steve Brett > Subject: Re: [SQL] list of tables ? > > > Le jeu 11/07/2002 à 16:00, Steve Brett a écrit : > > can anyone point me in the right direction ? > > > > i need to list all the tables in a database. > > > > i've looked at pgadmin_tables which is empty and pga_schema > whihc contains a > > sinlge row i don't want to parse ... > > > > is there an easier way t get a list of tables ? > > > > i'm on 7.2 > > > > ta, > > > > Steve Brett > \d > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] list of tables ?
thanks. Steve > -Original Message- > From: Achilleus Mantzios [mailto:[EMAIL PROTECTED]] > Sent: 11 July 2002 15:10 > To: Steve Brett > Cc: Pgsql-Sql (E-mail) > Subject: Re: [SQL] list of tables ? > > > On Thu, 11 Jul 2002, Steve Brett wrote: > > > can anyone point me in the right direction ? > > > > i need to list all the tables in a database. > > > > i've looked at pgadmin_tables which is empty and pga_schema > whihc contains a > > sinlge row i don't want to parse ... > > > > is there an easier way t get a list of tables ? > > > > i'm on 7.2 > > select * from pg_tables; > > > > > ta, > > > > Steve Brett > > > > > > > > ---(end of > broadcast)--- > > TIP 4: Don't 'kill -9' the postmaster > > > > -- > Achilleus Mantzios > S/W Engineer > IT dept > Dynacom Tankers Mngmt > tel:+30-10-8981112 > fax:+30-10-8981877 > email: [EMAIL PROTECTED] > [EMAIL PROTECTED] > ---(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] how do i import my sql query result to a file
\? will get you a list of the commands in psql. Steve > -Original Message- > From: Joseph Syjuco [mailto:[EMAIL PROTECTED]] > Sent: 18 July 2002 22:47 > To: [EMAIL PROTECTED] > Subject: [SQL] how do i import my sql query result to a file > > > how do i import results of my select query to a file > thanks > > > > > ---(end of > broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > ---(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] interval conversion
Hello all! I have a possibly stupid question- I'm doing some time calculations yielding intervals, and for my purposes I need to convert the interval(say, "1 day 8 hours") into (floating point) hours. While there's a plethora of handy date_extract functionality and the like, I need a conversion. Any suggestions on how to accompish this? Simpler the better, of course. Thanks! Brett ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] interval conversion
>> I have a possibly stupid question- I'm doing some time calculations yielding >> intervals, and for my purposes I need to convert the interval(say, "1 day 8 >> hours") into (floating point) hours. While there's a plethora of handy >> date_extract functionality and the like, I need a conversion. >> >> Any suggestions on how to accompish this? Simpler the better, of course. > > extract(epoch from some_interval)/3600 [slaps head] I swear, I studied the date/time sections of the manual for quite some time...really...Sgh :-) Thanks Tomasz! Brett ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Problem with pg_try_advisory_lock and two connections (seemingly) getting the same lock
Hi, I currently have a simple queue written ontop of Postgres. Jobs are inserted and workers periodically check for jobs they can do, do them, and then delete the rows. pg_try_advisory_lock is used to (attempt to) stop two workers from doing the same job. (I'm working on moving to a "real" messaging queue right now, this is more a point of curiosity and education now.) Here is my queue table, CREATE TABLE queue ( id serial NOT NULL PRIMARY KEY, rcvd timestamp with time zone, sent timestamp with time zone, host character varying(32), job character varying(32), arg text ); Here is an example query, SELECT q.* FROM (SELECT id, job, arg FROM queue WHERE job = 'foo' OR job = 'bar' OFFSET 0) AS q WHERE pg_try_advisory_lock(1, q.id) LIMIT 10 (For information on OFFSET 0 see: http://blog.endpoint.com/2009/04/offset-0-ftw.html) Now if I have two workers running I will periodically see that each worker gets a row with the same q.id (and thus does the work). How is that possible? The outer query seemingly does a WHERE on an advisory_lock. Does anyone have any ideas? Am I grossly misusing advisory_locks? Thanks, Brett -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
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"
