Re: [SQL] join question - three tables, two with foreign keys to the first
On Fri, 14 Jun 2002 00:13:22 +1000, "Dmitri Colebatch" <[EMAIL PROTECTED]> wrote: >select emp.name, lv.from_date, lv.to_date, pay.amount >from employee as emp >left outer join employee_leave as lv on emp.id = lv.employee_id >left outer join employee_pay as pay on emp.id = pay.employee_id >where emp.id = 1 > >problem is that I dont get the null values I expect Dmitri, why do you expect nulls? SELECT ... FROM t1 LEFT JOIN t2 ON t1.col1 = t2.col2; returns nulls for the b-columns in the select list, if you have a row in t1 with a value t1.col1, that does not appear as col2 in any row of t2. In your example, however, you select a single row from emp with id = 1, and there are two rows in lv with employee_id = 1 and two rows in pay with employee_id = 1. And I doubt, you want to get the same row from lv more than once, only because there are multiple matches in pay, and vice versa. Add lv.id and pay.id to your SELECT to see what I mean. You may expect to get 4 rows, but what you get is not 2+2, but 2*2. Add some more rows and test again. Isn't there any relationship between lv and pay? I don't know if I understand your problem. Propably you want: SELECT emp.name, lv.from_date, lv.to_date, NULL AS amount FROM employee AS emp LEFT OUTER JOIN employee_leave AS lv ON emp.id = lv.employee_id WHERE emp.id = 1 UNION ALL SELECT emp.name, NULL AS from_date, NULL AS to_date, pay.amount FROM employee AS emp LEFT OUTER JOIN employee_pay AS pay ON emp.id = pay.employee_id WHERE emp.id = 1; or, if lv and pay are unrelated, why not two queries? SELECT emp.name, lv.from_date, lv.to_date FROM employee AS emp LEFT OUTER JOIN employee_leave AS lv ON emp.id = lv.employee_id WHERE emp.id = 1; SELECT emp.name, pay.amount FROM employee AS emp LEFT OUTER JOIN employee_pay AS pay ON emp.id = pay.employee_id WHERE emp.id = 1; HTH. Servus Manfred ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Please help me out on this insert error
On Thu, 13 Jun 2002 13:16:29 +0800, Vernon Wu
<[EMAIL PROTECTED]> wrote:
>
>Command:
>
>Insert into profile (userid, haveChildren)values('id98', 'No');
>
>Error:
>
>ERROR: Relation 'profile' does not have attribute 'havaChildren'
^
cut'n'paste error here ?
>Table:
> Table "profile"
>Column| Type | Modifiers
>--+---+--
> userid | character varying(25) | not null
> [...]
> haveChildren | character varying(20) | not null
Anyway, try
Insert into profile (userid, "haveChildren") values('id98', 'No');
Servus
Manfred
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] sql statement how to do ?
On Fri, 5 Jul 2002 09:03:38 + (UTC), [EMAIL PROTECTED] wrote: >INSERT INTO auftrag (SELECT * FROM auftrag where a_id = '12345'); > >The problem is, that the table auftrag has a primay key called pk_auftrag. >Do this I get an error regarding duplicate pk_auftrag. Is there a way to >spare pk_auftrag somehow ? Juerg, is a_id your primary key? My examples are based on this assumption, but if it is not, you still get the point, which is: You can't use SELECT * here. If you know the new a_id in advance: INSERT INTO auftrag (a_id, col2, col3, ...) SELECT '67890', col2, col3, ... FROM auftrag WHERE a_id = '12345'; If a_id is a serial or in any other way supplied automatically by a DEFAULT clause or a trigger: INSERT INTO auftrag (col2, col3, ...) SELECT col2, col3, ... FROM auftrag WHERE a_id = '12345'; HTH. Servus Manfred ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] [HACKERS] please help on query
[moving to pgsql-sql]
On Thu, 11 Jul 2002 17:22:14 +0200, "Luis Alberto Amigo Navarro"
<[EMAIL PROTECTED]> wrote:
>I can't improve performance on this query:
>
>SELECT
> supplier.name,
> supplier.address
>FROM
> supplier,
> nation
>WHERE
> supplier.suppkey IN(
> SELECT
> partsupp.suppkey
> FROM
> partsupp
> WHERE
> partsupp.partkey IN(
>SELECT
> part.partkey
>FROM
> part
>WHERE
> part.name like 'forest%'
> )
> AND partsupp.availqty>(
>SELECT
> 0.5*(sum(lineitem.quantity)::FLOAT)
>FROM
> lineitem
>WHERE
> lineitem.partkey=partsupp.partkey
> AND lineitem.suppkey=partsupp.partkey
^^^
suppkey ???
> AND lineitem.shipdate>=('1994-01-01')::DATE
> AND lineitem.shipdate<(('1994-01-01')::DATE+('1 year')::INTERVAL)::DATE
> )
> )
> AND supplier.nationkey=nation.nationkey
> AND nation.name='CANADA'
>ORDER BY
> supplier.name;
Luis,
rules of thumb: "Avoid subselects; use joins!" and "If you have to use
subselects, avoid IN, use EXISTS!"
Let's try. If partkey is unique in part, then
| FROM partsupp
| WHERE partsupp.partkey IN (SELECT part.partkey
can be replaced by
FROM partsupp ps, part p
WHERE ps.partkey = p.partkey
or
partsupp ps INNER JOIN part p
ON (ps.partkey = p.partkey AND p.name LIKE '...')
When we ignore "part" for now, your subselect boils down to
| SELECT partsupp.suppkey
| FROM partsupp
| WHERE partsupp.availqty > (
|SELECT 0.5*(sum(lineitem.quantity)::FLOAT)
|FROM lineitem
|WHERE lineitem.partkey=partsupp.partkey
| AND lineitem.suppkey=partsupp.suppkey
| AND lineitem.shipdate BETWEEN ... AND ...
| )
which can be rewritten to (untested)
SELECT ps.suppkey
FROM partsupp ps, lineitem li
WHERE li.partkey=ps.partkey
AND li.suppkey=ps.suppkey
AND lineitem.shipdate BETWEEN ... AND ...
GROUP BY ps.partkey, ps.suppkey
HAVING min(ps.availqty) > 0.5*(sum(lineitem.quantity)::FLOAT)
^^^
As all ps.availqty are equal in one group, you can as well
use max() or avg().
Now we have left only one IN:
| WHERE supplier.suppkey IN (
| SELECT partsupp.suppkey FROM partsupp WHERE )
Being to lazy to find out, if this can be rewritten to a join, let`s
apply rule 2 here:
WHERE EXISTS (
SELECT ... FROM partsupp ps
WHERE supplier.suppkey = ps.suppkey
AND )
HTH, but use with a grain of salt ...
>Sort (cost=2777810917708.17..2777810917708.17 rows=200 width=81)
BTW, how many years are these? :-)
Servus
Manfred
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] [HACKERS] please help on query
On Thu, 11 Jul 2002 19:40:46 +0200, "Luis Alberto Amigo Navarro" <[EMAIL PROTECTED]> wrote: >I've tried [reformatted to fit on one page] | SELECT supplier.name, supplier.address | FROM supplier, nation, lineitem You already found out that you do not need lineitem here. | WHERE EXISTS( | SELECT partsupp.suppkey | FROM partsupp,lineitem | WHERE |lineitem.partkey=partsupp.partkey |AND lineitem.suppkey=partsupp.partkey I still don't believe this suppkey=partkey |AND lineitem.shipdate [...] |AND EXISTS( SELECT part.partkey |FROM part WHERE part.name like 'forest%') This subselect gives either true or false, but in any case always the same result. You might want to add a condition AND part.partkey=partsupp.partkey Are you sure partkey is not unique? If it is unique you can replace this subselect by a join. | GROUP BY partsupp.partkey,partsupp.suppkey | HAVING min(availqty)>(0.5*(sum(lineitem.quantity)::FLOAT)) | ) | AND supplier.nationkey=nation.nationkey | AND nation.name='CANADA' | ORDER BY supplier.name; >as you said and something is wrong >Sort (cost=1141741215.35..1141741215.35 rows=240049 width=81) The cost is now only 1141741215.35 compared to 2777810917708.17 before; this is an improvement factor of more than 2000. So what's your problem? ;-) Servus Manfred ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] sql group by statement
On Fri, 13 Sep 2002 12:29:21 +0200, "Albrecht Berger" <[EMAIL PROTECTED]> wrote: >Table : >pk id val1 val2 > 112 3 > 212 4 > 321 1 > 410 5 > 521 8 > > >Needed Result : >pk id val1 val2 > 410 5 > 521 8 Albrecht, "DISTINCT ON eliminates rows that match on all the specified expressions, keeping only the first row of each set of duplicates." So the trick is to sort appropriately: SELECT DISTINCT on (id) pk, id, val1, val2 FROM yourtable ORDER BY id asc, val2 desc, pk desc; Servus Manfred ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Table alias in DELETE statements
On Fri, 13 Sep 2002 14:10:25 +0200, Hanno Wiegard <[EMAIL PROTECTED]> wrote: >So the question for me is whether it is possible >to use a table alias in a DELETE statement or not, e.g. >DELETE FROM foo f WHERE f.ID > 3000 (more complicated cases in reality Hanno, looks like you are out of luck here. PG 7.3: DELETE FROM [ ONLY ] table [ WHERE condition ] SQL92: DELETE FROM [ WHERE ] SQL99: DELETE FROM [ WHERE ] ::= [ ONLY ] which BTW makes "DELETE FROM mytable" invalid. This would have to be "DELETE FROM (mytable)". Is there something wrong with my copy of the standard? There has been a discussion on -hackers about extensions to the DELETE statement (DELETE [FROM] a FROM b, c WHERE ...). If this gets implemented in a future release, there's a certain chance for a table alias. >and I really need the alias because the SQL is generated automaically >by a tool)? ... and this tool works with what database? Servus Manfred ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] [GENERAL] Monitoring a Query
On Mon, 23 Sep 2002 11:06:19 -0400 (EDT), Bruce Momjian <[EMAIL PROTECTED]> wrote: >Tom Lane wrote: >> It would be nearly free to include the start time of the current >> transaction, because we already save that for use by now(). Is >> that good enough, or do we need start time of the current query? > >Current query, I am afraid. We could optimize it so single-query >transactions wouldn't need to call that again. This has been discussed before and I know I'm going to get flamed for this, but IMHO having now() (which is a synonym for CURRENT_TIMESTAMP) return the start time of the current transaction is a bug, or at least it is not conforming to the standard. SQL92 says in 6.8 : General Rules 1) The s CURRENT_DATE, CURRENT_TIME, and CURRENT_TIMESTAMP respectively return the current date, current time, and current timestamp [...] ^^^ 3) If an SQL-statement generally contains more than one reference ^ to one or more s, then all such ref- erences are effectively evaluated simultaneously. The time of evaluation of the during the execution ^^ of the SQL-statement is implementation-dependent. SQL99 says in 6.19 : 3) Let S be an that is not generally contained in a . All s that are generally contained, without an intervening whose subject routines do not include an SQL function, in s that are contained either in S without an intervening or in an contained in the of a trigger activated as a consequence of executing S, are effectively evaluated simultaneously. The time of evaluation of a during the execution of S and its activated triggers is implementation-dependent. I cannot say that I fully understand the second sentence (guess I have to read it for another 100 times), but "during the execution of S" seems to mean "not before the start and not after the end of S". What do you think? Servus Manfred ---(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] [GENERAL] CURRENT_TIMESTAMP
On Mon, 23 Sep 2002 13:05:42 -0400, Tom Lane <[EMAIL PROTECTED]> wrote: >Manfred Koizar <[EMAIL PROTECTED]> writes: >> This has been discussed before and I know I'm going to get flamed for >> this, but IMHO having now() (which is a synonym for CURRENT_TIMESTAMP) >> return the start time of the current transaction is a bug, or at least >> it is not conforming to the standard. > >As you say, it's been discussed before. Yes, and I hate to be annoying. >We concluded that the spec defines the behavior as >implementation-dependent, AFAICT the spec requires the returned value to meet two conditions. C1: If a statement contains more than one , they all have to return (maybe different formats of) the same value. C2: The returned value has to represent a point in time *during* the execution of the SQL-statement. The only thing an implementor is free to choose is which point in time "during the execution of the SQL-statement" is to be returned, i.e. a timestamp in the interval between the start of the statement and the first time when the value is needed. The current implementation only conforms to C1. >and therefore we can pretty much do what we want. Start time of the statement, ... of the transaction, ... of the session, ... of the postmaster, ... of the century? I understand that with subselects, functions, triggers, rules etc. it is not easy to implement the specification. If we can't do it now, we should at least add a todo and make clear in the documentation that CURRENT_DATE/TIME/TIMESTAMP is not SQL92/99 compliant. Servus Manfred ---(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] [GENERAL] CURRENT_TIMESTAMP
On Tue, 24 Sep 2002 11:19:12 +1000, Martijn van Oosterhout <[EMAIL PROTECTED]> wrote: >Well, what I would suggest is that when you wrap several statements into a >single transaction with begin/commit, the whole lot could be considered a >single statement (since they form an atomic transaction so in a sense they >are all executed simultaneously). The people who wrote the specification knew about transactions. If they had wanted what you describe above, they would have written: 3) If a transaction generally contains more than one reference to one or more s, then all such ref- erences are effectively evaluated simultaneously. The time of evaluation of the during the execution of the transaction is implementation-dependent. But they wrote "SQL-statement", not "transaction". >And hence Postgresql is perfectly compliant. I'm not so sure. >The current definition is, I would say, the most useful definition. Can you >give an example where your definition would be more useful? I did not write the standard, I'm only reading it. I have no problem with an implementation that deviates from the standard "because we know better". But we should users warn about this fact and not tell them it is compliant. Servus Manfred ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] [GENERAL] CURRENT_TIMESTAMP
On Mon, 23 Sep 2002 13:36:59 -0700, Josh Berkus <[EMAIL PROTECTED]> wrote: >I, for one, would judge that the start time of the statement is "during the >execution"; it would only NOT be "during the execution" if it was a value >*before* the start time of the statement. It's a semantic argument. Josh, you're right, I meant closed interval. >Further, we could not change that behaviour without breaking many people's >applications. > >Ideally, since we get this question a lot, that a compile-time or >execution-time switch to change the behavior of current_timestamp >contextually would be nice. Yes, GUC! >We just need someone who;s interested enough in >writing one. First we need someone who decyphers SQL99's wording. Servus Manfred ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] [GENERAL] CURRENT_TIMESTAMP
On Mon, 23 Sep 2002 16:55:48 -0400, Tom Lane <[EMAIL PROTECTED]> wrote: >Bruce Momjian <[EMAIL PROTECTED]> writes: >Here's an example: > >CREATE RULE foo AS ON INSERT TO mytable DO >( INSERT INTO log1 VALUES (... , now(), ...); > INSERT INTO log2 VALUES (... , now(), ...) ); > >I think it's important that these commands store the same timestamp in >both log tables (not to mention that any now() being stored into mytable >itself generate that same timestamp). I agree. SQL99 mentions this requirement for triggers and I think we can apply it to rules as well. Here is another example: BEGIN; INSERT INTO foo VALUES (..., CURRENT_TIMESTAMP, ...); -- wait a few seconds INSERT INTO foo VALUES (..., CURRENT_TIMESTAMP, ...); COMMIT; Please don't ask me, why I would want that, but the standard demands the timestamps to be different. >After all, it's only a minor implementation >detail that you chose to fire these logging operations via a rule and >not by client-side logic. No, it's fundamentally different whether you do something in one SQL-statment or per a sequence of statements. Servus Manfred ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] [GENERAL] CURRENT_TIMESTAMP
On Mon, 23 Sep 2002 23:35:13 -0400, Tom Lane <[EMAIL PROTECTED]> wrote: >If you want to change 'current_timestamp' to >conform to a rather debatable reading of the spec, [...] Well the spec may be debatable, but could you please explain why my reading of the spec is debatable. The spec says "during the execution of the SQL-statement". You know English is not my first language, but as far as I have learned "during" does not mean "at any time before". Servus Manfred ---(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] [GENERAL] CURRENT_TIMESTAMP
On Tue, 24 Sep 2002 17:56:51 -0400 (EDT), Bruce Momjian <[EMAIL PROTECTED]> wrote: >Can you run a test: > > BEGIN; > SELECT CURRENT_TIMESTAMP; > wait 5 seconds > SELECT CURRENT_TIMESTAMP; > >Are the two times the same? MS SQL 7: begin transaction insert into tst values (CURRENT_TIMESTAMP) -- wait insert into tst values (CURRENT_TIMESTAMP) commit select * from tst t --- 2002-09-24 09:49:58.777 2002-09-24 09:50:14.100 Interbase 6: SQL> select current_timestamp from rdb$database; = 2002-09-24 22:30:13. SQL> select current_timestamp from rdb$database; = 2002-09-24 22:30:18. SQL> commit; Servus Manfred ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Updating from select
On Wed, 02 Oct 2002 19:11:19 +0200, Thrasher <[EMAIL PROTECTED]> wrote: >UPDATE trans_log t SET t.cost = > (SELECT SUM(p.cost) FROM products_log p WHERE p.trans = t.id) Thrasher, try it without the table alias t: UPDATE trans_log SET cost = (SELECT SUM(p.cost) FROM products_log p WHERE p.trans = trans_log.id) Servus Manfred ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] join question
On Wed, 27 Nov 2002 09:37:07 -0800, Nathan Young <[EMAIL PROTECTED]> wrote: >OK, that works great, but I was told that I should avoid sub-selects when >possible for performance reasons. >> >> select member.memberId, member.name from member left outer join >> (select * from payment where yearPaid=2002) as a using (memberId) where >> yearPaid is null; Nathan, if you want a version without a subselect, try SELECT m.memberId, m.name FROM member AS m LEFT OUTER JOIN payment AS p ON p.yearPaid=2002 AND m.memberId=p.memberId WHERE p.memberId IS NULL; though I don't know whether it is faster. Servus Manfred ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] error in copy table from file
On Wed, 11 Dec 2002 18:40:48 +0100, "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote: >copy table from 'path/file' using delimiters ';' > >it returns the following: > >'RROR: copy: line 1, Bad float8 input format '-0.123 ^ This belongs to the end of the error message. Finding it here at the beginning of the line makes me think your file has DOS style end of lines (CR/LF). Convert them to Unix style (LF only). Servus Manfred ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] NULLL comparison in multiple column unique index
On Thu, 02 Jan 2003 17:19:52 -0600, "Brian Walker"
<[EMAIL PROTECTED]> wrote:
>create table test1 (name varchar(64),num1 int,num2 int);
>create unique index idx1 on test1(name,num1);
>insert into idx1 values ('row3',null,22);
>insert into idx1 values ('row3',null,23);
>
>This is allowed to happen. In Microsoft SQL the second insert will
>fail because of the unique index. This looks like in MSSQL for the
>unique index checks that NULL is equal to NULL so the unique check
>fails. In PostgreSQL NULL != NULL so the unique check passes because
>even though the name is the same the "num1" field is different..
This is just one more issue where Postgres is standard compliant and
MS is not. Your problem has been discussed before:
http://archives.postgresql.org/pgsql-novice/2002-09/msg00062.php
>Does anybody have any ideas on how I can work around this difference?
Also read the other messages of that thread; thus you should get an
idea of possible solutions.
You wrote:
>In PostgreSQL NULL != NULL
While accurate enough for the context you used it in, it is not
completely exact. NULL = NULL is neither TRUE nor FALSE, it is
UNKNOWN. The same holds for NULL != NULL. Try
SELECT * FROM anytable WHERE NULL = NULL;
SELECT * FROM anytable WHERE NULL != NULL;
to illustrate this; you get 0 rows in both cases, even in MSSQL ;-).
What's relevant here is that NULL = NULL doesn't evaluate to TRUE,
which explains why rows containing NULL cannot violate a unique
constraint.
Servus
Manfred
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] performance question
On Mon, 20 Jan 2003 12:40:34 +0100 (CET), "Moritz Lennert" <[EMAIL PROTECTED]> wrote: >I have a table with some 2.2 million rows on a Pentium4, 1.8GHz with 512 >MB RAM. >Some queries I launch take quite a long time, and I'm wondering whether >this is normal,or whether I can get better performance somehow. Moritz, we need more information. Please show us . your PG version . CREATE TABLE ... . indices . your query . EXPLAIN ANALYZE output . your settings, especially shared_buffers, sort_mem, random_page_cost, effective_cache_size >One question I asked myself is whether the use of char(2) is the best >option. The column (and most others in the table) contains codes that >designate different characteristics (for ex. in a column 'sex' one would >find '1'=male, '2'=female). char(2) needs 8 bytes, smallint only 2 bytes (unless followed by a column with 4 or 8 byte alignment). Instead of char(1) (8 bytes) you might want to use the Postgres specific type "char" (with the double quotes!) needing only 1 byte. Servus Manfred ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] performance question
On Mon, 20 Jan 2003 18:10:24 +0100 (CET), "Moritz Lennert" <[EMAIL PROTECTED]> wrote: >I'll try that, although I haven't changed any of the tuples since import >of the data (this is a static table...) Then I must have miscalculated something :-( What does VACUUM VERBOSE ANALYZE say? >> From what I've seen I think that the planner is right to choose a seq >> scan. 226 seconds for reading 120K pages (~ 1GB) is not very >> impressive, though. What kind of disk do you have? > >IDE, Samsung, 7200rpm > >> Is your disk heavily fragmented? > >It shouldn't be. > >> Did you enable DMA? > >No, should I ? Yes. Here is what I got on a P IV 2 GHz with a Seagate 7200rpm(?) disk: ~ # hdparm -t -T /dev/hda /dev/hda: Timing buffer-cache reads: 128 MB in 0.39 seconds =328.21 MB/sec Timing buffered disk reads: 64 MB in 2.49 seconds = 25.70 MB/sec vs. ~ # hdparm -t -T /dev/hda /dev/hda: Timing buffer-cache reads: 128 MB in 0.37 seconds =345.95 MB/sec Timing buffered disk reads: 64 MB in 23.38 seconds = 2.74 MB/sec ~ # l xx -rw-r--r--1 root root 1332104434 2003-01-20 19:04 xx ~ # time dd if=xx of=/dev/null bs=8k 162610+1 Records in 162610+1 Records out real0m48.665s user0m0.150s sys 0m1.690s ~ # hdparm -d 0 /dev/hda ~ # time dd if=xx of=/dev/null bs=8k 162610+1 Records in 162610+1 Records out real7m42.666s user0m0.270s sys 1m27.160s With DMA: More than 3000 pages / second Without DMA: ~ 350 pages / second Your throughput: ~ 530 pages / second >> recommend setting shared_buffers to something in the range [1000, >> 4000]. >> And one of my favorites: effective_cache_size = 4 > >I will have to increase /proc/sys/kernel/shmmax for that, or ? Maybe for shared_buffers. Certainly not for effective_cache_size. The latter does not consume resources, it's just a hint for the planner. Servus Manfred ---(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] Debugging postmaster to fix possible bug in Postgres? Followup
On Thu, 13 Feb 2003 17:50:22 -0500, Dmitry Tkach <[EMAIL PROTECTED]> wrote: >Then it looks like postgres behaviour is still not compliant, if I read it correctly, >because > >select x from mytable order by y; > >should be invalid according to this, but works just fine in postres. Yes, this is a Postgres extension to the standard. Your query is handled like SELECT x, y FROM mytable ORDER BY y with y being eliminated after the sort step. This also explains why the OP got the error message ERROR: Attribute t.y must be GROUPed or used in an aggregate function because the implicitely rewritten form would look like SELECT COUNT(*), y FROM t WHERE ... ORDER BY y >P.S. I think, this is a great feature actually (missed it a lot in informix), > so, I hope, you guys won't start discussing how to fix it :-) AFAICT there's no need to worry. Everyone agrees that this is a good feature and it does not break standard SQL queries. Servus Manfred ---(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] What's wrong with this group by clause?
[forwarding to -hackers]
On Tue, 4 Nov 2003 18:28:12 -0300, Franco Bruno Borghesi
<[EMAIL PROTECTED]> wrote:
>Below you can find a simplified example of a real case.
>I don't understand why I'm getting the "john" record twice.
ISTM you have found a Postgres 7.3 bug.
I get one john with
PostgreSQL 7.1.3 on i686-pc-cygwin, compiled by GCC 2.95.3-5
and
PostgreSQL 7.2 on i686-pc-linux-gnu, compiled by GCC 2.7.2.1
but two johns with
PostgreSQL 7.3.2 on i686-pc-linux-gnu, compiled by GCC 2.7.2.1
>/*EXAMPLE*/
>CREATE TABLE people
>(
> name TEXT
>);
>INSERT INTO people VALUES ('john');
>INSERT INTO people VALUES ('john');
>INSERT INTO people VALUES ('pete');
>INSERT INTO people VALUES ('pete');
>INSERT INTO people VALUES ('ernest');
>INSERT INTO people VALUES ('john');
>
>SELECT
> 0 AS field1,
> 0 AS field2,
> name
>FROM
> people
>GROUP BY
> field1,
> field2,
> name;
>
> field1 | field2 | name
>++
> 0 | 0 | john
> 0 | 0 | pete
> 0 | 0 | ernest
> 0 | 0 | john
>(4 rows)
Same for
SELECT 0 AS field1, 0 AS field2, name
FROM people
GROUP BY 1, 2, name;
Servus
Manfred
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] What's wrong with this group by clause?
On Thu, 13 Mar 2003 01:34:34 -0600, "Len Morgan" <[EMAIL PROTECTED]> wrote: >>GROUP BY > > field1, > > field2, > >name; >I think the problem is that you don't have a column to group on. field1, field2, and name are the grouping columns. >Try adding >SELECT ,count(*) so that there is an aggregate of some kind. You don't need an aggregate in a GROUP BY query. A SELECT ... GROUP BY without any aggregate behaves like SELECT DISTINCT. There's nothing wrong with it. Performance might be a different story. BTW, Franco's problem has been recognised as a bug and a patch has already been published (cf. Tom Lane's mail in this thread). Servus Manfred ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Let join syntax
On Mon, 16 Jun 2003 12:34:34 +0200, "Nicolas JOUANIN" <[EMAIL PROTECTED]> wrote: >from >coh x0 , cpy x1 ,bra x2 , >cur x3 ,tad x4 LEFT OUTER JOIN sec x5 on x5.thr_id=x4.thr_id AND >x5.usr_id=x0.usr_id AND [...] > >Unfortunatelly, postgres returns me the following error : > Error: ERROR: Relation "x0" does not exist Yes, because the LEFT OUTER JOIN only sees x4 and x5. I have not analysed all your join conditions, but FROM coh x0 INNER JOIN cpy x1 ON [...] INNER JOIN bra x2 ON [...] INNER JOIN cur x3 ON [...] INNER JOIN tad x4 ON [...] LEFT JOIN sec x5 ON [...] might work. If there is nothing to join x1, x2, x3, x4 on, you could try to put x5 to the front and use RIGHT JOINs ... Servus Manfred ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Let join syntax
On Mon, 16 Jun 2003 12:34:34 +0200, "Nicolas JOUANIN" <[EMAIL PROTECTED]> wrote: >from >coh x0 , cpy x1 ,bra x2 , >cur x3 ,tad x4 LEFT OUTER JOIN sec x5 on x5.thr_id=x4.thr_id AND >x5.usr_id=x0.usr_id AND x5.bra_id = x0.bra_id AND x5.dpr_id = x0.dpr_id, >dpr x6 where ((x0.cpy_id = x1.cpy_id ) AND (x0.bra_id >= x2.bra_id ) ) AND (x0.cur_id = x3.cur_id ) ) AND (x0.dpr_id >= x6.dpr_id ) ) AND (x2.tad_id = x4.tad_id ) ) AND (x5.thr_id >= x4.thr_id ) ) AND (x2.bra_id = x6.bra_id ) ) AND (x5.usr_id >= x0.usr_id ) ) AND (x5.bra_id = x0.bra_id ) ) AND (x5.dpr_id >= x0.dpr_id ) Nicolas, sometimes reformatting a query helps a lot: FROM coh x0 , cpy x1 ,bra x2 , cur x3 , tad x4 LEFT OUTER JOIN sec x5 ON x5.thr_id = x4.thr_id AND x5.usr_id = x0.usr_id AND x5.bra_id = x0.bra_id AND x5.dpr_id = x0.dpr_id, dpr x6 WHERE x0.cpy_id = x1.cpy_id AND x0.bra_id = x2.bra_id AND x0.cur_id = x3.cur_id AND x0.dpr_id = x6.dpr_id AND x2.tad_id = x4.tad_id AND x2.bra_id = x6.bra_id AND x5.thr_id = x4.thr_id AND x5.usr_id = x0.usr_id AND x5.bra_id = x0.bra_id AND x5.dpr_id = x0.dpr_id First note that the last four lines duplicate the ON conditions thus effectively turning the OUTER JOIN into an INNER JOIN. As I suspect that that was not your intention, simply omit those four conditions from the WHERE clause. Now inserting INNER JOIN where the syntax forces us to do so leads to (completely untested): FROM coh x0 INNER JOIN bra x2 ON x0.bra_id = x2.bra_id INNER JOIN tad x4 ON x2.tad_id = x4.tad_id LEFT JOIN sec x5 ON x5.thr_id = x4.thr_id AND x5.usr_id = x0.usr_id AND x5.bra_id = x0.bra_id AND x5.dpr_id = x0.dpr_id, cpy x1, cur x3, dpr x6 WHERE x0.cpy_id = x1.cpy_id AND x0.cur_id = x3.cur_id AND x0.dpr_id = x6.dpr_id AND x2.bra_id = x6.bra_id You might feel like replacing the remaining commas in the FROM clause and the corresponding WHERE conditions with semantically equivalent INNER JOINs. But this limits the freedom of the planner which may be a good or a bad thing... Servus Manfred ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] plpgsql doesn't coerce boolean expressions to boolean
On Mon, 08 Sep 2003 11:40:32 -0400, Tom Lane <[EMAIL PROTECTED]> wrote: >4. Use the parser's coerce_to_boolean procedure, so that nonbooleans > will be accepted in exactly the same cases where they'd be accepted > in a boolean-requiring SQL construct (such as CASE). (By default, > none are, so this isn't really different from #2. But people could > create casts to boolean to override this behavior in a controlled > fashion.) I vote for 4. And - being fully aware of similar proposals having failed miserably - I propose to proceed as follows: If the current behaviour is considered a bug, let i=4, else let i=5. In 7.i: Create a new GUC variable "plpgsql_strict_boolean" (silly name, I know) in the "VERSION/PLATFORM COMPATIBILITY" section of postgresql.conf. Make the new behaviour dependent on this variable. Default plpgsql_strict_boolean to false. Place a warning into the release notes and maybe into the plpgsql documentation. In 7.j, j>i: Change the default value of plpgsql_strict_boolean to true. Issue WARNINGs or NOTICEs as appropriate. Update documentation. In 7.k, k>j: Remove old behaviour and GUC variable. Update documentation. Servus Manfred ---(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] Q: select query
On 12 Sep 2003 10:58:45 -0700, [EMAIL PROTECTED] (G. Ralph Kuntz, MD) wrote: >I would like to select the second and subsequent rows where the first >column is the same: > > 1 b > 1 c > 3 f > >in other words, all but the first row of a group. all = SELECT * FROM t; but = EXCEPT the first row of a group = SELECT i, min(x) FROM t GROUP BY i; or (if there are more columns) SELECT DISTINCT ON(i) * FROM t ORDER BY i, x; Putting it together: SELECT i, x FROM t EXCEPT (SELECT i, min(x) FROM t GROUP BY i); or SELECT * FROM t EXCEPT (SELECT DISTINCT ON(i) ...); Servus Manfred ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Unique Constraint Based on Date Range
On Sat, 20 Sep 2003 18:55:34 -0400, Kevin Houle <[EMAIL PROTECTED]> wrote: > SELECT INTO result * FROM table_rates WHERE > effective_date >= NEW.effective_date AND > expiry_date <= NEW.expiry_date AND > cost = NEW.cost; > IF FOUND THEN >RAISE EXCEPTION ''record overlaps with existing record''; > END IF; This only catches complete inclusion of an old range in the new one. newb-e old b--e Try WHERE effective_date < NEW.expiry_date AND expiry_date > NEW.effective_date which also detects newbe old b---e old b--e old be Servus Manfred ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
