Re: [SQL] Isnumeric function?
Greg Stark <[EMAIL PROTECTED]> writes:
> Theo Galanakis <[EMAIL PROTECTED]> writes:
>
> > I created the Index you specified, however it chooses to run a seq scan on
> > the column rather than a Index scan. How can you force it to use that
> > Index..
> >
> > CREATE INDEX idx_content_numeric ON botched_table(content) WHERE content ~
> > '^[0-9]{1,9}$';
> >
> > select * from botched_table where content = 200::integer
>
> You need to put a "and content ~ '^[0-9]{1,9}$'" in your query, the clause has
> to match the clause in the partial index pretty closely.
Well this is weird. I tried to come up with a cleaner way to arrange this than
the view I described before using a function. But postgres isn't using the
partial index when it seems it ought to be available.
When I say it has to match "pretty closely" in this case I think it would have
to match exactly, however in the case of simple range operators postgres knows
how to figure out implications. Ie, "where a>1" should use a partial index
built on "where a>0".
slo=> create table test (a integer);
CREATE TABLE
slo=> create index idx_text on test (a) where a > 0;
CREATE INDEX
slo=> explain select * from test where a > 0;
QUERY PLAN
Index Scan using idx_text on test (cost=0.00..17.50 rows=334 width=4)
Index Cond: (a > 0)
(2 rows)
slo=> explain select * from test where a > 1;
QUERY PLAN
Index Scan using idx_text on test (cost=0.00..17.50 rows=334 width=4)
Index Cond: (a > 1)
(2 rows)
That's all well and good. But when I tried to make a version of your situation
that used a function I found it doesn't work so well with functional indexes:
slo=> create function test(integer) returns integer as 'select $1' language plpgsql
immutable;
CREATE FUNCTION
slo=> create index idx_test_2 on test (test(a)) where test(a) > 0;
CREATE INDEX
slo=> explain select test(a) from test where test(a) > 0;
QUERY PLAN
--
Index Scan using idx_test_2 on test (cost=0.00..19.17 rows=334 width=4)
Index Cond: (test(a) > 0)
(2 rows)
slo=> explain select test(a) from test where test(a) > 1;
QUERY PLAN
---
Seq Scan on test (cost=0.00..25.84 rows=334 width=4)
Filter: (test(a) > 1)
(2 rows)
I can't figure out why this is happening. I would think it has something to do
with the lack of statistics on functional indexes except a) none of the tables
is analyzed anyways and b) the estimated row count is the same anyways.
--
greg
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Isnumeric function?
Greg Stark <[EMAIL PROTECTED]> writes: > That's all well and good. But when I tried to make a version of your > situation that used a function I found it doesn't work so well with > functional indexes: > ... > I can't figure out why this is happening. You're using 7.3 or older? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Isnumeric function?
Tom Lane <[EMAIL PROTECTED]> writes: > Greg Stark <[EMAIL PROTECTED]> writes: > > That's all well and good. But when I tried to make a version of your > > situation that used a function I found it doesn't work so well with > > functional indexes: > > ... > > I can't figure out why this is happening. > > You're using 7.3 or older? 7.4.3. -- greg ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] aggregate function stddev
Is this a TODO? --- Josh Berkus wrote: > Kemin, > > > Just noticed that the postgres stddev is the stddev_sample formula. > > There are two different ways to calculate this value. > > Their difference is very small with large samle size. It would be nice > > to distinguish the two different versions. > > Note sent to PGSQL-DOCS. > > > I also noticed that oracle has stddev_sample and stddev_population. > > This is just a wish list. > > Were you aware that in PostgreSQL you can write your own aggregates? It's > relatively easy to do. > > -- > Josh Berkus > Aglio Database Solutions > San Francisco > > ---(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 > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] HOW TO HANDLE ZEROS IN DATE FIELD?
I have a project that is taking input from another system. I have certain columns defined as 'Date' Columns. On input I will get '00' in this field which causes the insert to fail. I have read the docs on default and it is unclear to me if this will work. Does anyone have experience in solving this problem. The other alternative I thought of was to write a trigger to fix it ?? James M Doherty [EMAIL PROTECTED] Georgetown, TX 78626 "There is no luck without discipline" IRISH PROVERB ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] SQL99 "with recursive" question
Hello, i think i am to stupid to use the "with recursive" SQL! I have a table: create table tree ( id_tree integer, parent_id integer ... ); In PostgreSQL i use the great connectby() function and in Oracle i simple use select * from tree start with id_tree = 0 connect by parent_id=prior id_tree But how must i use this with "with recursive" function? Every example i found uses two tables. I have only one! Please help me! I need this to use my oracle tables with the same speed in sybase 9.0. - Thomas Wegner Cabrio Meter - The Weather Plugin for Trillian http://trillian.wegner24.de/cabriometer ---(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] PL/pgSQL Function Problem
I am trying to create a function that creates a user and adds a row to a table. It produces no warnings or errors when I create the function but when I attempt to execute it I get a syntax error. I do not understand why this is happening. Any help would be greatly appreciated. SELECT create_author( 'name', 'username', 'password' ); ERROR: syntax error at or near "$1" at character 14 CONTEXT: PL/pgSQL function "create_author" line 7 at SQL statement Here is the code: CREATE OR REPLACE FUNCTION create_author ( VARCHAR(32), VARCHAR(32), VARCHAR(32) ) RETURNS INTEGER AS ' DECLARE name_ ALIAS FOR $1; username_ ALIAS FOR $2; password_ ALIAS FOR $3; authorid_ INTEGER; BEGIN CREATE USER username_ WITH ENCRYPTED PASSWORD password_ IN GROUP authors; INSERT INTO Authors ( Name, Username ) VALUES ( $1, $2 ); SELECT Max( AuthorID ) INTO authorid_ FROM Authors; RETURN authorid_; END; ' LANGUAGE 'plpgsql' SECURITY INVOKER RETURNS NULL ON NULL INPUT; __ Do you Yahoo!? Yahoo! Mail is new and improved - Check it out! http://promotions.yahoo.com/new_mail ---(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] Using UPDATE FROM
I have a sql query which hits 5 database tables. I'll cut a bit out of the results and just show the important values reps_goal reps_actual repsvalue 10 10 1 33 5 1 10 12 1 10 12 1 10 10 1 11 11 1 What I'm trying to do is a single UPDATE statement which will take the values from the reps_actual column, and put them into the repsvalue column. These two columns come from different tables or obviously you could to a simple regular UPDATE. Here is the UPDATE statement: UPDATE programactivitysets SET repsvalue = reps_actual FROM workouts w, workoutactivities wa, workoutactivitysets was, programactivities pa, programactivitysets pas WHERE wa.workout_id = w.workout_id AND was.workoutactivity_id = wa.workoutactivity_id AND pa.programactivity_id = wa.programactivity_id AND pas.programactivity_id = pa.programactivity_id AND pas.set = was.set AND w.workout_id = 6036; After I run that and do a select, these are the results: reps_goal reps_actual repsvalue 10 10 5 33 5 5 10 12 5 10 12 5 10 10 5 11 11 5 For some reason, repsvalue is assigned 5 which is the reps_actual value for the 2nd row. This isn't right. Am I doing something incorrectly or can postgres not handle this kind of UPDATE? Regards, Collin Peters ---(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] CREATE RULE ignored, what did I do wrong
OK, below is the dump of the table definition. Several other tables reference this and
have ON DELETE CASCADE. In this table there is a rule for ON DELETE. The WHERE clause
(NOT old.is_deleted) should always be the case, as the field is FALSE for all existing
entries (checked).
The cascading deletes are all performed when I delete from this table. The rule is
not. The record is NOT retained with is_deleted now TRUE. I turned on log_statement,
and saw only the queries corresponding to the cascading delete, not my DO INSTEAD
queries.
Does the cascade happen first?? If so, how do I get in ahead of it?
Thanks.
***
smoothed_rank_episode_id | integer| not null default
nextval('base_rank_episode_base_rank_episode_id_seq'::text)
base_rank_episode_id | integer| not null
smoothing_id | integer| not null default 0
smoothing_parameters | double precision[] | not null default '{}'::double
precision[]
is_deleted | boolean| default false
Indexes:
"smoothed_rank_episode_pkey" primary key, btree (smoothed_rank_episode_id)
"smoothed_rank_episode_ak1" unique, btree (base_rank_episode_id, smoothing_id,
smoothing_parameters)
Foreign-key constraints:
"$1" FOREIGN KEY (smoothing_id) REFERENCES smoothing_algorithm(smoothing_id) ON
UPDATE CASCADE ON DELETE CASCADE
Rules:
del_smoothed_rank_episode AS ON DELETE TO smoothed_rank_episode WHERE (NOT
old.is_deleted) DO INSTEAD (DELETE FROM historical_rank WHERE
(historical_rank.smoothed_rank_episode_id = old.smoothed_rank_episode_id); DELETE FROM
signal WHERE (signal.signal_episode_id IN (SELECT signal_episode.signal_episode_id
FROM signal_episode WHERE (signal_episode.smoothed_rank_episode_id =
old.smoothed_rank_episode_id))); UPDATE smoothed_rank_episode SET is_deleted = true
WHERE (smoothed_rank_episode.smoothed_rank_episode_id = old.smoothed_rank_episode_id);
)
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] HOW TO HANDLE ZEROS IN DATE FIELD?
On Sep 3, 2004, at 11:36 PM, James M Doherty wrote: I have a project that is taking input from another system. I have certain columns defined as 'Date' Columns. On input I will get '00' in this field which causes the insert to fail. I have read the docs on default and it is unclear to me if this will work. Does anyone have experience in solving this problem. The other alternative I thought of was to write a trigger to fix it ?? 00 is not a valid date, as you are aware :) This is often solved in the application layer by preprocessing the data, changing 00 to NULL, for example. You may be able to do some of this preprocessing in the database itself, first loading the raw data into a temporary table and then transforming it before putting it into the desired table. Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] [GENERAL] PL/pgSQL Function Problem
the inquirer <[EMAIL PROTECTED]> writes: > I am trying to create a function that creates a user > and adds a row to a table. It produces no warnings or > errors when I create the function but when I attempt > to execute it I get a syntax error. I do not > understand why this is happening. > CREATE OR REPLACE FUNCTION create_author ( > VARCHAR(32), VARCHAR(32), VARCHAR(32) ) > RETURNS INTEGER AS ' > DECLARE > name_ ALIAS FOR $1; > username_ ALIAS FOR $2; > password_ ALIAS FOR $3; > authorid_ INTEGER; > BEGIN > CREATE USER username_ WITH ENCRYPTED PASSWORD > password_ IN GROUP authors; Utility statements (which is to say anything except SELECT/INSERT/ UPDATE/DELETE) generally don't cope with parameters. The above won't work because it's trying to substitute parameters for username_ and password_ in the CREATE USER utility statement. You could make it work by constructing the CREATE USER command as a string and then EXECUTE'ing it. (I agree this ain't ideal, but it's where we're at...) regards, tom lane ---(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] Using UPDATE FROM
Collin Peters <[EMAIL PROTECTED]> writes: > Here is the UPDATE statement: > UPDATE programactivitysets SET repsvalue = reps_actual > FROM workouts w, workoutactivities wa, workoutactivitysets was, > programactivities pa, programactivitysets pas > WHERE wa.workout_id = w.workout_id > AND was.workoutactivity_id = wa.workoutactivity_id > AND pa.programactivity_id = wa.programactivity_id > AND pas.programactivity_id = pa.programactivity_id > AND pas.set = was.set > AND w.workout_id = 6036; I think you're imagining that "programactivitysets pas" in the FROM clause is the same as the target table "programactivitysets". It's not, at least not in Postgres, though I've heard tell that MS SQL interprets it that way for some unfathomable reason. Drop the FROM entry and s/pas/programactivitysets/ in the WHERE clause, and I think you'll be good to go ... regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] CREATE RULE ignored, what did I do wrong
[EMAIL PROTECTED] writes: > Foreign-key constraints: > "$1" FOREIGN KEY (smoothing_id) REFERENCES smoothing_algorithm(smoothing_id) ON > UPDATE CASCADE ON DELETE CASCADE > Rules: > del_smoothed_rank_episode AS ON DELETE TO smoothed_rank_episode > WHERE (NOT old.is_deleted) DO INSTEAD ... The DELETE commands generated by the foreign key ON DELETE CASCADE will get rewritten by your ON DELETE rule. You probably do not want to do this; or at least not make it an INSTEAD rule. There has been some debate in the past about whether rules should be able to break foreign-key constraints, but I tend to class it as a "you should know what you're doing" feature. Preventing this kind of error would inevitably result in a serious reduction of the power of the rule feature. regards, tom lane ---(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
