[SQL] a question to developers
Hello Developers what had drove you to use CSTRING type for input argument of postgres-type input convertion functions (in general) and for output of postgres-type output conversion functions ? thanx ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] How to know column constraints via system catalog tables
> > Hi, > > I need to query each column's constraint and name of a table in > postgreSQL v7.3.4 with a single SQL query but don't know how. Would > appreciate any pointers! > > Thank you. > > Regards, > Damon > select relname,conname from pg_constraint,pg_class where pg_class.oid=conrelid ; could be start. Regards, Christoph ---(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] pg 7.4.rc1, Range query performance
--- Bruce Momjian <[EMAIL PROTECTED]> wrote: > Tom Lane wrote: > > Bruce Momjian <[EMAIL PROTECTED]> writes: > > > Sorry there isn't a simple solution. > > > > But there is: make an index with the column order (b,c,a). > > Oh, yea, right. If he puts the columns he is doing a straight equals > comparison first, the 'a' comparison will work fine. Great. > Yes, I think Stephan Szabo was trying to convince me of something like that for quite sometime; it was not clear to me then. I'll give it a try. I still think "CREATE [CLUSTERED] INDEX" (or whatever) is an important feature and should be implemented in pgSql. Locking large (or huge) tables for prolonged periods of time may not be acceptable in many setups. Thanks __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] curly braces to group outer joins in queries from OpenOffice.org?
Hi!
I just tried using openoffice to create a SQL query, but it failed when I
tried using left outer joins. The contructed query from OO contains curly
braces to group things. I've never seen this construct before. Is it OO
doing bad things, or does postgresql not understand it? It's on pg-7.3.4.
Perhaps 7.4 behaves differently?
SELECT "personer"."foretag", "personer"."fnamn", "personer"."enamn",
"befattningnamn"."namn"
FROM { OJ "public"."befattning" "befattning"
LEFT OUTER JOIN "public"."personer" "personer" ON "befattning"."personid"
= "personer"."personid" },
"public"."kommentar" "kommentar", "public"."personer" "personer",
"public"."befattning" "befattning",
"public"."befattningnamn" "befattningnamn" WHERE 0 = 1
ERROR: parser: parse error at or near "{" at character 100
(The WHERE 0 = 1 seems to be OO's way of checking the query syntax.)
Thanks
Palle
---(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] Getting the row_count value outside of a function
> > I can get the row_count value inside of a function, > but I can´t get it outside of a function. > > How can I do this? > > Regards, > Enio > Ok, you're outside of a function, but there must be another environment which isn't nirvana. Where are you? Regards, Christoph ---(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] transaction processing after error in statement
Holger Jakobs wrote: Why is that "funny behaviour" for you? By putting the statements into a transaction block you told the data management system "I want this group of statements to be atomic". Atomic means all or nothing. It might not be exactly what you intended to say, and you have a point if you conclude that PostgreSQL is limited because it doesn't let you say anything in between all or nothing. But after all, thus far it is only doing what you asked for. It is "funny behaviour", because I expect those operations of the transaction, which executed successfully, to be performed in an atomic way. It is obvious that I cannot expect an operation which reported an error to have any effect. "Atomic" means that all operations (whether successful or not) will be carried out all together or none of them - but only the successful ones will have had an effect. As long as we talk in an SQL context, can you please stick to SQL terms? I don't know exactly what you mean with "operation". If for example the statement DELETE FROM order_line WHERE ol_ordernum = 4711; has 12 matching rows in order_line, is an operation the removal of one single order line or do the actions performed by the triggers fired due to their removal count as separate operations for you? And if there is one that cannot be deleted because a row in another table with a foreign key references it, do you delete none of them or the remaining 11? And if you decide to delete none, how do you magically undo the work of the BEFORE triggers if you hit the foreign key after successfully processing 5 rows? Is there an SQL return code for "partial success"? The question about "partial success" is the important part here. Imagine a stored procedure in PL/pgSQL consisting of two INSERTs. One fails with a duplicate key error, the other one succeeds. The language lacks for technical reasons an exception handling mechanism, so you have to define if the other statement or nothing of the procedure succeeds, because you have no chance to report "partial success", there is no return code defined for that. Again: Why not make a difference between "commit" and "rollback" in a transaction in this case? Why not let the user decide which parts should be commited? The practical reason is that programming would become a lot more convenient. (if there is a practical reason it does not necessarily need a technical reason, I believe.) Nobody said that it should not be possible. But you have to dig a little deeper and make a bit more of a complete proposal for this, covering the different possible failure reasons, definitions how exactly to react in case of statements affecting multiple rows, related triggers and so on and so forth. "Make a difference between commit and rollback" is way too fuzzy here. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] curly braces to group outer joins in queries from OpenOffice.org?
Palle Girgensohn <[EMAIL PROTECTED]> writes: > I just tried using openoffice to create a SQL query, but it failed when I > tried using left outer joins. The contructed query from OO contains curly > braces to group things. I've never seen this construct before. The SQL standard has never heard of it, either. Parentheses would be correct here. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] How to know column constraints via system catalog tables
>
> Thanks for the reply but my main problem is I'm trying to find the primary column(s)
> and the data type of these column in a table. pg_constraint's conkey is a int2[]
> field. So if i have a table called 'films' (taken from postgreSQL doc) that has two
> primary keys (composite primary key) then the query below gives me {1,2} for column
> 1 and column 2.
>
> select x.conkey from
> pg_constraint x, pg_class a
> where a.relfilenode=x.conrelid
> and a.relname='films'
> and x.contype='p';
>
> I cannot match the above with the next query to get a single query statement that
> provides the primary column's name and its data type.
>
> SELECT distinct c.attname FROM
> pg_class a, pg_constraint b,
> pg_attribute c
> WHERE a.relfilenode=c.attrelid
> AND c.attnum>0
> AND a.relname='films';
>
> Thanks for spending the time to answer my query!
>
>
First, do you know the psql -E option which shows you the SQL behind the \d outputs.
You have probably used this films table (there is more than one in the doc):
CREATE TABLE films (
codeCHAR(5),
title VARCHAR(40),
did DECIMAL(3),
date_prod DATE,
kindVARCHAR(10),
len INTERVAL HOUR TO MINUTE,
CONSTRAINT code_title PRIMARY KEY(code,title)
);
Based on a query I'm using to retrieve column information
(improved by Tom Lane's help), I think I've found something useful. Try
SELECT
upper(u.usename) AS TBL_OWNER, upper(c.relname) AS TBL_NAME,
upper(a.attname) AS COL_NAME, a.atttypid AS COL_TYPE,
int4larger(a.attlen, a.atttypmod - 4) AS COL_LENGTH,
CASE WHEN a.attnotnull=true THEN 0 ELSE 1 END AS COL_NULL, a.attnum AS COL_SEQ,
CASE WHEN EXISTS(SELECT adsrc FROM pg_attrdef d
WHERE d.adrelid = a.attrelid and d.adnum = a.attnum) THEN
1
ELSE
0
END AS COL_DEFAULT
from pg_attribute a, pg_constraint x,
pg_class c left join pg_user u on (u.usesysid = c.relowner)
where c.oid = a.attrelid and not (c.relname ~* 'pg_') and
c.relkind = 'r' and a.attnum > 0 and
c.relfilenode=x.conrelid and x.contype='p' and c.relname ='films' and
(a.attnum = x.conkey[1] or a.attnum = x.conkey[2]) ;
tbl_owner | tbl_name | col_name | col_type | col_length | col_null | col_seq |
col_default
---+--+--+--++--+-+-
CH| FILMS| CODE | 1042 | 5 |0 | 1 |
0
CH| FILMS| TITLE| 1043 | 40 |0 | 2 |
0
(2 rows)
You'll probably want to get rid of some parts (e.g. the upper case conversion),
but basically it's what you were looking for. Right?
Regards, Christoph
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] curly braces to group outer joins in queries from
What about the `OJ'?
FROM ( OJ table alias left join table2 alias2 )
OJ seems plain wrong, right?
Here's their code snippet (from
oo_1.1_src/dbaccess/source/ui/querydesign/QueryDesignView.cxx):
if(aJoin.getLength())
{
::rtl::OUString aStr = ::rtl::OUString::createFromAscii("{ OJ ");
aStr += aJoin;
aStr += ::rtl::OUString::createFromAscii(" },");
aTableListStr += aStr;
}
You mean simple parathesis, no `OJ' would be OK?
like:
@@ -1141,9 +1141,9 @@
if(aJoin.getLength())
{
- ::rtl::OUString aStr = ::rtl::OUString::createFromAscii("{ OJ ");
+ ::rtl::OUString aStr = ::rtl::OUString::createFromAscii("( ");
aStr += aJoin;
- aStr += ::rtl::OUString::createFromAscii(" },");
+ aStr += ::rtl::OUString::createFromAscii(" ),");
aTableListStr += aStr;
}
Thanks for the prompt reply, as usual!
Regards,
Palle
--On tisdag, november 11, 2003 09.39.52 -0500 Tom Lane <[EMAIL PROTECTED]>
wrote:
Palle Girgensohn <[EMAIL PROTECTED]> writes:
I just tried using openoffice to create a SQL query, but it failed when
I tried using left outer joins. The contructed query from OO contains
curly braces to group things. I've never seen this construct before.
The SQL standard has never heard of it, either. Parentheses would be
correct here.
regards, tom lane
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [SQL] curly braces to group outer joins in queries from OpenOffice.org?
Palle Girgensohn <[EMAIL PROTECTED]> writes:
> Here's their code snippet (from
> oo_1.1_src/dbaccess/source/ui/querydesign/QueryDesignView.cxx):
> if(aJoin.getLength())
> {
> ::rtl::OUString aStr = ::rtl::OUString::createFromAscii("{ OJ ");
> aStr += aJoin;
> aStr += ::rtl::OUString::createFromAscii(" },");
> aTableListStr += aStr;
> }
Now that I look at it, I bet that they are expecting the ODBC driver to
translate "{ OJ ... }" into the appropriate syntax for the database
(either SQL standard or not, depending on the DB :-().
There are a number of ODBC brace-constructs that our driver knows how to
translate, but I don't think that's one of them. You should bug the
ODBC guys about it.
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] curly braces to group outer joins in queries from
Only, I set up postgreql to connect using JDBC... :(
So, the bug is that OO expects an ODBC connection, and I use JDBC... Seems
logical. I'll go ask the ODBC guys. Thanks for you help,
/Palle
--On tisdag, november 11, 2003 11.28.57 -0500 Tom Lane <[EMAIL PROTECTED]>
wrote:
Palle Girgensohn <[EMAIL PROTECTED]> writes:
Here's their code snippet (from
oo_1.1_src/dbaccess/source/ui/querydesign/QueryDesignView.cxx):
if(aJoin.getLength())
{
::rtl::OUString aStr = ::rtl::OUString::createFromAscii("{ OJ ");
aStr += aJoin;
aStr += ::rtl::OUString::createFromAscii(" },");
aTableListStr += aStr;
}
Now that I look at it, I bet that they are expecting the ODBC driver to
translate "{ OJ ... }" into the appropriate syntax for the database
(either SQL standard or not, depending on the DB :-().
There are a number of ODBC brace-constructs that our driver knows how to
translate, but I don't think that's one of them. You should bug the
ODBC guys about it.
regards, tom lane
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] curly braces to group outer joins in queries from OpenOffice.org?
Palle Girgensohn <[EMAIL PROTECTED]> writes: > Only, I set up postgreql to connect using JDBC... :( > So, the bug is that OO expects an ODBC connection, and I use JDBC... Seems > logical. I'll go ask the ODBC guys. Thanks for you help, I do not know if the JDBC spec includes ODBC-style brace constructs or not; if it does, then there's an omission in our JDBC driver. Anyone know? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] Bit strings
Hello, For a data warehousing project I will have to implement bitmaps. I would perfer to stay away from C, Java, etc., and would like to use PostgreSQL instead. I have a question about bit string type: does the time it takes to do the & or | of two bit strings run in constant time (as it does in C)? Thanks, Yasir ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] question to developers
Hello Developers what had drove you to use CSTRING type for input argument of postgres-type input convertion functions (in general) and for output of postgres-type output conversion functions ? thanx ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] transaction processing after error in statement
> > Why is that "funny behaviour" for you? By putting the statements into > a transaction block you told the data management system "I want this > group of statements to be atomic". Atomic means all or nothing. It > might not be exactly what you intended to say, and you have a point > if you conclude that PostgreSQL is limited because it doesn't let you > say anything in between all or nothing. But after all, thus far it is > only doing what you asked for. > It is "funny behaviour", because I expect those operations of the transaction, which executed successfully, to be performed in an atomic way. It is obvious that I cannot expect an operation which reported an error to have any effect. "Atomic" means that all operations (whether successful or not) will be carried out all together or none of them - but only the successful ones will have had an effect. Again: Why not make a difference between "commit" and "rollback" in a transaction in this case? Why not let the user decide which parts should be commited? The practical reason is that programming would become a lot more convenient. (if there is a practical reason it does not necessarily need a technical reason, I believe.) Sincerely, Holger -- [EMAIL PROTECTED], Bergisch Gladbach, Germany Telefon (0 22 02) 5 99 91 oder (01 77) 7 92 24 66 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] transaction processing after error in statement
On 11 Nov, Jan Wieck wrote: > As long as we talk in an SQL context, can you please stick to SQL > terms? I don't know exactly what you mean with "operation". If for > example the statement > > DELETE FROM order_line WHERE ol_ordernum = 4711; > > has 12 matching rows in order_line, is an operation the removal of one > single order line or do the actions performed by the triggers fired > due to their removal count as separate operations for you? And if > there is one that cannot be deleted because a row in another table > with a foreign key references it, do you delete none of them or the > remaining 11? And if you decide to delete none, how do you magically > undo the work of the BEFORE triggers if you hit the foreign key after > successfully processing 5 rows? Is there an SQL return code for > "partial success"? OK, let's say "statement" instead of "operation". No, there is no partial success. Either a statement delivers an "OK" or it doesn't. Actually, you will have to undo anything the statement did before the first error occurs. This may mean that you need some kind of savepoint. If so, the necessity to implent this shows and should be given a high priority. I don't know how the other RDMBS do it, but they do. I am talking from the perspective of an RDBMS user, not as an implementor. Calling a procedure is a statement as well, and it includes all other procedures called from this procedure. So the statement level is always the statements that were carried out directly in the transaction. If anything within one statement fails, the statement was not carried out and must not have any effect. It is not important whether the procedure was fired by a trigger or called by another procedure. Are there any Open Source RDBMS which behave like Oracle, Access and Allbase? If so, one might look into their code to find out how they have implented it. Coming back to Standards, here is a quote from the ANSI document: 4.28 SQL-transactions The execution of a may be initiated implicitly by an implementation when it detects unrecoverable errors. When such an error occurs, an exception condition is raised: transaction rollback with an implementation-defined subclass code. This means that a rollback does not have to be initiated if an unrecoverable error occurs, it only _may_ happen. Since it is impractical, it should not. AN EXAMPLE: Let's have two tables, employees and doctors in a hospital. create table emp ( empno integer primary key, name varchar(40) ); create table doctor ( empno integer primary key references emp, beepernumber integer unique ); Now let a user enter the data of a doctor. First the data of the employee part are sent to the database: insert into emp values (1, 'Fred'); --> success Second the doctor-special data are sent to the database: insert into doctor values (1, 34); -->error, beepernumber already present, unique key violation Since there was an error, we let the user key in a different beeper number for the doctor and send the data to the database: insert into doctor (1, 45); -->should be successful (if 45 is not already there) and it _is_ in Allbase, Oracle, Access -->Postgres tells you something about and *ABORT* state We commit the transaction: commit work; Effect in all other databases: a successfully entered doctor Effect in PostgreSQL: nothing! To get the same effect in PostgreSQL, we would have to rollback (or commit, no difference) the transaction after the attempt of inserting the non-unique beeper number and then re-insert all data into all tables. WHY? It only makes programming more complex, thus error-prone. I would appreciate if most of the difficult tasks could be done within the database system - that's what it's there fore. The first reason is that the implementors usually are far more advanced programmers than the programmers of applications. The second reason is that this isolates the tricky programming from the already complex logic of the application. Additionally, whether one might personally think that this behaviour is important or not, it is what most commercial RDBMS do. So it would make porting of applications from e. g. Oracle to PostgreSQL dramatically easier. Until now, this has been one of the pitfalls. That's why I brought this up in the first place. Sincerely, Holger -- [EMAIL PROTECTED], Bergisch Gladbach, Germany Telefon (0 22 02) 5 99 91 oder (01 77) 7 92 24 66 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] 7.3 how remove password valid until
Bruce Momjian wrote: > I think you have to specify the data as 'infinity'. > > test=> CREATE USER x WITH VALID UNTIL '2005-01-01'; > CREATE USER > test=> ALTER USER x WITH VALID UNTIL 'infinity'; > ALTER USER > Thank You Alban ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Is there a more elegant way to write this query?...
Hi-
I'm suffering from a performance problem, but when I look at my query, I'm
not convinced that there isn't a better way to handle this in SQL. -So I'm
seeking advice here before I go to the performance list.
I have three tables- case, actor and actor_case_assignment.
As the names imply, actor_case_assignment contains records that assign an
actor to a case. Actors such as attorneys or judges may have many cases,
while the average actor (we hope) only has one.
What I'm trying to do is link these tables to get back a single row per
actor that shows the actor's name, the number of cases that actor is
assigned to, and if they only have one case, I want the number for that
case. This means I have to do some grouping to get the case count, but I'm
then forced to use an aggregate function like max on the other fields. I
hope there's a better way. Any suggestions?
Here's what I'm using right now:
select
actor.actor_id,
max(actor.actor_full_name),
max(case_data.case_public_id),
max(case_data.case_id),
count(case_data.case_id) as case_count
from
actor,
actor_case_assignment,
case_data
where
actor.actor_full_name_uppercase like upper('martin%')
and actor.actor_id = actor_case_assignment.actor_id
and case_data.case_id = actor_case_assignment.case_id
group by
actor.actor_id
order by
max(actor.actor_full_name),
case_count desc,
limit
1000;
Thanks!
-Nick
-
Nick Fankhauser
[EMAIL PROTECTED] Phone 1.765.965.7363 Fax 1.765.962.9788
doxpop - Court records at your fingertips - http://www.doxpop.com/
---(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] Is there a more elegant way to write this query?...
Hi Eric- Thanks for your suggestion. > An explain analyze would help. I'll do that (and move the question to the performance list) when I get to the performance question, but at this point, I'm just seeking some help in looking at this from a different angle- I couldn't figure out how to achieve the result I was after without using max(), which felt like a kludge. Your solution gave me the start I needed. -Nick > How about: > select > actor.actor_full_name, > actor.actor_id, > s1.ctCases, > s1.case_id, > case_data.case_public_id > from > actor inner join ( select actor_id, count(*) as ctCases, > max(case_id) > as case_id > from > actor_case_assignment group by actor_id) as s1 > on (actor.actor_id = s1.actor_id) >left outer join case_data using > (s1.case_id=case_data.case_id) > limit 1000; ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] Quota query with decent performance?
Hello, I'm researching how "quota queries" (a term used by Fabian Pascal) may be performed in various DBMSes with acceptable performance: http://troels.arvin.dk/db/rdbms/#select-limit-simple-note An example of a quota query could be to get the top-3 youngest people from a collection of people. The complicated part is that such a query might return more than 3 rows in some tie situations. In MSSQL and DB2 there are very efficient facilities for such queries, but I can't find any well-performing declarative methods for PostgreSQL. I have tried a couple of different strategies, and I currently get the best results from a correlated subquery like SELECT * FROM person AS px WHERE ( SELECT COUNT(*) FROM person AS py WHERE py.age < px.age ) < 3; When my base table has 4000 rows, my query takes 27 seconds in PostgreSQL 7.2.3 (PIII 1000MHz) which is clearly unacceptable, especially comparing to the same query in DB2 which only takes 1.4 seconds (on the same server) - or to this non-standard-SQL DB2-query which only takes 0.02 seconds to calculate the same result: SELECT * FROM ( SELECT firstname,age,RANK() OVER (ORDER BY age ASC) AS rank FROM person ) AS foo WHERE rank<=3; Test-files with table definitions and randomly generated rows: http://troels.arvin.dk/db/tests/quota.1/ Any suggestions on how to perform fast "quota queries" in PostgreSQL? -- Greetings from Troels Arvin, Copenhagen, Denmark ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Quota query with decent performance?
Troels, Thank you for contacting us before publishing your results. Please ignore any list-trolls who criticize your methodology; there are a few cranks on every list. The important thing is your contacted us. > In MSSQL and DB2 there are very efficient facilities for such queries, but > I can't find any well-performing declarative methods for PostgreSQL. I > have tried a couple of different strategies, and I currently get the best > results from a correlated subquery like > > SELECT * FROM person AS px > WHERE ( > SELECT COUNT(*) > FROM person AS py > WHERE py.age < px.age > ) < 3; > > When my base table has 4000 rows, my query takes 27 seconds in PostgreSQL > 7.2.3 (PIII 1000MHz) which is clearly unacceptable, Well, first off 7.2.3 is a two-year old version. There have been performance improvements specificially on these sorts of issues since then. Please use at least 7.3.4, and we would prefer that you use 7.4RC2, which is available now. 7.4, in particular, introduces significant improvements in "group by" queries. If for some reason you have to compare against this old version, please be fair to us and note somewhere that you were using an old version of PostgreSQL. Second, the query you post is one "SQL Standard" way, which is good for portability but not for speed. Frankly, I'm not convinced that it's even the best SQL standard way. On the other databases, you seem happy to use non-SQL-standard syntax, so let me give you one such solution in PostgreSQL: SELECT * FROM person WHERE person.age >= (SELECT p2.age from person p2 order by p2.age DESC LIMIT 1 OFFSET 2) also try: SELECT * FROM person, (SELECT p2.age from person p2 order by p2.age DESC LIMIT 1 OFFSET 2) as prank WHERE person.age >= prank.age This should give you all of the rows whose ages are in the top 3 ranks much faster. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Quota query with decent performance?
maybe: select * from person where age <= (select age from person order by age limit 1 offset 2); 7.20 msec assuming it does what you want. On Tue, 11 Nov 2003, Troels Arvin wrote: > An example of a quota query could be to get the top-3 youngest people from > a collection of people. The complicated part is that such a query might > return more than 3 rows in some tie situations. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Quota query with decent performance?
Josh Berkus <[EMAIL PROTECTED]> writes: > Second, the query you post is one "SQL Standard" way, which is good for > portability but not for speed. Frankly, I'm not convinced that it's even the > best SQL standard way. On the other databases, you seem happy to use > non-SQL-standard syntax, so let me give you one such solution in PostgreSQL: > [snip] I don't know of any very good solution in bog-standard SQL either. Aside from the LIMIT-based solution that Josh offered, I recall that Oleg Bartunov and Teodor Sigaev had some ideas about top-N-aggregate solutions. We didn't accept those into the main distribution (yet) but if you dig in the PG list archives I think there is working code available. Try searching for "partial sorting". regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
