[SQL] car mileage summation / sledgehammer method
OK, this is quite brutal and is going to be very expensive, but I think it does what you want. The trick lies in the idea of joining a table with itself. Thus, I first define a virtual copy of the table (in my case "dup") and then produce a query that joins the table to this copy. Enjoy. Oliver detail=# \d mileage Table "mileage" Attribute | Type| Modifier ---+---+-- miles | integer | date | timestamp | detail=# select * from mileage; miles | date ---+ 5 | 2000-08-01 00:00:00+02 9 | 2000-08-02 00:00:00+02 4 | 2000-08-03 00:00:00+02 (3 rows) detail=# \d dup View "dup" Attribute | Type| Modifier ---+---+-- miles | integer | date | timestamp | View definition: SELECT mileage.miles, mileage.date FROM mileage; detail=# select mileage.miles, mileage.date, sum(dup.miles) from mileage, dup where dup.date <= mileage.date group by mileage.date, mileage.miles order by mileage.date; miles | date | sum ---++- 5 | 2000-08-01 00:00:00+02 | 5 9 | 2000-08-02 00:00:00+02 | 14 4 | 2000-08-03 00:00:00+02 | 18 (3 rows)
Re: [SQL] Regular expression query
> The regexp package we currently use implements POSIX 1003.2 regexps > (see src/backend/regex/re_format.7). I believe there is an item on the > TODO list about upgrading the regexp parser to something more modern > ... feel free to hop on that project if it's bugging you ... I would like to recommend ftp://ftp.cus.cam.ac.uk/pub/software/programs/pcre/ which stands for "PERL compatible regular expressions" and has an expression analyzer & compiler for more efficient repeated matching. Since Philip Hazel has only written this package to support his Mail Transfer Agent (exim), it is optimised for performance. I hope I have been of help, Oliver Seidel
[SQL] array column -- do you really want this?
Hello Umashankar, *warning* this doesn't answer the question you asked *warning* you write that you are new to object databases. The problem that you describe sounds like it might much more completely be solved by not using the object features and avoiding an array alltogether. If you simply wish to solve the problem, then I can recommend some reading on normal forms, which goes into all the problems that can occur in databases without consideration for storage anomalities. You can find a good article here: http://home.earthlink.net/~billkent/Doc/simple5.htm Your database would then be in "first normal form" and not contain lists inside a field any more. You simply create two columns, the first listing the patient ID and the second listing the illness. Patient IDs are allowed to occur repeatedly. You could retrieve all illnesses for a particular patient by the use of: select illness from patient_illness where patient_id=1434; or you may retrieve all patients' illnesses by querying: select * from patient_illness order by patient_id,illness; I hope that helps, Oliver
[SQL] OFFTOPIC: search and replace with unix tools
> "Josh" == Josh Berkus <[EMAIL PROTECTED]> writes:
Josh> Folks, I need to strip certain columns out of my pgdump
Josh> file. However, I can't figure out how to use any Unix-based
Josh> tool to search-and-replace a specific value which includes a
Josh> tab character (e.g. replace "{TAB}7 00:00:00" with "" to
Josh> eliminate the column).
Unix lives by the shell pipe. Set "exit on error", to avoid data loss
in case of "filesystem full", proceed by using "tr" to translate
single characters within the file to something more easily replacable,
do the replace with "sed", translate back using "tr", move over old
file, done:
---
#!/bin/bash
set -e -x
cat "$*" | \
tr '\t' '§' | \
sed -e 's/§7 00:00:00//g' | \
tr '§' '\t' | \
cat > x.$$
mv x.$$ "*"
---
(please don't kill me for the two "cat" operators, they serve no
purpose besides legibility).
so long,
Oliver
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
