Re: [SQL] Multicolum index and primary key
On Mon, 2003-11-17 at 18:13, Tomasz Myrta wrote: > Dnia 2003-11-17 19:00, Użytkownik Michele Bendazzoli napisał: > > a is always present in the queries ... and other that (a, ab, abc) i > > have only to query (ac): so I think I have to index separately only > > (ac). > > For such cases consider changing primary key from (a,b,c) into (a,c,b) b,c are exactly simmetric, so if I change the primary key to (a,c,b) I have to index separately (a,b) ... rigth? ciao, Michele ---(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] Quota query with decent performance?
On Mon, Nov 17, 2003 at 23:55:53 +0100, Troels Arvin <[EMAIL PROTECTED]> wrote: > On Tue, 11 Nov 2003 18:49:31 -0500, Chester Kustarz wrote: > > [... discussion of top-n query (where n=3) ...] > > > select * > > from person > > where age <= > > (select age from person order by age limit 1 offset 2); > > It fails when the cardinality of person is less than 3 (returns empty > set). My solution is this, which is not as beautiful any more: Used that way the subselect value will be null if there are no matching rows. This should allow you to do something like: select * from person where not isfalse (age <= (select age from person order by age limit 1 offset 2)); ---(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] Quota query with decent performance?
On Tue, 18 Nov 2003 06:56:42 -0600, Bruno Wolff III wrote: > select * > from person > where not isfalse (age <= > (select age from person order by age limit 1 offset 2)); Thanks; much nicer than my COALESCE-variant. http://troels.arvin.dk/db/rdbms/#select-top-n-postgresql updated. -- Greetings from Troels Arvin, Copenhagen, Denmark ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Quota query with decent performance?
On Tue, 18 Nov 2003 06:56:42 -0600, Bruno Wolff III wrote: [...] > where not isfalse (age <= [...] Strange. I can't find the ISFALSE in neither PostgreSQL or standard SQL documentation. How can that be? -- Greetings from Troels Arvin, Copenhagen, Denmark ---(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] Quota query with decent performance?
On Tue, Nov 18, 2003 at 15:21:43 +0100, Troels Arvin <[EMAIL PROTECTED]> wrote: > On Tue, 18 Nov 2003 06:56:42 -0600, Bruno Wolff III wrote: > > [...] > > where not isfalse (age <= > [...] > > Strange. I can't find the ISFALSE in neither PostgreSQL or standard SQL > documentation. How can that be? I didn't remember the exact syntax and found the isfalse function by guessing. The standard syntax is expression is false instead of isfalse(expression), though the function does work (in 7.4 at least). area=> select isfalse(null); isfalse - f (1 row) ---(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] Quota query with decent performance?
On Tue, Nov 18, 2003 at 15:21:43 +0100, Troels Arvin <[EMAIL PROTECTED]> wrote: > On Tue, 18 Nov 2003 06:56:42 -0600, Bruno Wolff III wrote: > > [...] > > where not isfalse (age <= > [...] > > Strange. I can't find the ISFALSE in neither PostgreSQL or standard SQL > documentation. How can that be? I forgot to mention that the is false operator is described under the section on comparison operators. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Quota query with decent performance?
On Tue, 18 Nov 2003 08:36:27 -0600, Bruno Wolff III wrote: > The standard syntax is expression is false instead of > isfalse(expression) OK, so I guess that a 'better' (closer to standard) version of your query would be: SELECT * FROM pview WHERE ( age <= ( SELECT age FROM pview ORDER BY age ASC LIMIT 1 OFFSET 20 -- 2=n-1 ) ) IS NOT FALSE; -- Greetings from Troels Arvin, Copenhagen, Denmark ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] Need query to separate rows by one field's value
Here's a query challenge for you. Given: table foo ( a integer primary key, b integer check (b > 0)) with a limited number of rows (say, <= 10). Values in column "a" are unique (obviously). Values in column "b" are not. I want to find a set of values for "a" such that the sum of "b" values is as close to one-half of the total sum of "b" over all rows. That is, select 2.0*sum(b) from foo where a in (...); would be approximately the same as select sum(b) from foo; Likewise, select 2.0*sum(b) from foo where a NOT in (...); would be approximately that value, too. -- Jeff Boes vox 269.226.9550 ext 24 Database Engineer fax 269.349.9076 Nexcerpt, Inc. http://www.nexcerpt.com ...Nexcerpt... Extend your Expertise ---(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] Addition and subtraction on BIT type
Thank you for your reply. select int4(b'1001')::bit(32); gives the same result as what you gave. select int4(b'1001')::bit(4); gives the upper four bits, which are all zeroes. How would I get the lower four bits? I building bitmaps using plpgsql, and therefore, I will be doing a lot bit manipulation. Thanks, Yasir On Sun, 16 Nov 2003, Stephan Szabo wrote: > Date: Sun, 16 Nov 2003 21:40:45 -0800 (PST) > From: Stephan Szabo <[EMAIL PROTECTED]> > To: Yasir Malik <[EMAIL PROTECTED]> > Cc: [EMAIL PROTECTED] > Subject: Re: [SQL] Addition and subtraction on BIT type > > On Sun, 16 Nov 2003, Yasir Malik wrote: > > > I think I am almost at a solution to my last question. I can do > > select int4(a) from test; > > to convert to an integer. So now addition and > > subtraction can be done between bit types. But how do I convert back to > > BIT type? If I do > > select bit(int4(b'1001')); > > > > I get the following message: > > ERROR: parser: parse error at or near "int4" at character 12 > > > > Can anyone tell me why the bit function is not working? It's under the > > pg_catalog schema. > > It's also the name of a type that takes a precision in parentheses, so > you'd have to say "bit"(...) with the quotes. As a note, I think > that's going to effectively return you a bit(32), so > > sszabo=# select "bit"(int4(b'1001')); >bit > -- > 1001 > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Function ROWTYPE Parameter with NEW/OLD
Postgres 7.2.1 I'm trying to create a function that takes a ROWTYPE parameter that I can then call from a rule by passing NEW/OLD, but am having problems. CREATE OR REPLACE FUNCTION "some_boolean_function" (mytablename) RETURNS boolean AS ' DECLARE mytable ALIAS FOR $1; BEGIN -- IF SOME CONDITION RETURN TRUE ELSE RETURN FALSE; END;' LANGUAGE 'plpgsql'; This works fine. CREATE RULE some_rule AS ON UPDATE TO mytablename WHERE (some_boolean_function(new, old)) DO INSTEAD (some other statements); It cacks on the new and old parameters - misunderstanding on my part? potential bug? I have to check most of the fields in this table, so would rather pass the whole record rather than individual fields. Thanks, Andrew ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Array fields in Postgresql...
> Can any one give me a link/tutorial for using arrays in databases..I
> need Queries to access the arrays as well...
A quick search in Google.Com yielded a number of results which included
the following:
PostgreSQL Tutorial
http://www.eskimo.com/support/PostgreSQL/tutorial/
("Arrays" are listed near the end of the page)
In Google.Com, you can also use the following search string:
+postgresql +arrays +tutorial
--
Randolf Richardson - [EMAIL PROTECTED]
Inter-Corporate Computer & Network Services, Inc.
Vancouver, British Columbia, Canada
http://www.8x.ca/
This message originated from within a secure, reliable,
high-performance network ... a Novell NetWare network.
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] type conversion needed
Title: Message How to convert bytea data type to character varying and vice versa looking forward for ur assistance kishore
Re: [SQL] SOLVED: Emulating 'connect by prior' using stored proc
[sNip] > In order to make this work with postgres an additional table is needed > that can hold the level (depth) of the branch because pgsql doesn't like > returning a tuple that isn't based on a defined structure. Once you've > created this table you can pretty much forget about it. > > CREATE TABLE "node_relationships_n_level" ( > "level" integer > ) inherits (node_relationships); > > > Now create your stored procedure. > > CREATE OR REPLACE FUNCTION "crawl_tree" (integer,integer) RETURNS SETOF > node_relationships_n_level AS 'DECLARE > temp RECORD; > child RECORD; > BEGIN > SELECT INTO temp *, $2 AS level FROM node_relationships WHERE > child_node_id = $1; > > IF FOUND THEN > RETURN NEXT temp; > FOR child IN SELECT child_node_id FROM node_relationships WHERE > parent_node_id = $1 ORDER BY ordinal LOOP > FOR temp IN SELECT * FROM crawl_tree(child.child_node_id, $2 + > 1) LOOP > RETURN NEXT temp; > END LOOP; > END LOOP; >END IF; > RETURN NULL; > END; > ' LANGUAGE 'plpgsql'; > > > The second parameter must be zero. This is a kludge because this is a > recursive function and I needed some way of passing the level to > successive function calls. However, if you like, you could consider this > to be a "level offset"--set it to '2' and all the levels returned will > be n + 2. > > Execute "SELECT * FROM crawl_tree(682904,0)" and you're done. > > Hope this helps people. I have one question because I'm not clear about something with your implementation (a good one too by the looks of it -- thanks for sharing this information); if I start my query from an item at level 5, will the level be reflected as such, or will it dynamically start at 1? As I understand it, in Oracle the level would begin at 1 in this case. Thanks in advance. -- Randolf Richardson - [EMAIL PROTECTED] Inter-Corporate Computer & Network Services, Inc. Vancouver, British Columbia, Canada http://www.8x.ca/ This message originated from within a secure, reliable, high-performance network ... a Novell NetWare network. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] HELP ME
> Where can i find a tutorial on PL/PGSQL?.Help me by > listing some sites to guide me in this context. Does the following meet your needs? Chapter 19. PL/pgSQL - SQL Procedural Language http://www.sql.org/sql-database/postgresql/manual/plpgsql.html -- Randolf Richardson - [EMAIL PROTECTED] Inter-Corporate Computer & Network Services, Inc. Vancouver, British Columbia, Canada http://www.8x.ca/ This message originated from within a secure, reliable, high-performance network ... a Novell NetWare network. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Oracle 'connect by prior' now eaiser in 7.3?
[sNip] > See connectby() in contrib/tablefunc. Someone was working on SQL99 > recursive queries but it didn't get done for 7.4 -- perhaps it will be > in 7.5. In the meantime, connectby() is in 7.3 and might work for you. Oracle 8i doesn't seem to have any special indexing to handle this efficiently. Do you happen to know if PostgreSQL will have a special indexing option for this feature? If it does, it will very likely provide a major performance advantage over Oracle. -- Randolf Richardson - [EMAIL PROTECTED] Inter-Corporate Computer & Network Services, Inc. Vancouver, British Columbia, Canada http://www.8x.ca/ This message originated from within a secure, reliable, high-performance network ... a Novell NetWare network. ---(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] Closed
> is here nobody? Is this group closed? I see that your question wasn't answered. There are people around here, but it looks like the activity is a bit scattered -- some newsgroups are much busier, such as Novell.Com's DeveloperNet and SpamCop.Net's help newsgroups, while there are others that see very little activity at all. Anyway, don't worry, this place is active in a healthy way. -- Randolf Richardson - [EMAIL PROTECTED] Inter-Corporate Computer & Network Services, Inc. Vancouver, British Columbia, Canada http://www.8x.ca/ This message originated from within a secure, reliable, high-performance network ... a Novell NetWare network. ---(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] strange DEFERRABLE behaviour
Hi I've got some tables defined as: create table xx ( some_column references master_table It means this column is defined by default: NOT DEFERRABLE, INITIALLY IMMEDIATE I tried replacing rows in this table by new ones as: set autocommit=off; begin; set constraints all deferred; delete from insert ... insert... commit; I get "integrity violation"... just after "delete" If I well understood manual, it works like it should. I'm not sure, because if I run this query in pgAdmin2, it works fine (why?) and replaces rows as needed. When I drop this foreign key constraint (not too easy without a constraint name) and recreate it as "DEFERRABLE", this query works fine also in psql. My question is: Why my query works fine when using pgAdmin, and it fails when using psql? Regards, Tomasz Myrta ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Expressional Indexes
[sNip] >> I have been considering using "calculated index" or "computed index" >> but dunno if that really conveys anything. > > Well, "Expression Indexes" is the most accurate. Or "Expression-Based > Indexes." What is the proposed definition of an "Expression Index?" When I see this term, I get the impression I can create an index that's based on the results of a SELECT, such as for selecting data with specific values or ranges of values... For example, if I want to index on a date field but only have the index keep track of the most recent 30 days (and then create a secondary index for all dates) so as to improve performance on more heavily loaded systems. Am I understanding this new terminology correctly? Thanks in advance. -- Randolf Richardson - [EMAIL PROTECTED] Inter-Corporate Computer & Network Services, Inc. Vancouver, British Columbia, Canada http://www.8x.ca/ This message originated from within a secure, reliable, high-performance network ... a Novell NetWare network. ---(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] Arrays - a good idea?
Hi all, Even though PostgreSQL supports arrays, is it a good idea to use them? I mean, they a) don't conform to the relational model and b) are not transportable so if one is designing an app, should one use them? When should they not/never be used? What are the criteria for justifying their use? Paul... -- plinehan__AT__yahoo__DOT__com C++ Builder 5 SP1, Interbase 6.0.1.6 IBX 5.04 W2K Pro Please do not top-post. ---(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] Arrays - a good idea?
Hello, I don't think there's any reason you should use arrays. You can do everything using tables, and it probably would be easier to use tables instead. Extracting information from arrays is also more difficult if you're using something like JDBC or the connectivity available in PHP. I don't know the criteria of when arrays are necessary, but I cannot think of an example where arrays are absolutely necessary Regards, Yasir On Tue, 18 Nov 2003, Paul Ganainm wrote: > Date: Tue, 18 Nov 2003 22:05:00 - > From: Paul Ganainm <[EMAIL PROTECTED]> > To: [EMAIL PROTECTED] > Subject: [SQL] Arrays - a good idea? > > > > Hi all, > > > Even though PostgreSQL supports arrays, is it a good idea to use them? I > mean, they > > a) don't conform to the relational model > > and > > b) are not transportable > > > so if one is designing an app, should one use them? > > When should they not/never be used? What are the criteria for justifying > their use? > > > Paul... > > > -- > > plinehan__AT__yahoo__DOT__com > > C++ Builder 5 SP1, Interbase 6.0.1.6 IBX 5.04 W2K Pro > > Please do not top-post. > > > ---(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 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
[SQL] Need Help : Query problem
Hi Everybody! If I've a table like below kk | kj | pngk | vote ++--+-- 01 | 01 | a| 10 01 | 01 | b| 10 01 | 01 | c| 10 01 | 02 | a| 10 01 | 02 | b| 10 01 | 03 | a| 10 How do I write a query so that I can get a result as below [ select only a record/s with same kk and kj but different pngk. For example here I've 3 records with same kk=01,kj=01 but diff pngk=a,b,c and 2 records with same kk=01,kj=02 but diff pngk=a,b] kk | kj | pngk | vote ++--+-- 01 | 01 | a| 10 01 | 01 | b| 10 01 | 01 | c| 10 01 | 02 | a| 10 01 | 02 | b| 10 Thanks. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] SOLVED: Emulating 'connect by prior' using stored proc
> I have one question because I'm not clear about > something with your > implementation (a good one too by the looks of it -- thanks > for sharing > this information); if I start my query from an item at level > 5, will the > level be reflected as such, or will it dynamically start at 1? > > As I understand it, in Oracle the level would begin at > 1 in this case. > It's the same in this case. The tree building stuff only ever looks down so the level returned in the query results will start at 1 no matter where you enter your tree. In our case we could enter the tree at 'node' 100 and get the tree below that but the function will start at 1 because we only interested in the data below not the entry point and not where in the tree we entered. As in the solution if you really want to start it at 5 then set that as the value of your second parameter. Cheers, Graeme ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Expressional Indexes
"Randolf Richardson, DevNet SysOp 29" <[EMAIL PROTECTED]> writes: > For example, if I want to index on a date field but only have the index > keep track of the most recent 30 days (and then create a secondary index for > all dates) so as to improve performance on more heavily loaded systems. > > Am I understanding this new terminology correctly? Thanks in advance. No, you could do the above using "partial indexes" but it wouldn't work very well in this case because the "last 30 days" keeps moving and you would have to keep redefining the index periodically. It also wouldn't really help performance. Expression Indexes are just more powerful "functional indexes". In 7.3 they could be used for indexing expressions like "lower(foo)". In 7.4 they're more powerful and you can index expressions other than simple function calls. They still should be things that always return the same value, which excludes subqueries. -- greg ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
