Re: [SQL] Re: Use of the LIMIT clause ?
On Mon, Mar 12, 2001 at 09:21:58PM -0500, Tom Lane wrote: > Spy <[EMAIL PROTECTED]> writes: > > Tom Lane a écrit : > >> Is that actually how MySQL interprets two parameters? We treat them > >> as count and offset respectively, which definition I thought was the > >> same as MySQL's. > > > But MySQL's syntax is different, as found on > > http://www.mysql.com/doc/S/E/SELECT.html : > > "SELECT [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] > > [SQL_BUFFER_RESULT] > > [...] > > [LIMIT [offset,] rows]" > > That's annoying; looks like we do it backwards from MySQL. Can anyone > confirm that this is how MySQL behaves (maybe it's a typo on this > documentation page)? Yes, it does behave as documented. > Should we consider changing ours if it is different? I don't know that it's worth it... it seems to inconvenience some people either way. I may soon be moving a moderately complex system from MySQL to Postgres and it wouldn't be the end of my world if I had to reverse all the LIMITs. Richard ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Oracle to PostgreSQL help: What is (+) in Oracle select?
On Fri, Mar 16, 2001 at 05:57:14PM -0500, Christopher Audley wrote: > I'm trying to modify an application which runs on Oracle to run against > PostgreSQL. I'm currently stuck on a query that I can't recognize, it > doesn't look like standard SQL. > > A select is done across two tables, however when joining the foreign > key, the right hand side of the equallity has (+) appended > > SELECT o.* from one o, two t where o.key = t.key(+) > > Does anyone know what this does and how I can reproduce the select in > PostgreSQL? It's an outer join. In Postgres it'd be SELECT o.* from one left outer join two using ( key ) but it's new in 7.1 . Richard ---(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] primary key scans in sequence
On Thu, Mar 29, 2001 at 03:47:58PM +0200, bernd wrote: > hey i have the following table def (834.000 rows, vaccum analyze'd): > dl_online=# \d mitglied > Table "mitglied" >Attribute| Type | Modifier > +--+ > mitgliedid | bigint | not null > dlnummer | varchar(30) | > vorname| varchar(50) | > zuname | varchar(50) | not null > geburtsdatum | varchar(20) | > aktiv | boolean | not null default 't'::bool > strasse| varchar(255) | > plz| varchar(25) | > ort| varchar(255) | > telefon| varchar(255) | > eintrittsdatum | varchar(20) | > geschlechtid | bigint | not null default 3 > treuelevelid | bigint | not null default 1 > clubmitglied | boolean | not null default 'f'::bool > bemerkungen| text | > mid| bigint | > > Indices: mitglied_dlnummer_idx, [on dlnummer] > mitglied_pkey [on mitgliedid] > > ok; i use 2 querys: > > 1) get one row over dlnummer: > dl_online=# explain select * from mitglied where dlnummer = '098765432'; > NOTICE: QUERY PLAN: > Index Scan using mitglied_dlnummer_idx on mitglied (cost=0.00..4.77 rows=1 > width=154) > EXPLAIN > > 2) get one row over the primatry key (mitgliedid): > dl_online=# explain select * from mitglied where mitgliedid = 833228; > NOTICE: QUERY PLAN: > Seq Scan on mitglied (cost=0.00..18674.74 rows=1 width=154) > EXPLAIN > > why doesn't use postrges in (2) the primary-key-index?? take a look at the > cost! and both queries returns only ONE row (the optimizer knows that fact). Because the type of the "mitgliedid" is "bigint", but the type of the constant "833228" is "integer" (I think; certainly it isn't "bigint"). Postgres doesn't realise that it can use an index on a bigint to do comparisons to an integer. If you explicitly cast the constant to a bigint, it should be willing to do an index scan, like so: select * from mitglied where mitgliedid = 833228::bigint Yes, this is a bit unpleasant to have to in your client code, and no, I don't know if there's a neater way to let Postgres know it can use this index for this query. But what I've just described does work. Richard ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] 2 tables, joins and same name...
On Thu, Aug 30, 2001 at 04:25:41PM -0400, Marc André Paquin wrote: > Hello, > > Here is 2 tables: > > airport > - > airport_id > name > code > city_id > > destination > --- > destination_id > dest_name > ... > airport_dep_id // using airport.airport_id (departure) > airport_arr_id // using airport.airport_id has well (arrival) > > I have 2 columns in the second table that uses the same name column in > the first table... > > I dont know how to formulate my SQL query... I want to select the > destinations in the destination table with not the ID of each airport > but their names. I can do a join with one but with the second one, I get > no results... And this is confusing! > > select dest.dest_name, air.name as airport1, air.name as airport2 from > destination, airport air where dest.airport_dep_id_id=air.airport_id and > dest.airport_arr_id=air.airport_id; You have to join against the airport table twice: SELECT dest.dest_name, air1.name as airport1, air2.name as airport2 FROM desination dest, airport air1, airport 2 WHERE dest.airport_dep_id = air1.airport_id AND dest.airport_arr_id = air2.airport_id; Richard ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] CHECK clause doesn't work with CASE clause
On Thu, Jun 27, 2002 at 11:52:54AM +0200, J?rg Holetschek wrote:
> Hi folks,
>
> I have a problem with a CHECK clause that doesn't seem to work properly. The
> CASE
> WHEN ((focus <> NULL) AND (epilepsy_class = 'f')) THEN
> TRUE
> WHEN ((focus <> NULL) AND (epilepsy_class IN ('g',
> 'n'))) THEN FALSE
> END),
The condition "focus <> NULL" can't ever come out true. You probably
mean IS NOT NULL.
Richard
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] unique value - trigger?
n Thu, Jul 17, 2003 at 12:56:52PM +0100, Gary Stainburn wrote: > > nymr=# \d lnumbers >Table "lnumbers" > Column | Type | Modifiers > ---+---+--- > lnid | integer | not null > lnumber | character varying(10) | not null > lncurrent | boolean | > Primary key: lnumbers_pkey > Triggers: RI_ConstraintTrigger_7575462 > > I want to make it so that if I set lncurrent to true for one row, any existing > true rows are set to false. > > I'm guessing that I need to create a trigger to be actioned after an insert or > update which would update set lncurrent=false where lnid not = Absolutely. Something like this will work: CREATE FUNCTION lnumbers_current_trigger() RETURNS TRIGGER AS ' BEGIN IF NEW.lncurrent THEN UPDATE lnumbers SET lncurrent = ''f'' WHERE lnid = NEW.lnid AND lnumber != NEW.lnumber AND lncurrent = ''t''; END IF; RETURN NEW; END' LANGUAGE 'plpgsql'; CREATE TRIGGER lnumbers_current AFTER UPDATE OR INSERT ON lnumbers FOR EACH ROW EXECUTE PROCEDURE lnumbers_current_trigger(); (Lightly tested only on 7.3.3) In the WHERE condition in the function, specifying "lncurrent = 't'" means that we don't update more rows than we have to. Making the trigger fire AFTER INSERT as well as AFTER UPDATE means that if you just add a new row with lncurrent set to true, it Does The Right Thing. In this particular example, the trigger will work perfectly well as a BEFORE, also. If you can't or don't want to install PL/PgSQL (or some other procedural language), you can do it with rules. It's more long-winded that way, although I also think it's more elegant... Richard ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] unique value - trigger?
On Thu, Jul 17, 2003 at 12:56:52PM +0100, Gary Stainburn wrote: > > nymr=# \d lnumbers >Table "lnumbers" > Column | Type | Modifiers > ---+---+--- > lnid | integer | not null > lnumber | character varying(10) | not null > lncurrent | boolean | > Primary key: lnumbers_pkey > Triggers: RI_ConstraintTrigger_7575462 > > I want to make it so that if I set lncurrent to true for one row, any existing > true rows are set to false. > > I'm guessing that I need to create a trigger to be actioned after an insert or > update which would update set lncurrent=false where lnid not = Absolutely. Something like this will work: CREATE FUNCTION lnumbers_current_trigger() RETURNS TRIGGER AS ' BEGIN IF NEW.lncurrent THEN UPDATE lnumbers SET lncurrent = ''f'' WHERE lnid = NEW.lnid AND lnumber != NEW.lnumber AND lncurrent = ''t''; END IF; RETURN NEW; END' LANGUAGE 'plpgsql'; CREATE TRIGGER lnumbers_current AFTER UPDATE OR INSERT ON lnumbers FOR EACH ROW EXECUTE PROCEDURE lnumbers_current_trigger(); (Lightly tested only on 7.3.3) In the WHERE condition in the function, specifying "lncurrent = 't'" means that we don't update more rows than we have to. Making the trigger fire AFTER INSERT as well as AFTER UPDATE means that if you just add a new row with lncurrent set to true, it Does The Right Thing. In this particular example, the trigger will work perfectly well as a BEFORE, also. If you can't or don't want to install PL/PgSQL (or some other procedural language), you can do it with rules. It's more long-winded that way, although I also think it's more elegant... Richard ---(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] One to many query question
On Wed, Jul 30, 2003 at 02:35:20PM -0500, Dave Dribin wrote: > Hi, I'm having trouble with what I think should be an easy query. For > simplicity, I will use a CD database as an example. Each CD may have > multiple genres. Here's some sample data: > > Artist Title Genres > -- -- > Miles Davis Some Kind of Blue Jazz > Metallica Ride the Lightning Rock > Chemical Brothers Surrender Electronic > Radiohead OK Computer Rock, Electronic > > For simplicities sake, let's ignore normalization on artist and genre, > and say the tables look like: > > CREATE TABLE cd ( > id integer unique, > artist varchar(25), > title varchar(25) > ); > > CREATE TABLE cd_genres ( > cd_id integer, > genre varchar(25) > ); Assuming that each CD can have several rows in cd_genres, SELECT id, artist, title FROM cd WHERE id NOT IN ( SELECT cd_id FROM cd_genres WHERE genre = 'Rock' ); will do what you want. Your co-worker is perhaps used to certain lesser databases which don't support subselects... Richard ---(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: TODO item for plpgsql Was Re: [SQL] obtuse plpgsql function needs
On Fri, Aug 01, 2003 at 01:06:18PM -0700, Josh Berkus wrote: > As far as I can tell from my PL/SQL guide to Oracle8, PL/SQL does not permit > exectution of strings-as-queries at all. So there's no equivalent in PL/SQL. I'm not an Oracle bunny but they seem to have something vaguely similar to what we do; they call it "EXECUTE IMMEDIATE" and the concept is described as "Dynamic SQL". http://download-west.oracle.com/docs/cd/A87860_01/doc/appdev.817/a77069/10_dynam.htm#4376 Richard ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Left joins with multiple tables
On Sat, Jan 17, 2004 at 02:30:01AM +, Colin Fox wrote: > For each person in the people table, they may or may not have a record in > a, may or may not have a record in b, and may or may not have a record in > c. ... > But I'd like to be able to do something like: > > select > id, name, a.field1, b.field2, c.field3 > from > people p left outer join a on a.person_id = p id, > people p left outer join b on b.person_id = p.id, > people p left outer join c on c.person_id = p.id; You can just chain the joins and the Right Thing will happen: SELECT id, name, a.field1, b.field2, c.field3 FROM people p LEFT OUTER JOIN a ON (p.id = a.person_id) LEFT OUTER JOIN a ON (p.id = b.person_id) LEFT OUTER JOIN a ON (p.id = c.person_id) I'm not sure that this behaviour is mandated by the SQL standard; a certain other popular open source database-like product interprets the same construction differently. But it does do what you want in postgres. Richard ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] max value from join
On Mon, Jun 14, 2004 at 11:02:06AM -0500, hook wrote: > I have a court program with related tables > I am trying to extract data related to the last conttinue date using > select >c.citkey, /* c.cdate, >c.badge, c.vioDesc, >b.lname, b.fname,b.mi, b.race, b.dob, b.sex, >d.docket, d.plea, d.fine,d.costs, d.ddate, d.abdocket, d.bond, >p.disDate, p.disDesc, p.disCode, p.amount, >*/ >t.contDate, >t.abcontinue, >w.bndType, w.bndAmt >from citation c, cdefendant b, ccourt d, ccontinue t, > disposition p, warrant w >where c.citkey = b.citkey and > b.citkey = d.citkey and > d.citkey = t.citkey and > t.citkey = p.citkey and > p.citkey = w.citkey >group by > c.citkey, c.cdate, c.badge, c.vioDesc, > b.lname, b.fname, b.mi, b.race, b.dob, b.sex, > d.docket, d.plea, d.fine, d.costs, d.ddate, d.abdocket, d.bond, > p.disDate, p.disDesc, p.disCode, p.amount, > t.abcontinue, t.contDate, > w.bndType, w.bndAmt >having max(t.contDate) = t.contDate >order by c.citkey > > > I cannot seem to get unique rows with only the max contDate?? A subselect may be useful to you: SELECT c.citkey, t.contDate -- other fields... FROM citation c, ccontinue t -- other tables... WHERE c.citkey = t.citkey-- other join clauses... AND t.contDate = ( SELECT max(contDate) FROM ccontinue ) -- no need for GROUP BY / HAVING ORDER BY c.citkey Richard ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Scalar subqueries
On Mon, Jun 14, 2004 at 12:38:44PM -0700, Subbiah, Stalin wrote: > > I've a logs table that has both sign-in and sign-out records which are > differentiated by action flag. Records with action flag = (1,2) => sign-in > records and action flag = (3,4,5,6,7) => sign-out records. > All I'm trying to do is print signin id and corresponding sign-out id's in > single row. You're writing one more level of sub-select than you actually need. SELECT inlog.log_id AS signin_id, ( SELECT MIN(outlog.log_id) FROM logs outlog WHERE outlog.log_id > inlog.log_id AND action IN (3, 4, 5, 6, 7) ) AS signout_id FROM logs inlog WHERE inlog.action IN (1, 2); Assuming you want to match signins and signouts by the same account to the same service, or whatever, you can add in clauses like AND outlog.account_id = inlog.account_id or whatever else you like, to the inner select. Richard ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Trigger and function not on speaking terms
On Mon, Aug 02, 2004 at 04:20:15PM -0400, Jeff Boes wrote: > It would seem my trigger definition is trying to find fn_foo(), when I > mean for it to call fn_foo(TEXT). Triggers have to be declared to take no arguments; they find the rows on which they operate in magical ways. (For PL/PgSQL triggers, see chapter 37.10 of the manual.) Richard ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] SQL confusion
On Sat, Oct 09, 2004 at 01:39:45PM -0700, Andrew Ward wrote: > > I'm running postgres 7.3.2 on linux, and making my > requests from Perl scripts using DBD::Pg. My table > structure is as follows (irrelevant cols removed) > > CREATE TABLE name ( > namecounter integer NOT NULL, > firstmiddle character varying(64) NOT NULL, > lastname character varying(64) NOT NULL, > birthdate date, > hh smallint, > famnu integer, > ); This may not be the fastest query, but it should be quite comprehensible. Lightly tested only on 7.4.5 (do you know why you're not using 7.4.x?). SELECT namecounter -- and whatever other columns you need FROM name outername -- alias so it's clear what the joins do WHERE hh = 1 -- simple case: head of family OR ( hh = 0 -- not a head AND NOT EXISTS ( -- there is no head of this family SELECT namecounter FROM name in1 WHERE hh = 1 AND in1.famnu = outername.famnu) AND birthdate = ( -- this person is as old as the oldest person SELECT MIN(birthdate) FROM name in2 WHERE in2.famnu = outername.famnu) ); Richard ---(end of broadcast)--- TIP 8: explain analyze is your friend
