[SQL] unsubscribe
unsubscribe
Re: [SQL] index find method?
Hello, The Default method is btree, that is the commonly method used in the indexes. Rafael Domiciano DBA Postgres Senffnet 2008/6/18 Jorge Medina <[EMAIL PROTECTED]>: > hi list, > when I do: > CREATE INDEX name_index ON some_table (some_col); > what method(hash,btree,rtree,etc.) use by default? > > -- > Jorge Andrés Medina Oliva. > Systems Manager and Developer. > BSDCHiLE. > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >
Re: [SQL] Case Insensitive searches
I have read the article... tnks, very helpful. But, can I create a index using function like "substring"? I would like to create something like this: CREATE INDEX indtest_01 ON table_01 ((SUBSTRING(month_year, 3, 4) || SUBSTRING(month_year, 1, 2)) 2008/8/4 Terry Lee Tucker <[EMAIL PROTECTED]> > On Monday 04 August 2008 11:09, Frank Bax wrote: > > Terry Lee Tucker wrote: > > > On Monday 04 August 2008 10:05, Richard Broersma wrote: > > >> On Mon, Aug 4, 2008 at 6:54 AM, Mike Gould <[EMAIL PROTECTED]> > wrote: > > >>> In some db's if you > > >>> use a lower() or upr() it will always do a table scan instead of > using > > >>> a index > > >> > > >> True, this would also happen in PostgreSQL. However, you can overcome > > >> this by creating a "functional" index: > > >> > > >> > http://www.postgresql.org/docs/8.3/interactive/indexes-expressional.html > > >> > > >> This way all expression using where lower( column ) = 'a'. will always > > >> use an index scan. > > > > > > What about using the operator, ~* ? > > > > > > Does that cause a table scan as well? > > > > Whether or not any query uses an index scan or seq scan depends on many > > factors and is not always easily predictable. > > > > Richard's statement about "will always use an index scan" is not > > universally true. If the table is very small; a index scan is NOT used. > > Table statistics could also indicate a seq scan is more efficient > > (suppose 99% of rows had column='a'). > > > > The ~* operator is very likely to scan the entire table because it will > > look for 'A' anywhere in the column (and will therefore match 'Joanne'; > > and I doubt that there is special code to handle case where length of > > argument is exactly the same as column. However; ~* '^a' which anchors > > search to first character is perhaps more likely to use an index scan. > > > > Frank > > Frank, > > Thanks for the response. Actually, from within the applicaion, we use ~* > and > it is anchored with whatever they've typed in the widget as search > criteria. > > Anyway, thanks for the helpful response... > -- > Terry Lee Tucker > Turbo's IT Manager > Turbo, division of Ozburn-Hessey Logistics > 2251 Jesse Jewell Pkwy NE > Gainesville, GA 30501 > Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987 > [EMAIL PROTECTED] > www.turbocorp.com > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >
[SQL] Doubts about FK
Hi there, In my DB I have a couple of FK, so the change of referenced columns is a quite complicated. Today, I DISABLE TRIGGER ALL on the tables envolved and then ENABLE TRIGGER ALL to back them. Is there a better way to do that? I read something about SET CONSTRAINTS ALL DEFFERED, but I don't know how to use it. Regards, Rafael Domiciano
Re: [SQL] Doubts about FK
2008/9/12 Scott Marlowe <[EMAIL PROTECTED]> > On Fri, Sep 12, 2008 at 12:14 PM, Rafael Domiciano > <[EMAIL PROTECTED]> wrote: > > Hi there, > > > > In my DB I have a couple of FK, so the change of referenced columns is a > > quite complicated. > > Today, I DISABLE TRIGGER ALL on the tables envolved and then ENABLE > TRIGGER > > ALL to back them. > > Is there a better way to do that? > > Depends. Are other people connected to the server when you do it? > disable trigger disables the triggers for everybody, not just you if I > remember correctly. If other folks are using the db, then they can > insert bad data during that period. I do it in a transaction, so there's no problem about the other folks...! > > I read something about SET CONSTRAINTS ALL DEFFERED, but I don't know how > to > > use it. > > the constraints have to created as deferrable to do that. then, in a > transaction, you can do something like: > Can I turn my FK into deferrable FK only in that transaction? > > begin; > update in a way that would normally violate an FK > insert in a way that fixes the previous statement's FK relationship > commit; > > and it will work as long as the constraints all make sense by the time > you get to commit. That's what I wanna to do! > > > Note that unique constraints are not deferrable in pgsql. >
Re: [SQL] Can COPY update or skip existing records?
Hi there, The operation "on_duplicate_key_update" is in implementation on the new version of Postgres :) The only way (or, at least, the best way... I think) to do what you want is using a temporary table... let's see: /* Creating the structure of the first table (table_01)... You can do it the way you like */ create table temp_01 as (select * from table_01 limit 1); TRUNCATE TABLE table_01; /* COPY */ COPY temp_01 FROM '/tmp/table'; /* Insert the values */ insert into table_01 a where not exists (select 1 from temp_01 b where a.cod_serial = b.cod_serial) /* Or you could do like this */ delete from temp_01 a where exists (select 1 from table_01 b where a.cod_serial = b.cod_serial) I hope being helpful. Best Regards, Rafael Domiciano Postgres DBA 2008/9/30 Glenn Gillen <[EMAIL PROTECTED]> > Hey all, > > I've got a table with a unique constraint across a few fields which I > need to regularly import a batch of data into. Is there a way to do it > with COPY without getting conflicts on the unique contraint? I have no > was of being certain that some of the data I'm trying to load isn't in > the table already. > > Ideally I'd like it to operate like MySQL's on_duplicate_key_update > option, but for now I'll suffice with just ignoring existing rows and > proceeding with everything else. > > Thanks, > > -- > Glenn > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >
Re: [SQL] Trigger/Function - one solution - was constraint question (I think)
I would develop like this (No so many changes, it is basically a small trigger) create or replace function compound_rows_range_check() returns trigger as $body$ DECLARE BAYNO int4; BEGIN -- First Verification = if changing compound or row fail IF (old.co_id <> new.co_id or old.cr_id <> new.cr_id) THEN RAISE EXCEPTION 'Cannot change co_id () | cr_id ()', old.co_id, old.cr_id; END IF; -- Last Verification SELECT cb_id into BAYNO from compound_bays where co_id = NEW.co_id and cr_id = NEW.cr_id and cb_id > NEW.cr_length order by cb_id desc limit 1; IF (FOUND) THEN RAISE EXCEPTION 'Cannot remove occupied bays: % > %', BAYNO, NEW.cr_length; END IF; RETURN NEW; END; $body$ LANGUAGE 'plpgsql'; 2008/12/4 Gary Stainburn <[EMAIL PROTECTED]> > I have managed to develop one solution using functions and triggers. Has > anyone got a better solution? > > Gary > > create unique index "compound_bays_unique_index" on compound_bays using > btree > (co_id,cr_id,cb_id); > > create or replace function compound_rows_range_check() returns trigger as > $proc$ > DECLARE > BAYNO int4; > BEGIN > -- if changing compound or row fail > IF NEW.co_id <> OLD.co_id THEN >RAISE EXCEPTION 'cannot change compound id'; > END IF; > IF NEW.cr_id <> OLD.cr_id THEN >RAISE EXCEPTION 'cannot change row id'; > END IF; > SELECT cb_id into BAYNO from compound_bays where > co_id = NEW.co_id and > cr_id = NEW.cr_id and > cb_id > NEW.cr_length > order by cb_id desc > limit 1; > IF found THEN >RAISE EXCEPTION 'Cannot remove occupied bays: % > %', > BAYNO, NEW.cr_length; > END IF; > RETURN NEW; > END; > $proc$ LANGUAGE plpgsql; > > CREATE TRIGGER compound_rows_range_check BEFORE UPDATE on compound_rows > FOR EACH ROW EXECUTE PROCEDURE compound_rows_range_check(); > > create or replace function compound_bays_range_check() returns trigger as > $proc$ > DECLARE > ROWLENGTH int4; > BEGIN > SELECT cr_length into ROWLENGTH from compound_rows where > co_id = NEW.co_id and > cr_id = NEW.cr_id; > IF not found THEN >RAISE EXCEPTION 'Compound / Row not found'; > END IF; > IF NEW.cb_id > ROWLENGTH THEN >RAISE EXCEPTION 'row length exceeded: % > %', > NEW.cb_id,ROWLENGTH; > END IF; > RETURN NEW; > END; > $proc$ LANGUAGE plpgsql; > > CREATE TRIGGER compound_bays_range_check BEFORE INSERT OR UPDATE on > compound_bays > FOR EACH ROW EXECUTE PROCEDURE compound_bays_range_check(); > > > > -- > Gary Stainburn > > This email does not contain private or confidential material as it > may be snooped on by interested government parties for unknown > and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >
[SQL] Diferent databases on same query...
Hi, I'd like to do a query where can be possible I access tables from diferent databases on the same query. On MySQL, I do: SELECT People1.PersID, People1.Name, Result1.Value, Result1.Date FROM Database1.People1, Database2.Result1 WHERE ... I think on ORACLE works like as: SELECT People1.PersID, People1.Name, Result1.Value, Result1.Date FROM People1@Database1, Result1@Database2 WHERE ... But Who I can to do this on Postgresql ? Thank you. Douglas Rafael. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Diferent databases on same query...
>> Hi, >> >> I'd like to do a query where can be possible I access tables from >> diferent databases on the same query. >> On MySQL, I do: >> >> SELECT People1.PersID, People1.Name, Result1.Value, Result1.Date FROM >> Database1.People1, Database2.Result1 WHERE ... >> >> I think on ORACLE works like as: >> >> SELECT People1.PersID, People1.Name, Result1.Value, Result1.Date FROM >> People1@Database1, Result1@Database2 WHERE ... >> >> But Who I can to do this on Postgresql ? >> > >You CANNOT do that with PostgreSQL. >But why do you want to do that? IMHO it's a rather bas design to hold data >in different places, if you need to select them in one query. >Is there a real reason to hold the tables in different databases? > >Andre Hi, Andre ! Maybe yes, maybe not. I have 6 companies of the same group, little but they are like as, and they share the main database. But there are data that are specific of each one. The design of tables is the same for all them. So, on mysql I had on diferent databases. Thus, on the same query, I select the name of employee, the truck and invoice of the specific company. Two companies have server separated. How can I do to do distribute database server with postgres ? You have a suggestion ? Thank you, Douglas. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] query optimizer dont treat correctly OR
Hello folks See the command bellow. I use some thing simmilar about an decade on Oracle, Sybase, MSSQL, DB2, etc. But with Postgresql , he generate an FULL TABLE SCAN, and consequenyly it take about 10 minutes to run (Very big table..) -- SELECT A.SR_RECNO , A.CDEMP, A.NRPED,A.SR_RECNO FROM FTB01 A WHERE ( A.CONTROLE <= ' ' AND A.CDEMP = '75' AND A.NRPED < '261' ) OR ( A.CONTROLE = ' ' AND A.CDEMP < '75' ) OR ( A.CONTROLE < ' ' ) ORDER BY A.CDEMP DESC, A.NRPED DESC, A.SR_RECNO DESC LIMIT 170 -- Otherwise, is i write the query on the form of an little more "dummy" and eliminating the "OR" and changing by UNION, the time of execution drops to less menos of two seconds -- SELECT TMP1.* FROM ( SELECT A.SR_RECNO, A.CDEMP, A.NRPED,A.SR_RECNO FROM FTB01 A WHERE ( A.CONTROLE <= ' ' AND A.CDEMP = '75' AND A.NRPED < '261' ) ORDER BY A.CDEMP DESC, A.NRPED DESC, A.SR_RECNO DESC LIMIT 170 ) TMP1 UNION SELECT TMP2.* FROM ( SELECT A.SR_RECNO, A.CDEMP, A.NRPED,A.SR_RECNO FROM FTB01 A WHERE ( A.CONTROLE = ' ' AND A.CDEMP < '75' ) ORDER BY A.CDEMP DESC, A.NRPED DESC, A.SR_RECNO DESC LIMIT 170 ) TMP2 UNION SELECT TMP3.* FROM ( SELECT A.SR_RECNO, A.CDEMP, A.NRPED,A.SR_RECNO FROM FTB01 A WHERE OR ( A.CONTROLE < ' ' ) ORDER BY A.CDEMP DESC, A.NRPED DESC, A.SR_RECNO DESC LIMIT 170 ) TMP3 ORDER BY CDEMP DESC, NRPED DESC, SR_RECNO DESC LIMIT 170 -- The comand above works (even being 10 x slower then other Databases ) with our generate the full scan. Why Post do this wrong julgment with the initial command? Exist some thing that i can configure to to make postgres works correctly ? Obs.: * Tested on versions 7.3.2 e 7.4.1 * Obvialy the vacuumm full analyse was executed Thanks Luiz ---(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] number os commands inside transaction block
Dear Friends how i can increse the number of commands in an transaction block i use postgres 7.4.5 on linux Regards Luiz - Original Message - From: "Stephan Szabo" <[EMAIL PROTECTED]> To: "Don Drake" <[EMAIL PROTECTED]> Cc: Sent: Monday, January 31, 2005 7:31 PM Subject: Re: [SQL] plpgsql functions and NULLs On Sun, 30 Jan 2005, Don Drake wrote: OK, I have a function that finds records that changed in a set of tables and attempts to insert them into a data warehouse. There's a large outer loop of candidate rows and I inspect them to see if the values really changed before inserting. My problem is that when I look to see if the row exists in the warehouse already, based on some IDs, it fails when an ID is NULL. The ID is nullable, so that's not a problem. But I'm forced to write an IF statement looking for the potential NULL and write 2 queries: IF omcr_id is null select * from WHERE omcr_id is NULL AND ... ELSE select * from WHERE omcr_id=candidate.omcr_id AND END IF; Hmm, perhaps some form like: WHERE not(candidate.omcr_id is distinct from omcr_id) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.8.2 - Release Date: 28/1/2005 ---(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] number os commands inside transaction block
Hi Michael Fuhr how i can increse the number of commands in an transaction block What do you mean? What problem are you trying to solve? i´m trying to solve the follow message current transaction is aborted, queries ignored until end of transaction block some one tell me this is defined inside postgres sources i recive this message when i execute an certain number of queries inside an begin/commit block Regards Luiz ---(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] problem with postgres
Dear friends I hope some one can help me Server machine celeron 2.4Gb with 512 Ram postgres 7.4.5 conectiva 8 with kernel 2.4.19 i´m getting many message of Erro:canceling query due to user request how to solve this i even get with an just booted up server, and running vacuum analyze verbose inside psql with no other people connected the only changes i has on postgresql.conf is max_connection=512 shared_buffers=8192 sort_mem=8192 vacuum_mem=31792 Regards Luiz Rafael ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] problem with postgres
Dear friends I hope some one can help me Server machine celeron 2.4Gb with 512 Ram postgres 7.4.5 conectiva 8 with kernel 2.4.19 i´m getting many message of Erro:canceling query due to user request how to solve this i even get with an just booted up server, and running vacuum analyze verbose inside psql with no other people connected the only changes i has on postgresql.conf is max_connection=512 shared_buffers=8192 sort_mem=8192 vacuum_mem=31792 Regards Luiz Rafael ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
