Re: [BUGS] Possible bug in ALTER TABLE RENAME COLUMN (PostgreSQL 7.1.3)
This appears to be fixed: test=> CREATE TABLE sheep (cow text); CREATE test=> CREATE INDEX goat ON sheep (cow); CREATE test=> ALTER TABLE sheep RENAME cow TO chicken; ALTER test=> \d sheep Table "sheep" Column | Type | Modifiers -+--+--- chicken | text | Indexes: goat test=> \d goat Index "goat" Column | Type -+-- chicken | text btree --- > Hi, > > During testing of new revision control code in pgAdmin II, I've found that > renaming an indexed column in a table does not automatically update the > index column name in pg_attribute. pg_get_indexdef does return the correct > definition however: > > helpdesk=# CREATE TABLE sheep (cow text); > CREATE > helpdesk=# CREATE INDEX goat ON sheep (cow); > CREATE > helpdesk=# \d goat >Index "goat" > Attribute | Type > ---+-- > cow | text > btree > > (END) > helpdesk=# ALTER TABLE sheep RENAME cow TO chicken; > ALTER > helpdesk=# \d sheep > Table "sheep" > Attribute | Type | Modifier > ---+--+-- > chicken | text | > Index: goat > > (END) > helpdesk=# \d goat >Index "goat" > Attribute | Type > ---+-- > cow | text > btree > > (END) > helpdesk=# SELECT pg_get_indexdef((SELECT oid FROM pg_class WHERE relname = > 'goat')); > pg_get_indexdef > --- > CREATE INDEX goat ON sheep USING btree (chicken text_ops) > (1 row) > > (END) > > (Please CC any replies as I'm not on this list) > > Regards, Dave. > > -- > Dave Page ([EMAIL PROTECTED]) > http://pgadmin.postgresql.org/ > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] Bug #480: problem with LIKE pattern matches involving % and \_
[EMAIL PROTECTED] writes: > The following pattern matches do not work: > LIKE '%\_%' > LIKE '%\_' > LIKE '\_%' You are short a backslash: what the LIKE operator is seeing is just %_%, etc, which naturally matches anything. You gotta double the backslashes, because one backslash will be eaten by the string-literal parser. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] [GENERAL] Cant get planner to use index (7.1.3-1PGDG)
> Now, Here's where things get weird. > > fdb=> explain SELECT count(*) FROM mfps_orderinfo_435 WHERE order_date = > current_date; > NOTICE: QUERY PLAN: > > Aggregate (cost=13532.12..13532.12 rows=1 width=0) > -> Seq Scan on mfps_orderinfo_435 (cost=0.00..13528.77 rows=1340 > width=0) > > Here it does a straight date compare and it chooses not to use the index. > What?? It's probably deciding that the number of rows (1340) is large enough that the index scan isn't a win. Short form is that due to the way things are structured, after a certain point the index scan becomes more expensive than sequentially scanning the table. > fdb=> explain SELECT count(*) FROM mfps_orderinfo_435 WHERE first_name = > 'SMITH'; > NOTICE: QUERY PLAN: > > Aggregate (cost=1044.16..1044.16 rows=1 width=0) > -> Index Scan using idx_mfps_orderinfo_435_fname on mfps_orderinfo_435 > (cost=0.00..1043.47 rows=279 width=0) > > EXPLAIN > fdb=> explain SELECT count(*) FROM mfps_orderinfo_435 WHERE first_name > like 'SMITH%'; > NOTICE: QUERY PLAN: > > Aggregate (cost=12769.48..12769.48 rows=1 width=0) > -> Seq Scan on mfps_orderinfo_435 (cost=0.00..12769.48 rows=1 width=0) > > EXPLAIN > fdb=> explain SELECT count(*) FROM mfps_orderinfo_435 WHERE first_name > like 'SMITH'; > NOTICE: QUERY PLAN: > > Aggregate (cost=12770.17..12770.17 rows=1 width=0) > -> Seq Scan on mfps_orderinfo_435 (cost=0.00..12769.48 rows=279 width=0) > > EXPLAIN > > Here it will do an index scan if and only if I use the '=' operator. If I > use like with the % at the end of the string or EVEN if I have no wild card > at all... it still does a seq scan. If anyone has any advice on how to > get these indexes working properly, please let me know. You may want to check your locale setting. IIRC, if you're running with locale enabled and not in C locale, LIKE does not get optimized to run with indexes. ---(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
