ing the program in
December, but you probably can't wait that long.
I'd be interested to hear from anyone who's written a web search
engine. It seems to me that the algorithm for, say, google should be
fairly similar to what I'm doing for HR.
IO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 436-9166
for law firms, small businesses fax 436-0137
and non-profit organizations. pag
rrowed,
>vtb.equip_attr[1] AS year,
>vtb.equip_attr[3] AS model,
>vtb.equip_attr[4] AS type
> FROM log_tb ltb, vehicle_tb vtb
> WHERE ltb.v_id=vtb.equip_id
> GROUP BY ltb.v_id
> ORDER BY year;
--
__AGLIO DATABASE SOLUTIONS___
Function to create a temporary table or view and
summarizing from that.
-Josh Berkus
--
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (
table
2. a. Drop the Foriegn Key constraint
b. Update both the routes and hosts tables
c. Re-establish the foriegn key constraint
If either of these approaches doesn't work, you have a valid bug
report. COngratulations!
-Josh Berkus
--
__AGLIO DATABASE SOLUTIONS
uy" policy, in which
> case all of my work is for naught! ARGH!!
Well, if they don't use it, you can easily re-create your work at home
and GPL it. It also depends on the contract you signed ...
-Josh
--
__AGLIO DATABASE SOLUTIONS___
d.
-Josh
--
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 436-9166
for law firms, small businesses
le in MSSQL
rather than using the syntac you're suggesting. I've found
that the order that the MSSQL chooses to execute query
segments in can cause some unpredicatble results ... )
-Josh Berkus
e just use modulo
instead?
Thanks for any advice!
-Josh Berkus
Ms. Wong,
> This program seems to use a lot of the memory on the
> linux server, and
> the memory doesn't seem to be released at the end of
> execution. The same
> thing occurs when I try to connect to the database from
> MS Access via
> ODBC.
It's been my experience that Win32 ODBC does not dr
-Josh
--
__AGLIO DATABASE SOLUTIONS_______
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 436-9166
for law firms, small businesses
customer-transaction database could easily exceed 2 billion objects
created (and destroyed). Are there plans to expand this to Int8?
-Josh Berkus
P.S. My aplolgies if I've already posted these questions; I never
received them back from the list mailer.
g
aliasing) would be cosmetic.
-Josh
--
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 436
egant, and using serials instead of the OID not possible.
SO I'm concerned about the problems you mentioned above. pg_dump has a
-o option; are there problems with this? And how liekly are counter
overflow problems?
Josh Berkus
--
__AGLIO D
Tom,
> The trouble with pg_dump -o is that after reload, the OID
> generator
> will be set to max(any OID in the dumped data). So a
> dump & reload
> doesn't do anything to postpone OID-wraparound Ragnarok.
>
> As for the likelihood of overflow, figure 4G / tuple
> creation rate
> for your inst
Folks,
These JDBC issues belong on the pgsql-interfaces list, where
you'll find a community of JDBC bug-finders.
Have fun!
-Josh Berkus
. Is that an accurate summary assessment?
-Josh berkus
Folks,
Is the TEXT data type automatically a BLOB (or TLOB?), or
does it only become so if huge amounts of text are saved to
the TEXT field?
-Josh Berkus
Tom,
> Just FYI, I have committed code for 7.1 that allows ORDER
> BY to work
> correctly for a UNION'd query. A limitation is that you
> can only do
> ordering on columns that are outputs of the UNION:
As far as I know, that limitation is standard to all SQL
that supports UNION; the relational
tart getting re-used regardless if they are
already
present, then, like Tom says, it's Ragnarok. But it
seems
like somebody would have increased the OID to INT8 if
that
were a prospect.
-Josh Berkus
P.S. Bruce, I'm sorry about not sending my comments on
your
book. Do
> > OIDs as a primary index. None of my OIDs still in use
> will
> > be touched.
>
>
> No, it uses all oids, and can create duplicates.
Does this mean that Tom's "Wraparound Ragnarok" is the
accurate scenario?
-Josh Berkus
Tom, Bruce,
Thanks. I think that gives me a pretty clear picture. How can we
submit this whole OID thing to the PGSQL FAQ? Want me to write it up?
-Josh Berkus
P.S. BTW, my conclusion based on this discussion is that I will not use
the OIDs
Mr. Vadsholt,
This is a Postgre-SQL list. If you are using Microsoft SQL Server you
should proceed to http://msdn.microsoft.com/
-Josh Berkus
--
__AGLIO DATABASE SOLUTIONS___
Please see:
... H. The online docs appear to be down. When they're back up,
please check the sections on: Date/Time data types, and Date/Time
manipulation functions.
-Josh Berkus
P.S. Brian, a general tutorial on writing SQL, such as O'Reilly
Franz,
You'd better stay away from that syntax if you want to make your
applications portable. I can tell you that it won't work on MS SQL
Server or MySQL. I can't speak for Oracle.
-Josh Berkus
P.S. Thanks for the nifty construction ... I wouldn't have thought of
2. If 1. is "no", is there a way to do the above without programming the
type in C?
3. What sort of trouble am I going to get into trying to pull data from
a custom type into an external interface (i.e. PHP4)?
Thanks for your thoughts!
-Josh Be
UPDATE, because it
will space me having to remember to use the function every time I handle
a phone number field. I'll post the PLSQL function after I've written
it.
-Josh Berkus
--
__AGLIO DATABASE SOLUTIONS_______
Josh Be
d phone number for Primary Phone'
RETURN output_string
)
More code
-Josh
--
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solut
dded in data entry.
Soon.
-Josh
P.S. this makes you the first outside contributor to my open-source
project ... which isn't up on the web yet!
--
__AGLIO DATABASE SOLUTIONS___
jian's excellent PGSQL
book-in-progress, available on the Postgresql.org website. (Chapter 7, I
believe).
-Josh Berkus
--
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete in
Mr. May,
For discussions of JDBC, please subscribe to the pgsql-interfaces
list. You will find many JDBC users on that list.
-Josh Berkus
P.S. PGSQL folks, is there any way we can clarify this on the web page?
The JDBC users seem to keep ending
Eduardo,
Use the curval(serial) function. For more information, look in the
online docs under the name of that function. nextval(), curval() are
wonderful things!
-Josh Berkus
--
__AGLIO DATABASE SOLUTIONS___
eff or Jan Wieck.
-Josh Berkus
--
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 436-9166
for
ed columns must either
contain and aggregate function (e.g. SUM) or be named in the GROUP BY
clause.
O'Reilly has just come out with a SQL in a Nutshell book. I'd suggest
picking one up.
-Josh Berkus
--
__AGLIO DATABASE SOLUTIONS_______
ery, possibly using a PL/PGSQL or C function,
so that it works for any number of node levels.
-Josh Berkus
--
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data manage
Mr. May,
The reason you're having trouble is that the problem you've described
does not call for a union query at all. What you want is a simple GROUP
BY query:
SELECT Node_ID, Word, Count(*)
FROM NodeIndex
GROUP BY Node_ID, Word
-Josh Berkus
--
__AGLI
o run.
I've fooled around with drectional joins, views, and temporary tables,
but I can seem to find anything that works faster. Suggestions?
-Josh Berkus
--
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information
A *lot* faster. Like, 7x as fast. I'd forgotten about EXISTS, since I
so seldom have a use for it ... but this is shy it was created, I guess.
Thanks so much for your help!
-Josh Berkus
--
__AGLIO DATABASE S
erver ... I run two of the damn machines, and they're nothing but
grief.
-Josh
--
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
in fact, very fast
... just awkward to code and manipulate.
-Josh
--
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data man
xecution tests
between the two query structures.
Fortunately, this will all soon become moot; Tom says that outer joins
have been stable in the 7.1 build for a while. Speaking of which,
when's the 7.1 "release"? Huh, huh?
IN" on *either* version. Instead, use "NOT EXISTS", which is much, much
faster.
-Josh Berkus
--
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data ma
posted to our mailing list in error. This is a
PostgreSQL SQL-developers mailing list. If you need help with Oracle, I
suggest proceeding to www.oracle.com and looking for appropriate forums;
I'm sure there are many.
-Josh Berkus
--
__
r ')'" in a huge DDL
statement.
4. Use of named in addition to ordinal variables in PL/PGSQL functions
(e.g. $account_type, $period instead of $1, $2).
Thanks so much for your ongoing hard work!
-Josh Berkus
--
__AGLIO DATABASE S
b script to parse it out 25 records at a
time.
Hopefully, someone on this list will have done that before and can
provide less theoretical advice.
-Josh Berkus
P.S. I've also posted this to the pgsql-php list. I;ve quoted the full
text o
GLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 436-9166
for law firms, small businesses fax 436-0137
and non-profit organizations. pager 338-4078
San Francisco
writer about
dressing those up into an educational "chapter". It'll cost
me a little more than $0.05, but is only my fair
contribution. Look for something in february-march.
-Josh Berkus
at
your disposal. Post the text, I'll help clean it up!
-Josh
--
__AGLIO DATABASE SOLUTIONS_______
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 436-9166
fo
Frank,
Please look in the list archives. About 2 months ago this topic came
up and was discussed extensively (including a creative solution by yours
truly).
-Josh Berkus
--
__AGLIO DATABASE SOLUTIONS___
ing to itself on
(hopefully) indexed fields, in my expreience it runs very, very fast.
4. My PHP developer has reprogrammed the easily available PHP Tree
Control to uses this table structure (I don't know if he's giving it
out, but he said it wasn't very difficult).
-Josh Berk
.
-Josh Berkus
--
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 436-9166
for law firms
dirty.
Great. Maybe I'll buy it from you if I ever need to use Java :-)
-Josh
--
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management s
lly as straightforward
> as one might
> hope. Is there an easier way?
Hmmm. I don't know, Frank. That strikes me as a really
good, straightforward workaround to your problem. I'm not
sure what you could do that would be simpler. This is
practically a textbook example of why triggers are necessary
to retain relational integrity.
-Josh Berkus
string, it's a datetime field.
WHy are you trying to substring a datetime field, anyway?
-Josh
--
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology
se
> for invoice numbers.
>
> - Sequences are not rollback'able.
> - It seems overkill to have a table just for this.
> - What else?
Given a full set of business rules for invoice numbers, I
could probably throw you a solution. Gods know that I've
devised enough invoicing systems in the past. Please post
more detail.
-Josh Berkus
___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 436-9166
for law firms, small businesses fax 436-0137
and non-profit organizations.
rom it too for more exotic tasks.
Also, pgAccess, a tcl/tk GUI, is excellent although incomplete. I end
up using a combination of pgAccess, KpgSQL, and command line access.
-Josh Berkus
--
__AGLIO DATABASE SOLU
e, please reply and I'll give you a pointer.
Othervwise, RTFM.
-Josh Berkus
?
For example, if I want to locate the NEXTVAL. do a bunch of
stuff, and then insert the record, does this obligate me to
take the DEFAULT NEXTVAL out of the table and do it the
NEXTVAL insertion in my functions, or is it irrelevant?
-Josh Berkus
mples?
Anybody (Jan?) who can shed some light on the above will
receive my enthusiastic gratitude in ASCII text.
-Josh Berkus
P.S. I'm using Postgres 7.0.2 on SuSE 7.0 and use pgaccess
extensively for function editing.
P.P.S. My most heartfelt gratitude to Jan Wieck for writing
some decen
Folks,
Oh, yes, one more:
4. If I pass a NULL to any of the parameters of a PL/PGSQL
function, any (other) VARCHAR parameters are set to NULL as
well.
Thanks!
-Josh
Tom,
> Not only varchar --- any other parameters, period. And
> not only that,
> but the result is taken as NULL no matter what you try to
> return.
Not quite. I tried the following:
Parameters: $1=integer, $2=NULL, $3=varchar
And I had the function test for nulls. It read the first
paramet
Folks,
ALTER TABLE won't work until 7.1. CUrrently, I have a table
that needs one small change, but it's refrenced as a foriegn
key by 7 other tables. Any suggestions on how I can make
the table change without having to drop and re-create 8
tables?
-Josh Berkus
Tom,
> Damn! When is the "holy grail" of PostgreSQL going to be
> stable enough to use? Beta3 still has a "not advisable
> for
> production" warning, and I'm being tied up in knots by
> the
> number of things I need in 7.1.
Ooops! That may have sounded a little harsh. I am a bit
desperate, b
en sent to the client and emitting
> NOTICE's then could confuse the fe/be protocol.
Hey, just the fact that you spit back "Error on Line 38" cuts my
debugging time in half over the SQL handler's "Error at or near ';'"
Of course, running a tail on
: Why do you want a Bit type? What
purpose does it serve that INT2 and BOOLEAN do not?
I'm not being sarcastic -- this is an important question to
ask before creating *any* custom type on any RDBMS.
-Josh Berkus
Folks,
Well, I now have a copy of Bruce's book on order. Bruce, if
your sales have been slow, don't let A-W blame it on the
online draft. According to Stacy's, it takes them an
average of 7 working days to get Ingram to cough up a new
copy, which is twice the normal period for tech books. As a
Keith,
> This is a compatibility issue. While I prefer to use
> BOOLEAN, this is SQL3
> and not available on the (unfortunately must use)
> MS-SQL/MSDE platform.
>
> My options are to use a CHAR field and re-write my code
> for "T" and "F" or
> an int field and re-write my code to use "field=0"
Josh Berkus wrote:
> Well, yes. This is beacause BLOBs are NOT part of the SQL
> standard and IMHO a bad idea relationally; thus their
> implementation is entirely proprietary to the RDBMS. The
> solution is not to use BLOBs.
Ooops. Let me re-state: This is because the *implem
27;d also need to see if our functions survive the
dump ...
-Josh Berkus
--
__AGLIO DATABASE SOLUTIONS_______
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 436-9166
...
-Josh Berkus
--
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 436-9166
for law firms, small businesses
.id = t2.id
Although this is a subselect in the FROM clause, something
we've had trouble with as I recall.
-Josh Berkus
Folks-
Answered my own question about the backslashes before
spaces, in text returned as results from functions: it's a
bug in kpsql, one of the interface tools I was using.
Somebody might want to forward this to the Interfaces list.
-Josh Berkus
Najm
CURRVAL('sequence_name')
For this and other sequence and serial functions, please see the online
version of Bruce's book.
BTW, O'Reilly's "SQL in at Nutshell" also catalogs all PGSQL functions
from ver. 6.5.
-:).
> > Regards, Najm
>
> select nextval('nameofseq');
Sorry, no. You want SELECT CURRVAL('sequence_name'). NEXTVAL select
the NEXT value, and increments the sequence in the process.
-Josh Berkus
--
__AGLIO DATABASE SOLUTIONS____
;t even *have* your book yet, it's better than nothing! :-)
-Josh Berkus
--
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
Folks,
Fascinating as this thread is, is the SQL Developers list really the
appropriate place for it? Don't we have a Policy list or something?
-Josh Berkus
--
__AGLIO DATABASE SOLUTIONS__
)
> ERROR: referential integrity violation - key
> referenced from
> users not found in age_list
Simple ... you have values in the AGE column that are not in
the age_list table. Thus you're in violation of the foriegn
key you're trying to establish.
-Josh Berkus
databases gone bad,
composite data types have not earned a warm place in my
heart ...
-Josh Berkus
sion key.
-Josh Berkus
--
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 436-9166
for law firm
tionally
compliant is not achievable.
-Josh Berkus
--
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 436-9166
for law firms, small bu
e my code easier to read
and maintain. And, of course, you may have already implemented one or
the other in 7.1 (which I have not yet got to run on an alternate port).
Thanks for your hard work and consideration towards us users.
-Josh Berkus
--
__AGLIO DATABASE SOLUTIO
e 'plpgsql';
> CREATE
Hmmm? When I've tried creating similar functions, I got from the
compiler:
Error at or near 'END'
I'll try your code above as a test, then try re-modifying some of my own
functions.
-Josh Berkus
--
______A
eks :-) Is this the
> appropriate list, or
> should I move over to hackers?
You should probably cross-post. This list is the place to
see if a number of other developers are interested in the
functionality you propose (yes), hackers is probably the
place to ask how to make the actual changes.
I can't help. Heck, I can't even get 7.1 beta to run on an
alternate port.
-Josh Berkus
P.S. BTW, John, I'm thrilled to get a discussion of issues,
going here in addition to the how-tos!
re there plans to improve this, or has somebody written a script that
handles the steps involved?
4. I'm not trying 7.1 beta 3. I noticed that for this version,
Theodescu's PGAccess lists all builtin functions along with the
user-defined functions in the functions window. Anyb
ocess running in
the foreground, you see all sorts of useful debugging info
as execution takes place. Not quite as complete, but a
*lot* faster than checking the log manually.
-Josh
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete in
Tom,
What's the syntax for directional joins in 7.1 beta?
Thanks!
-Josh Berkus
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions
Tom,
Sorry! Never mind, I found it in the Development Docs.
Grazie!
-Josh
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and
;
And I get:
ERROR: JOIN/ON CLAUSE REFERS TO 'c' WHICH IS NOT PART OF
JOIN.
What's wrong here?
-Josh Berkus
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED
an, please tall me if I'm wrong in this; I could really
use the functionality if it *is* possible.)
You could use some creative manipulation of the system
tables to achieve the same result, however.
-Josh Berkus
__AGLIO DATABASE SOLUTIONS_______
or something similar (it may be EXECUTE), which uses the dynamic sql
> elements of plpgsql. I think.
Is this true, Jan? Does anyone have more specific documentation?
-Josh Berkus
--
__AGLIO DATABASE SOLUTIONS___
luenced by whatever you guys are planning
to do about Stored Procedures.
-Josh Berkus
--
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions
TE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER
"plpgsql_call_handler" LANCOMPILER 'PL/pgSQL';
-Josh Berkus
(Instructions courtesy of Jeff at PGSQL Inc.)
--
__AGLIO DATABASE SOLUTIONS___
ault for keying off a non-unique value?
And, if the latter, is there a way I can construct a foreign
key constraint that keys onto a view or query?
Grazie!
-Josh Berkus
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Comple
Folks,
Where do I send bug reports for 7.1 beta? I;'ve looked on the web
site, and don't see an address or bugtraq forum.
-Josh Berkus
--
__AGLIO DATABASE SOLUTIONS___
ral
functions ... my search routines will be hard to run without
it. Perhaps you could turn off EXECUTE by default, but
allow it as a compile-time option for those of us wise
enough to understand the dangers?
-Josh Berkus
__AGLIO DATABASE SOLUTIONS___
Complete inform
.
-Josh Berkus
--
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 565-7293
for law firms, small businesses
t app uses a large assortment of
PL/pgSQL functions as pseudo-middleware).
-Josh Berkus
--
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions
er. You can't get that by scanning an index
> that has both columns in the same order...
Aha. I see the problem. My approach would be to use two seperate
indexes, but maybe that's not sophisticated enough :-)
And, Tom, you're working too hard again. It's Saturday. Go home.
1 - 100 of 838 matches
Mail list logo
