Re: [SQL] SQL Query Performance tips
If I understand well a person has all the free weapons which have a level <= to his own level, and of course all the weapons he bought. 1) get da weapons One query can only use one index. Bad for you ! Let's split the free and non-free weapons. 1a) free weapons SELECT weapon_alignment, count(1) as cnt FROM weapons WHERE weapon_level < (user_level) AND weapon_cost = 0 GROUP BY weapon_alignment; No need for distinct anymore ! Note also that distinct'ing on weapon_name is a slower than on weapon_id. You can create an index on (weapon_cost,weapon_level) but I don't think it'll be useful. For ultimate speed, as this does not depend on the user_id, only the level, you can store the results of this in a table, precalculating the results for all levels (if there are like 10 levels, it'll be a big win). 1b) weapons bought by the user SELECT w.weapon_alignment, count(1) as cnt FROM weapons w, user_weapons uw WHERE w.weapon_id = uw.weapon_id AND uw.user_id = (the user_id) AND w.weapon_cost > 0 GROUP BY weapon_alignment; You'll note that the weapons in 1a) had cose=0 so they cannot appear here, no need to distinct the two. 2) combine the two SELECT weapon_alignment, sum(cnt) FROM (SELECT weapon_alignment, count(1) as cnt FROM weapons WHERE weapon_level < (user_level) AND weapon_cost = 0 GROUP BY weapon_alignment) UNION ALL SELECT w.weapon_alignment, count(1) as cnt FROM weapons w, user_weapons uw WHERE w.weapon_id = uw.weapon_id AND uw.user_id = (the user_id) AND w.weapon_cost > 0 GROUP BY weapon_alignment) GROUP BY weapon_alignment; You can also do this : SELECT weapon_alignment, count(1) as cnt FROM (SELECT weapon_alignment FROM weapons WHERE weapon_level < (user_level) AND weapon_cost = 0) UNION ALL SELECT w.weapon_alignment FROM weapons w, user_weapons uw WHERE w.weapon_id = uw.weapon_id AND uw.user_id = (the user_id) AND w.weapon_cost > 0) GROUP BY weapon_alignment; How does it turn out ? ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] return value of the trigger function
On Mon, Jan 10, 2005 at 15:15:33 +0100, Jarek Pude?ko <[EMAIL PROTECTED]> wrote: > Hi > > I have big problem with a trigger function. > Defs: > > CREATE TABLE foo (id int2, name varchar(20)); > > foo.id cannot be serial or autoint because it will not be unique. > > Now I need a trigger that return foo.id of the inserted record. > > INSERT INTO foo VALUES (max(foo.id)+1,'junk'); If you are really doing the above, you probably do want to use serial. If you insist on not doing this your app should do something like lock table, select max(foo.id)+1 from id, and then nsert using the value returned bye the select and then whatever else you want to do. > > > IMHO the trigger should be: > > CREATE TRIGGER tr_get_new_id > AFTER INSERT on foo > ON EACH ROW > EXECUTE PROCEDURE get_new_id(); > > But I cannot create the function :( > I don't know what type should be input and how about output? trigger or > int2? > > TIA, > > > -- > Jarek Pudelko > JP272-RIPE > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command >(send "unregister YourEmailAddressHere" to [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
[SQL] Split pg_dump script
Hello, The attached dump_split.sh script maybe helpful to some of you. It is called with a database name and a base file name. It first dumps out the schema and metadata of the database in a plain SQL file. After this, it dumps out the data, each table into its own file, compressed with bzip2. This basically allows one to create an ascii dump of a large database and still open and edit the schema with your favourite text editor afterwards. It also allows restoration of single tables (create the table by copying the instructions from the schema file, and then restore the content by piping the data file via bunzip2 into psql). I know that pg_dump -Ft and -Fc also allow to do this, but bzip2 has a far better compression ratio on most data. Comments welcome (I'm subscribed to this list, so no need to Bcc:). HTH, Markus dump_split.sh Description: application/shellscript signature.asc Description: OpenPGP digital signature
[SQL] SQL design question: null vs. boolean values
Hi all: I was wondering if anyone had recommendations for the following scenarios: (A) I have three radio boxes in the user form field_foo []yes []no []unknown These are mutually exclusive and user input is always required. So in the database, should I have something like: field_foo char(1) not null check (field_foo in 'y', 'n', 'u') OR field_foo char(1) check (field_foo in 'y', 'n') The second choice always implies that NULL means unknown, whereas for the first choice, unknown is coded as 'u'. (B) In the user form, I have a field like: field_bar [] select_me with ONE choice, which is optional. Should I code this as: field_bar char(1) not null check (field_foo in 'y', 'n') OR field_foo char(1) check (field_foo in 'y') The second choice always implies that NULL means not selected whereas whereas for the first choice, selected is coded as 'y' and not selected coded as 'n' Any advice, dear SQL experts ? Best regards, --j __ Do you Yahoo!? Yahoo! Mail - 250MB free storage. Do more. Manage less. http://info.mail.yahoo.com/mail_250 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] SQL design question: null vs. boolean values
j.random.programmer wrote: Hi all: I was wondering if anyone had recommendations for the following scenarios: (A) I have three radio boxes in the user form field_foo []yes []no []unknown These are mutually exclusive and user input is always required. So in the database, should I have something like: field_foo char(1) not null check (field_foo in 'y', 'n', 'u') OR field_foo char(1) check (field_foo in 'y', 'n') The second choice always implies that NULL means unknown, whereas for the first choice, unknown is coded as 'u'. Option 1 - the value is known, the user made a choice and it was to click the "unknown" box. The box could be labelled "marmalade" just as easily. (B) In the user form, I have a field like: field_bar [] select_me with ONE choice, which is optional. Should I code this as: field_bar char(1) not null check (field_foo in 'y', 'n') OR field_foo char(1) check (field_foo in 'y') The second choice always implies that NULL means not selected whereas whereas for the first choice, selected is coded as 'y' and not selected coded as 'n' Any advice, dear SQL experts ? First option. I'm not convinced the choice is optional - you've presented the tickbox to them so you have to assume they've read it and chosen not to tick it. -- Richard Huxton Archonet Ltd ---(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] SQL design question: null vs. boolean values
On Sat, Jan 15, 2005 at 06:40:18AM -0800, j.random.programmer wrote: > field_foo char(1) check (field_foo in 'y', 'n') > > The second choice always implies that NULL means > unknown, > whereas for the first choice, unknown is coded as 'u'. NULL actually means "unknown". SQL uses 3-valued logic: T, F, and NULL. So NULL here is a not-unreasonable choice. (Some would argue, however, that it's always better to have definite data. in which case, your three-option choice is what they'd prefer. My own view is that nullable boolean columns capture exactly the 3-value logic of SQL, so what's the problem?) > In the user form, I have a field like: > > field_bar > [] select_me > > with ONE choice, which is optional. > > Should I code this as: > > field_bar char(1) not null check (field_foo in 'y', > 'n') I'd use "boolean not null default 'f'", myself. But in any case, this is _not_ a use for NULL, because you know absolutely what the deal was: either the user selected, or else it didn't. A -- Andrew Sullivan | [EMAIL PROTECTED] A certain description of men are for getting out of debt, yet are against all taxes for raising money to pay it off. --Alexander Hamilton ---(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] I am writing a MS SQL server conversion utility and am having an issue with timestamp
On Fri, Jan 14, 2005 at 04:15:02PM -0500, Joel Fradkin wrote: > Any one have a good idea for dealing with a timestamp where only time is > available on some of the source records? TIMESTAMP values contain a date and time. If you have only times then they'll have to go in a TIME column or you'll have to use a bogus date in a TIMESTAMP column (ugly). > Some records have both time and day. > > My MSSQL database has 290 tables so splitting the fields would be a very > large project. Couldn't a conversion be done programatically? Or are there too many special cases that need wetware intervention? > Is there a way to add just the time part of date time to timestamp? Not that I'm aware of. Unless I'm mistaken you'll have to use separate DATE and TIME columns and set the date to NULL, else assign a bogus date where the date is missing, which wouldn't smell very nice. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(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
