[SQL] Need a SQL to create sets of hobbies

: CREATE TABLE x2 (sid INTEGER, hobby TEXT, PRIMARY KEY(sid,hobby)); and makes x2 contain rows: sid | hobby ---+-- 1 | music 1 | arts 2 | computer 3 | arts 3 | football 4 | computer 4 | music where gid starts from 1. Thank you in advance! CN -

Re: [SQL] Creating Index

Hi! I thought I have got no more question in this thread, and I was wrong :-( Is the extra 300ms in UNION essential? Best Regards, CN --This table contains 1036 rows. CREATE TABLE table1 ( c1 VARCHAR(20) PRIMARY KEY, c2 "char" )WITHOUT OIDS; - --This table con

Re: [SQL] Creating Index

ut 7.3 just > punts if *any* of the UNION columns have inconsistent datatypes. Apparently this postgreSQL beast has always been well under control by the fingers of you genious developers! Long live the king! Ooops! I'm sorry! Please pardon my English! Long live postgreSQL, the no. 1 D

Re: [SQL] Creating Index

0..40.30 rows=0 loops=1) > >Sort Key: table2.c1 > >-> Seq Scan on table2 (cost=0.00..22.50 > >rows=333 width=56) (actual > > time=38.65..38.65 rows=0 loops=1) > > Filter: (c3 > 2003) > > -> Subquery Scan "*SELECT* 2" (cost=0.00..22.50 rows=333 > > width=8) (actual time=0.02..0.02 rows=0 > > loops=1) > >-> Seq Scan on table4 (cost=0.00..22.50 rows=333 > >width=8) (actual time=0.01..0.01 rows=0 > > loops=1) > > Filter: (c1 > 2003) > > Total runtime: 41.86 msec Best Regards, CN -- http://www.fastmail.fm - mmm... Fastmail... ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] Creating Index

rows. Again, I would much appreciate any idea helping me speed up view1. One off-topic issue is that I wish postgresql could be smarter to make use of the index even there are INT2 columns in composit index. As my case shows, INT2 is big enough for columns year and month, isn't it? Best R

Re: [SQL] help with rule and notification

rement in an application server in 3 tier environment is not too difficult. Regards, CN -- http://www.fastmail.fm - Or how I learned to stop worrying and love email again ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [SQL] Creating Index

helps and is available, then someone please show me how to build that index like: CREATE INDEX i1 ON table1 Is creating a function that eats DATE as argument to build that index my only solution? Best Regards, CN -- http://www.fastmail.fm - The professional email service

Re: [SQL] Creating Index

23..89.18 rows=9429 loops=1) -> Subquery Scan "*SELECT* 2" (cost=0.00..0.00 rows=1 width=156) (actual time=0.03..0.03 rows=0 loops=1) -> Seq Scan on table3 (cost=0.00..0.00 rows=1 width=156) (actual tim

Re: [SQL] Creating Index

Peter, Thanks a lot! > Unqualified count() cannot use an index because it has to visit all the > rows in the table. It is only for my test. In my real practice, queries like "SELECT * FROM view1 WHERE year = 2003 AND month BETWEEN 10 AND 12" will be performed. > Then again, I don't quite believe

[SQL] Creating Index

smart enough to apply its usage explained in the manual. I would much appreciate if anyone could show me how to build that index something similar to (I guess) the following query (which is illegal of course): CREATE INDEX index1 ON table1 EXTRACT(YEAR FROM d) || EXTRACT(MONTH FROM d); TIA CN

Re: [SQL] Extraordinary Full Join

| Edu A Do I have any chance to achieve the ultimate goal? I need, at least, the correct result for the worst case like: id | name | book | hobby ---+--++--- m1 | John | book1 | m1 | John | | hobby2 m1 | John | book2 | m1 | John || hobby1 Regards, CN

[SQL] Extraordinary Full Join

s? Even better, if possible, "sort" book and hobby column descendently to fill up null columns: id | name | book | hobby +--++--- m1 | John | book1 |hobby1 m1 | John | book2 |hobby2 What is the better-have SQL to produce the last furnished list? TIA CN -- http://www.f

[SQL] Which Approach Performs Better?

o me to understand. Thank you in advance for any input! Regards, CN -- http://www.fastmail.fm - A no graphics, no pop-ups email service ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[SQL] Two Permance Questions

Hi! Q1. Is subquery better or join? For subquery: SELECT t1.c1,(SELECT t2.c2 FROM t2 WHERE t2.c1 = t1.c1) FROM t1 I wonder it will loop n times if t1 returns n rows. If this is the case, is it better to use join like this: SELECT t1.c1,t2.c2 FROM t1,t2 WHERE t2.c1 = t1.c1 Q2. If the query i

[SQL] How To Get Bytea Data Instead Of Its Oid

Greetings! I must have missed lesson 1 of postgresql! create table test (c1 text,c2 bytea); Then, I probably have successfullyi nserted several records into test using C++Builder. Now I am trying to retrieve back the binary data in c2 I just inserted. However, the SQL statement issued by both

[SQL] How To Increment A Field Value By Function

for this (LOCK TABLE fails in function)? Regards, CN