eans that it is possible to get both your desired rowcounts out of
a *single* query, using subselects. "SQL for Smarties" can help you
learn to build this kind of query.
-Josh
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
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
s.
Browse through and you should find them.
-Josh Berkus
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 565-7293
for law fir
message.
-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
total_of_items
FROM (SELECT invoice_id, sum(item_amount) FROM invoice_items) iit
WHERE iit.invoice_id = invoices.id;
-Josh Berkus
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
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
t record, simply reverse the ORDER
BY and take the second record.
e.g.:
If you want the next-to-last (n-1) record from:
SELECT * FROM syslog
ORDER BY entrytime;
Then ask for:
SELECT * FROM syslog
ORDER BY entrytime DESC
LIMIT 1 OFFSET 1;
Easy, no?
-Josh Berkus
__AGLIO DATA
__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 Francisc
Folks:
Do I need to worry about this:
pq_flush: send() failed: Broken pipe
... which appears in the log intermittently?
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED
into 2 fields (e.g. 'element 1|element2')
2. Do the operation in your middleware, where you can use C, Java,
Python or similar to pass arrays.
-Josh Berkus
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete inform
n anyone point me in the right direction, or should I be posting this
to a different list?
-Josh Berkus
P.S. Postgres 7.1.2 running on SuSE Linux 7.2 on a Celeron 500/128mb
RAM/IDE HDD.
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
C
for users
porting from MS SQL Server or SyBase.
Grazie!
-Josh Berkus
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 565
me to upgrade ;-)
-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
a
bly at GROUP BY.
-Josh
P.S. I'm sure you don't mean for your e-mails to come across as
antagonistic when you are asking for help. Can you please be careful of
your phrasing?
______AGLIO DATABASE SOLUTIONS___
Josh Berkus
Compl
with a little nested subselect, but I'm having
a lot of difficulty picturing what we're looking at.
-Josh
__AGLIO DATABASE SOLUTIONS_______
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data managemen
__
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
--
conversation and basically determined that the problem is 90% likely to
be disk access time. Bummer 'cause I didn't want to add any hardware to
this machine, but, well, there ya go.
-Josh
__AGLIO DATABASE SOLUTIONS___
me from someone else.
I could suggest a couple of workarounds, if you gave a fuller
description of exactly what you're trying to accomplish.
-Josh Berkus
P.S. Please do not cross-post to more than 2 lists at a time. The
Postgres lists have been kept to a managable volum
robably not going to find people on this list
capable of diagnosing either problem. Try the pgsql-interfaces list
instead. (http://postgresql.advancecreations.com/users-lounge/index.html)
-Josh Berkus
__AGLIO DATABASE SOLUTIONS_______
J
, twice, to a third table. I don't
believe that this structure has a particular name. It's very common.
-Josh Berkus
*= Bruce, what I mean by "too short" is that you only have about 80
pages of introduction to SQL, which makes it a good first intro but does
not bridge the ga
e as airport2
FROM desitination dest JOIN airport depart_air
ON dest.airport_dep_id=depart_air.airport_id
JOIN airport arrive_air
ON dest.airport_arr_id=arrive_air.airport_id
Got it?
-Josh
__AGLIO DATABASE SOLUTIONS_______
hat'll make me friends at Stacy's Bookstore. "I'm not sure of the
title, and I don't know the author or publisher, but it's about
databases and it's pink." ;-P
-Josh
__AGLIO DATABASE SOLUTIONS___
UES" syntax is only appropriate if you are inserting a set of
constants with no SELECT statement involved.
-Josh Berkus
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and d
fund nor write it myself, so that'll remain a "wish list" item
until maybe Red Hat sees money in it.
Speaking of which, does anyone know if RH has a seperate "wish list" for
what us developer-types would like to see our of RHDB?
-Josh
__AGLIO DATABASE SOLUTIONS___
it into the server. You heard it here first!
>
Does this mean I'll be forced to learn Emacs?
-Josh
(Who uses Kedit, Pico, and Joe)
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL P
#x27;ll
ask ...
-Josh
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 565-7293
for law firms, small businessesfax 621-2
mns in alphabetical
order (!?!). They then had to call me, and re-ordering the index
columns cut the delay in single-row queries (especially DELETE queries)
by 80%.
> so, i am hopeful that there is some sort of postgresql performance
> faq
> for queries.
Somebody wanna re-organize
s to the database, especially for
a marginal query performance gain.
To phrase it another way: Optimization problems cost you seconds. DB
Design and normalization problems cost you *days*.
-Josh Berkus
__AGLIO DATABASE SOLUTIONS___
) as mcount FROM ml
WHERE ml.state <> 11 GROUP BY jid) ma1,
(SELECT jid, COUNT(oid) as mcount FROM ml
WHERE ml.state in (2,5) GROUP BY jid) ma2
WHERE j.fkey = 1 AND mj.jid = j.id
AND ma1.jid = j.id AND ma2.jid = j.id
GROUP BY j.id, j.created, ma1.mcount, m
ues perfect for attaching
foriegn keys.
-Josh
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 565-7293
for law firms, small businesses
elp there.
-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
BY statement as you appear to use it in that query is
permitted and correct. I suspect that your problem is located somewhere
else. For example, what interface tool are you using to send queries to
the database?
-Josh
__AGLIO DATABASE SOLUTIONS___
Richard,
I'm curious now. What happens if you remove the table qualifications,
e.g.:
ORDER BY type, nom;
-Josh
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and
s/where clauses in the
*exact* order of the indecies in SQL Server, it ignores them and does a
table scan. This is especially deadly because table scans are about 1/2
as fast in SQL Server as they are in Postgres.
-Josh
__AGLIO DATABASE SOLUTIONS___
is limited (but I'm not as good as Bruce or Chris)
-Josh
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 565-7293
for law fir
gt; Thanks for any help,
Can you be more explicit about what you are attempting? Perhpas yyou
could give specific examples from you actual data.
-Josh
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [
Folks,
Also, any reviews on foriegn-language SQL or database books (in the
appropriate language) would be appreciated.
-Josh
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED
At least here, you can get a message to the actual database developers.
Still, I understand your frustration.
-Josh
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and dat
*
search_attributes.attribute_value )
(the ~* allows searches on partial value matches)
This will give you these results:
20 Mary Stuart 1600 Pensylvannia Ave. HairBrown
20 Mary Stuart 1600 Pensylvannia Ave. EyesHazel
20 Mary Stuart 1600 Pen
course, what they really
want is for you to make a commitment to donate twice a year, every year.
-Josh
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management sol
I've looked it over again
and there's nothing missing. Is it possible, Frederick, that the comma
after "matches" or "people_attributes" got cut off?
-Josh
__AGLIO DATABASE SOLUTIONS___
Josh Berku
__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 or
RCHAR ) RETURNS VARCHAR AS'
SELECT SUBSTR($1, 1, ((STRPOS($1, ''-'') - 1));
END;'
LANGUAGE 'SQL';
Then run:
UPDATE main_table SET property_id = remove_propid_tail(property_id)
WHERE property_id ~ '-';
-Josh
__AGLIO DATABASE SOLUTIONS__
L-SQL is for more advanced SQL issues.
Yes, you can do this easily, Please see:
http://www.postgresql.org/idocs/index.php?datatype.html#DATATYPE-NUMERIC
Look at secion 3.1.1 on the page.
-Josh
__AGLIO DATABASE SOLUTIONS___
Josh Berku
g OIDs as an index, and you've
just found one more!
-Josh
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 565-7293
for law f
eys and using those. Some Trigger types require referencing
the OID, but that's about it.
-Josh
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions
on Postgres, but there are no definite plans.
-Josh Berkus
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 565-7293
for law f
le, but does have a unique index. The usq
is populated by a single sequence "universal_sq" which is shared between
tables, thus allowing all tables usq uniqueness between them.
This strategy has allowed me to write a number of functions which are
table-agnostic, needing only
tml
section 3.1.1
I can't imagine how it would be more clear.
-Josh
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 565-7293
illy", for example, I could:
UPDATE table SET name = 'O\'Reilly';
Since you want to save an actual backslash, do this:
UPDATE table SET field = 'C:\\windows';
In your interface code, you may which to add a function that doubles
your backsl
oresighted enough to
supply. Unfortunately, that does mean that this solution is not
portable to other RDBMSs, but as PostgreSQL grows in market share,
that's less of a concern.
-Josh Berkus
__AGLIO DATABASE SOLUTIONS___
Josh B
__
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 565-7293
for law firms, small businessesfax 621-2533
and non-profit organizatio
Bhuvan,
> How can we connect to different database using plpgsql function? Can
> we?
No, you can't.
-Josh
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
UTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 565-7293
for law firms, small businessesfax 621-2533
and non-profit
ead up on them in the postgreSQL docs. Also see
my posts on pgsql-sql for the last week regarding primary keys.
-Josh Berkus
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
Eddy,
> What value should I RETURN for a SQL FUNCTION that contains an INSERT
> statement?
OPAQUE.
-Josh
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
an
.
-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
ast
Stop quoting your integers. It should be
WHERE user_id = 1346
No quotes for numbers.
-Josh
__AGLIO DATABASE SOLUTIONS_______
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions
what happens if you swap out the network card?
-Josh
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 565-7293
for law firms, small bus
d LEFT OUTER JOIN
(SELECT scanid, volume as C_volume FROM volumes WHERE region = 'C') cv
ON scan.scanid = cv.scanid
ORDER BY scanid;
This approach can be adapted to include aggregates and the like.
-Josh Berkus
__AGLIO DATABASE SOLUTIONS_______
ers of records.
-Josh
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 565-7293
for law firms, small businessesfax 621-2
;3"
work just as well, and don't have the 12-byte overhead of a MAC address.
-Josh
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415
rojects to create roll-ups; it's the "best" SQL92 approach to
the "pivot table" problem. However, it will not work in 7.0.3.
-Josh
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information tec
y to
> restructure this solution to get around this limitation?
No, you need to upgrade. What's the obstacle to using 7.1.3, anyway?
-Josh
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology
Yes, you're right. Sorry! Disregard my commentary about the second
view.
-Josh
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions
A are unique.
-Josh
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 565-7293
for law firms, small bu
e2.table1_id)
LEFT OUTER JOIN table3 ON (table2.id = table3.table2_id);
-Josh
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 565-7293
f
__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
L 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 businessesfa
RETURN FALSE;
ELSE
RETURN TRUE;
END IF;
END;'
LANGUAGE 'plpgsql';
... then you build your function around this:
CREATE FUNCTION my_function ( ...
...
IF NOT table_exists(''my_table'') THEN
CREATE TAB
list is gonna
do it, y'know?
-Josh
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 565-7293
for law firms, small businesses
le
in the FROM clause, while in Postgres such repetition is prohibited.
This is mainly due to the fact that UPDATE ... FROM is not well-defined
in the SQL 92 standard.
-Josh
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete inf
Josh
-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-profi
SE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 565-7293
for law firms, small businessesfax 621-2533
and non-p
osh
__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 organiz
upported in PL/pgSQL,
but not in Postgres SQL.
-Josh
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 565-7293
for law firms, small b
aven't seen any answers on the SQL
list. I can think of several workarounds, depending on what kind of a
user interface you're using, such as having a linked batches table.
-Josh Berkus
__AGLIO DATABASE SOLUTIONS_______
Josh Berk
of the application.
However, keep in mind that a lot of people (the Postgres core team
included) do not agree with me about inheritance and its limitations.
-Josh
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information te
Joel,
> A query such as "select sum(pages) from job_documents where
> delivery_type='print'" returned 0 in version 7.0.3 if there were no
> rows
> matching the query. In 7.1.3 the result is NULL if no rows match the
> query. Why the change? Which result is "correct" according to the
> SQL
> stand
x27;;
> EXECUTE query;
> ...
> END;
> ...
I'm pretty sure your quotes are correct. However, I believe "query" is
a reserved word. Try using a different variable name.
-Josh
__AGLIO DATABASE SOLUTIONS___
lue that ensures it is allways after another attribute value called
> departure_time (declared as TIME).
> Any know how this constraint would be written??
Easy:
CONSTRAINT arr_dep_time CHECK ( arrival_time > departure_time )
__AGLIO DATABASE SOLUTIONS_______
BASE 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 Fran
This is in the pgadmin installation
instructions.
-Josh Berkus
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 565-7293
for law f
re-arrange the query slightly, you can turn it into a view.
The trick is to have the search_id as an output column rather than a
WHERE clause item in the sub-selects.
Have fun!
-Josh
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Comp
ely by the PostgreSQL
engine. Otherwise, such indexes are useless and may even slow down
queries.
Feedback? Answers?
-Josh
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
ateTimestamp
columns are last.
2.2. Set the defaults on those columns to current_user and
current-timestamp.
2.3. COPY all columns except those two. They should populate
according to the defaults (I hope).
-Josh
__AGLIO DATABASE SOLUTIONS___
Karel,
> .. well, I add it to my TODO for 7.3 (I plan rewrite several things
> in to_* functions).
How about a to_char function for INTERVAL? Please, oh please?
-Josh
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Co
ally, you can declare an array as a parameter of a function, but
that's it. You cannnot have array variables or retuurn types.
-Josh
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology
possible that any particular person went to more than one
school, add:
GROUP BY frienda, friendb
Simple, neh?
-Josh
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data man
1 beginner question per day.
But I can't figure out how to do this without subscribing to
pgsql-beginner and suffering a deluge of 100's of e-mails. Can anyone
suggest something?
-Josh Berkus
__AGLIO DATABASE SOLUTIONS___
Jo
he Mean
and the Median you can do all kinds of interesting statistical analysis.
-Josh Berkus
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions
1 OFFSET middlerec('pageviews')) med
GROUP BY site, median_views;
Where middlerec is a custom function that counts the records and returns
the middle one.
-Josh
__AGLIO DATABASE SOLUTIONS_______
Josh Berkus
Complete informat
Keith,
There are a number of posts and papers on tree structures , both in the
SQL list archives, and on Roberto Mello's resources at
techdocs.postgresql.org.
-Josh
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Com
Failing that, you can write your own and post it to
Techdocs.postgresql.org. That's what open source is all about!
-Josh
__AGLIO DATABASE SOLUTIONS_______
Josh Berkus
Complete information technology [EMAIL PROTECTED]
.
-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
e fly by a complex view.
So it depends on the number and complexity of the rules you will be
applying. If the rules are very complex and/or require procedural
logic, you probably want to generate and store records. Otherwise, no.
-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
uppose that this can be
dealt with, but until then does anyone have any suggestions?
-Josh Berkus
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solution
ourse, it is an Open-Source project, so if you hire your own
programmer, you can do anything you want.
-Josh Berkus
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data mana
Tom,
> I don't believe a single word of that explanation ... whatever is
> going
> on here, that ain't it. A new table is going to have a new OID, and
> so will its indexes; there is no way that Postgres will confuse it
> with
> the old one, even if bits of the old one were still hanging around
301 - 400 of 838 matches
Mail list logo
