Re: [SQL] ORDER records based on parameters in IN clause
Another option would be: SELECT * FROM table WHERE id=2003 OR id=1342 OR id=799 OR id=1450; This should give you the results in the right order... - Michiel Scott Marlowe wrote: On Wed, 2005-06-29 at 09:22, Russell Simpkins wrote: fair enough. but a simple order by id would never work. Try this: select *, case when id=2003 then 1 when id=1342 then 2 when id=799 then 3 when id=1450 then 4 end as ob from tablename where id in (2003,1342,799,1450) order by ob; ---(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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] ORDER records based on parameters in IN clause
On 6/30/05, M.D.G. Lange <[EMAIL PROTECTED]> wrote: > Another option would be: > SELECT * FROM table WHERE id=2003 OR id=1342 OR id=799 OR id=1450; > This should give you the results in the right order... I don't think so... create temporary table seq as select * from generate_series(1,20) as g(id); select * from seq where id in (5,2,12); id 2 5 12 select * from seq where id = 5 or id = 2 or id = 12; id 2 5 12 It certainly doesn't work. You have to order it by something, like: select * from seq where id in(5,2,12) order by id=5 desc,id=2 desc,id=12 desc; id 5 2 12 Regards, Dawid ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] ENUM like data type
|> > I disagree. In several relations (views of the world) one |needs to have a |> > hand full of well defined values while |> > integers or bools are not appropriate and strings are too |free form. |> > For example male female or true and false. Whilst the |second has a well |> > known type, other things like a telephone number type |don't have such type. |> > So a new one should be created instead of (varchar/ xy |between 4 and 8). One |> > might argue that new 1:n relations with integrity shall be |established but |> > this is in my eyes overkill for a constant number of values. |> > Also think of schema export without data. These values are |part of the |> > schema data but not the schema itself which in this sense is wrong |> |> Please, most of this can be done with CHECK(...). | |Indeed. A CHECK constraint on a DOMAIN is an ENUM plus some. Yea, that's what also I currently doing. But using varchars instead of symbolic values (as int4) is just more efficient. | |-- | | |---(end of |broadcast)--- |TIP 5: Have you checked our extensive FAQ? | | http://www.postgresql.org/docs/faq | ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] SQL Query question
Hi Whilst I'm not new to SQL I am reasonably new to Postgres and as such I have a question on the following query: SELECT tbl1."TermTypeID", tbl1."ParentID", tbl1."KeywordID", tbl1."Term", tbl2."KeywordID" FROM "Terms" As tbl1 LEFT JOIN "SearchStore" As tbl2 ON tbl1."KeywordID" = tbl2."KeywordID" AND tbl2."StockID" = 1 WHERE (tbl1."TermTypeID" >= 200) AND (tbl1."TermTypeID" < 600) AND (tbl1."IsSynonym" = false) AND (tbl1."LanguageID" = 1) ORDER BY tbl1."TermTypeID", tbl1."Term"; Why does the above query work fine and the folowing query not work? And as a additional kind of spanner in the works I've tried the following on MS SQL Server and Oracle both of which produce the correct results (i.e. the same as the above query). NB: the Terms table always has data whereas the SearchStore may or may not have any data. SELECT tbl1."TermTypeID", tbl1."ParentID", tbl1."KeywordID", tbl1."Term", tbl2."KeywordID" FROM "Terms" As tbl1 LEFT JOIN "SearchStore" As tbl2 ON tbl1."KeywordID" = tbl2."KeywordID" WHERE (tbl1."TermTypeID" >= 200) AND (tbl1."TermTypeID" < 600) AND (tbl1."IsSynonym" = false) AND (tbl1."LanguageID" = 1) AND (tbl2."StockID" = 1) ORDER BY tbl1."TermTypeID", tbl1."Term"; Just to be obvious both queries as far as I can should return everything from Terms and anything if it exists from SearchStore subject to the WHERE clause parameters - obviously! Many thanks in advance Nick ---(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] ORDER records based on parameters in IN clause
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 > fair enough. but a simple order by id would never work. That was me, sorry, I must have been asleep when I wrote it. :) - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200506300636 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFCw8uCvJuQZxSWSsgRAlVbAKCcJ9ktDZggHeICw/gZTBXoeAcK8gCghDKN 7jWWr2T1diDLeEmhzLhogCQ= =Yjrr -END PGP SIGNATURE- ---(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] ENUM like data type
Am Donnerstag, 30. Juni 2005 00:55 schrieb Tom Lane: > It's not that hard to make your own type using the builtin textin and > textout functions, and then add just the functions you wish to provide. Implementing the "distinct type" feature of SQL would probably amount to something like that. Might be worth looking into. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] SQL Query question
Am Donnerstag, 30. Juni 2005 11:27 schrieb Nick Stone: > SELECT > tbl1."TermTypeID", > tbl1."ParentID", > tbl1."KeywordID", > tbl1."Term", > tbl2."KeywordID" > FROM > "Terms" As tbl1 LEFT JOIN > "SearchStore" As tbl2 ON tbl1."KeywordID" = tbl2."KeywordID" > WHERE > (tbl1."TermTypeID" >= 200) AND > (tbl1."TermTypeID" < 600) AND > (tbl1."IsSynonym" = false) AND > (tbl1."LanguageID" = 1) AND > (tbl2."StockID" = 1) > ORDER BY > tbl1."TermTypeID", > tbl1."Term"; > > Just to be obvious both queries as far as I can should return everything > from Terms and anything if it exists from SearchStore subject to the WHERE > clause parameters - obviously! The condition (tbl2."StockID" = 1) will remove all rows that have null values in the tbl2 fields, thus making your left join useless. Perhaps you should change that to (tbl2."StockID" = 1 OR tbl2."StockID" IS NULL) -- or use your first version. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] SQL Query question
Nick Stone wrote: Hi Whilst I'm not new to SQL I am reasonably new to Postgres and as such I have a question on the following query: FROM "Terms" As tbl1 LEFT JOIN "SearchStore" As tbl2 ON tbl1."KeywordID" = tbl2."KeywordID" AND tbl2."StockID" = 1 Why does the above query work fine and the folowing query not work? And as a additional kind of spanner in the works I've tried the following on MS SQL Server and Oracle both of which produce the correct results FROM "Terms" As tbl1 LEFT JOIN "SearchStore" As tbl2 ON tbl1."KeywordID" = tbl2."KeywordID" WHERE ... (tbl2."StockID" = 1) Hmm - I'm not sure that MSSQL/Oracle are giving the "right" answer here. I assume the tbl2.stockid test is the issue here, and we apply the test after the join whereas the others push the condition inside the join. I'm inclined to prefer PG's way of doing things, since it means you get what you explicitly asked for (to my point of view anyway). Not sure what the SQL spec says though, and in the end I suppose that's the only way to decide "right". -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] ORDER records based on parameters in IN clause
M.D.G. Lange wrote: Another option would be: SELECT * FROM table WHERE id=2003 OR id=1342 OR id=799 OR id=1450; This should give you the results in the right order... Per the SQL Standard, the rows of a table have no ordering. The result of a SELECT is just a derived table. Assuming a row order is *always* a bug. If you want an explicit row order you *must* use an ORDER BY clause. I would also recommend to you a saying that I learned many years ago, "Filter on the server, sort on the client." - Michiel Scott Marlowe wrote: On Wed, 2005-06-29 at 09:22, Russell Simpkins wrote: fair enough. but a simple order by id would never work. Try this: select *, case when id=2003 then 1 when id=1342 then 2 when id=799 then 3 when id=1450 then 4 end as ob from tablename where id in (2003,1342,799,1450) order by ob; ---(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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Daryl Richter Platform Author & Director of Technology v: 610.361.1000 x202 (( Brandywine Asset Management ) ( "Expanding the Science of Global Investing" ) ( http://www.brandywine.com )) ---(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] SQL Query question
Thanks for the reply at least that explains it. Nick -Original Message- From: Richard Huxton [mailto:[EMAIL PROTECTED] Sent: 30 June 2005 12:22 To: Nick Stone Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] SQL Query question Nick Stone wrote: > Hi > > Whilst I'm not new to SQL I am reasonably new to Postgres and as such > I have a question on the following query: > FROM > "Terms" As tbl1 LEFT JOIN > "SearchStore" As tbl2 ON tbl1."KeywordID" = tbl2."KeywordID" AND > tbl2."StockID" = 1 Why does the above query work fine and the folowing > query not work? And as a additional kind of spanner in the works I've > tried the following on MS SQL Server and Oracle both of which produce > the correct results > FROM > "Terms" As tbl1 LEFT JOIN > "SearchStore" As tbl2 ON tbl1."KeywordID" = tbl2."KeywordID" > WHERE ... > (tbl2."StockID" = 1) Hmm - I'm not sure that MSSQL/Oracle are giving the "right" answer here. I assume the tbl2.stockid test is the issue here, and we apply the test after the join whereas the others push the condition inside the join. I'm inclined to prefer PG's way of doing things, since it means you get what you explicitly asked for (to my point of view anyway). Not sure what the SQL spec says though, and in the end I suppose that's the only way to decide "right". -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Updatable view
I have been looking around to get an idea how to make RULES on a VIEW for INSERT , UPDATE and DELETE of a VIEW with an INNER JOIN. First the idea: There are 3 tables, those are related one way or another, combined in this view: pg_user (ok, it's a view really) tblcontacts (with contact information like first name and lastname and such) tbldepartments (a user works at a certain department) tblusersettings (some extra information about the user which is not in pg_shadow, eg locked status, birthdate and such) tblusersettings has the links to tbldepartments, tblcontacts and pg_user (via a trigger to check if the user exists) --- CREATE OR REPLACE VIEW usersview AS SELECT tblusersettings.userid, tblusersettings.birthdate, tblusersettings.islocked, pg_user.usename, pg_user.usesuper, pg_user.valuntil, tbldepartments.departmentname, tbldepartments."ID" AS departmentid, tbldepartments.dateformatid, tbldepartments.currencyid, tblcontacts."ID" AS contactid, tblcontacts.firstname, tblcontacts.lastname, tblcontacts.gender, tblcontacts."function", tblcontacts.phone, tblcontacts.email, tblcontacts.languageid FROM tblusersettings JOIN pg_user ON tblusersettings.userid = pg_user.usesysid JOIN tbldepartments ON tblusersettings.departmentid = tbldepartments."ID" JOIN tblcontacts ON tblusersettings.contactid = tblcontacts."ID" ORDER BY pg_user.usename; the view is OK, so I want something like this to INSERT a user: CREATE OR REPLACE RULE insertuser AS ON INSERT TO usersview DO INSTEAD ( (CREATE USER NEW.usename); (INSERT INTO tblcontacts(firstname, lastname, gender, titleid, function, phone, email, languageid, attentionid) VALUES (NEW.firstname, NEW.lastname, NEW.gender, 0, NEW.function, NEW.phone, NEW.email, NEW.mobile, NEW.languageid, 0)); ); The idea is: - Create a user - Create a contact - Create usersettings for the user with in it the newly created contactid and userid. How impossible is this? How do I get the newly created contact and user id's in the usersettings table? TIA, Michiel ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
