Re: [GENERAL] Difference between ON and WHERE in JOINs
Le 19/09/2012 02:47, David Johnston a écrit : There is no difference in your example. Conceptually though I suggest using only table-table conditions in an ON clause and placing any table-value conditions into the where. This is how I use it usually. The main time you get differences is when you use OUTER JOIN constructions since the order of filtering can affect the final result. With an inner join the order of evaluation doesn't matter since all valid results will have a record from both sides of the join. Ok, I didn't know what the trigger was : outer joins, ok. I have this query working : select profil,count(og.name) from ldap l left join uidinoldgroups ug on l.uid=ug.uid left join oldgroups og on og.id=ug.idgroupe and og.rne='0410030k' and og.type='g' where l.profilgeneral='P' and l.rne='0410030k' group by l.profil But if I put : and og.rne='0410030k' and og.type='g' in the where part (what you suggested and what I did naturally), I get 0 results which is quite confusing (because it's an outer join). Also, if I replace these with full outer joins, I still get 0 results. With my data, if I replace og.type='g' with og.type='m' I get MOST OF my resultats back (those where the count() returns more than zero). I know this is specific to my data but I really don't get the behaviour. Thanks for your help, JC -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Difference between ON and WHERE in JOINs
Le 19/09/2012 17:08, Merlin Moncure a écrit : SELECT * FROM foo LEFT JOIN BAR ON foo.id = bar.id AND bar.col = 'something'; The difference here is that the filtering is now happening at join time where the left join semantics are playing: always return foo and return bar rows if and only if the join condition is met. Ok, as a rule of thumb, should I put in the where clause only the conditions related to foo and inner joins ? All other (outer) conditions should then go on their respective 'ON' clauses ? JC -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Difference between ON and WHERE in JOINs
I'm looking for an article that explains the difference between these constructs IN POSTGRESQL (the rules seem to differ from one DB to another) : SELECT A.* FROM A JOIN B ON a.id=b.id AND A.somefield='somevalue' and SELECT A.* FROM A JOIN B ON a.id=b.id WHERE A.somefield='somevalue' I have noticed big differences though I don't know the rules and I've been bitten several times recently. Time to learn. Thanks, JC -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Fwd: [GENERAL] URGENT ! Nouveau virus
Toujours dans la série Merci Microsoft (j'ai déjà prévenu à propos des multiples trous de sécurité dans Outlook et Outlook express). Cette menace est très sérieuse. === Forward === From: Denis Bucher [EMAIL PROTECTED] To: [EMAIL PROTECTED] Date: Tuesday, September 18, 2001, 7:07:34 PM Subject: [GENERAL] URGENT ! Nouveau virus Hello ! TRES URGENT : N'utilisez PLUS DU TOUT Microsoft Internet Explorer pour l'instant (tant que Microsoft n'aura pas développé un correctif et tant que vous n'aurez pas appliqué ce correctif), à la place vous pouvez utiliser Opera (http://www.opera.com). Infos plus complètes : http://slashdot.org/articles/01/09/18/151203.shtml En effet, un nouveau virus infecte des sites web, et à cause d'un trou de sécurité (ENCORE) dans le browser de Microsoft, si vous visitez un site qui a été touché votre ordinateur sera infecté par de TRES NOMBREUX VIRUS D'UN SEUL COUP. Je pense que d'ici un jour ou deux les antivirus pourront combattre cela et qu'on aura plus d'informations, mais en attendant FERMEZ INTERNET EXPLORER ET NE L'OUVREZ PLUS Bonne soirée ! Denis Bucher === -- Jean-Christophe Boggio [EMAIL PROTECTED] -o) Independant Consultant and Developer /\\ Delphi, Linux, Perl, PostgreSQL _\_V ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re[2]: [GENERAL] Unexplained behaviour
Hi Tom, Ref : Thursday, March 29, 2001 1:41:49 AM TL Your trigger will obviously fail to generate unique keys if two TL transactions are running concurrently, since any two transactions TL started in the same minute will generate the same initial key, TL and if they are running concurrently then neither will see the other's TL entry in the table. Given that, I don't know why you're bothering. TL Why don't you use a sequence object to generate the unique keys? I thought I could not do something like : a table with sequence with before-insert-trigger that would define a field based on the already-assigned-sequence number. I tried, it works perfect and no more errors. Again, again, again, many thanks. TL (The internal queries of the trigger will appear in the log only when TL compiled, ie, first time through that line in a particular backend.) Did not know that. Precious information ! -- Jean-Christophe Boggio [EMAIL PROTECTED] Independant Consultant and Developer Delphi, Linux, Perl, PostgreSQL ---(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
[GENERAL] Where's that doc on plans ?
A few days ago I read a very interesting doc about postgres' query planner, how to read an EXPLAIN plan, how is a cost calculated, etc. but I can't find it anymore. Anyone has an idea ? Thanks in advance. -- Jean-Christophe Boggio [EMAIL PROTECTED] Independant Consultant and Developer Delphi, Linux, Perl, PostgreSQL ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Request regarding logs
When you log pg's activity with statistics enabled you can get this kind of report : StartTransactionCommand query: select * from identity where login='cat'; ProcessQuery ! system usage stats: ! 0.012632 elapsed 0.01 user 0.00 system sec ! [0.03 user 0.01 sys total] ! 0/0 [0/0] filesystem blocks in/out ! 49/55 [309/285] page faults/reclaims, 0 [0] swaps ! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent ! 0/0 [0/0] voluntary/involuntary context switches ! postgres usage stats: ! Shared blocks: 21 read, 0 written, buffer hit rate = 93.27% ! Local blocks: 0 read, 0 written, buffer hit rate = 0.00% ! Direct blocks: 0 read, 0 written CommitTransactionCommand The problem is when you have many queries every second, you get all the lines mixed up : several queries can start before the first gives the result. And so the stats are useless in production. Would it be possible to have a unique identifier in the "query:" line and another one at the beginning of the stats ? Maybe the backend's PID ? The goal is to write some code to analyze the stats and : - calculate global stats - point out the bad queries (and produce the explain plan with the report) I posted nearly the same message a few days ago and was surprised not seeing any answer. I wonder if the question is too stupid and if so please, someone enlighten me ? Maybe it was meaningless, this is why I tried to say things differently. My apologies for the ~repost -- Jean-Christophe Boggio [EMAIL PROTECTED] Independant Consultant and Developer Delphi, Linux, Perl, PostgreSQL ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] INSERT ... RETURNING as Oracle
Just wanted to add that PHP has a GETLASTOID function that will work (after an insert) even if you don't use sequences/serials. -- Jean-Christophe Boggio [EMAIL PROTECTED] Independant Consultant and Developer Delphi, Linux, Perl, PostgreSQL ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Error creating tables.
Salut Aristide, Le Thursday, October 12, 2000 à 7:23:18 PM, tu me disais: AA create table test (id serial, name varchar(10) ) ; AA returns: AA NOTICE: CREATE TABLE will create implicit sequence 'test_id_seq' for SERIAL column 'test.id' AA NOTICE: CREATE TABLE/UNIQUE will create implicit index 'test_id_key' for table 'test' AA ERROR: cannot create test_id_seq Maybe the test_id_seq sequence already exists (you created the table already, dropped it and, as someone said today, the DROP does not automagically get rid of the automagic sequence created when you declare a SERIAL field). You can try : DROP SEQUENCE test_id_seq; Just guessing... -- Jean-Christophe Boggio [EMAIL PROTECTED] Independant Consultant and Developer Delphi, Linux, Oracle, Perl
