Re: [SQL] how do i import my sql query result to a file
--- Joseph Syjuco <[EMAIL PROTECTED]> wrote: > how do i import results of my select query to a file > thanks > in the psql command prompt type \o and then type your select query. The result will be dumped into ludwig. __ Do You Yahoo!? Yahoo! Autos - Get free new car price quotes http://autos.yahoo.com ---(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] negative queries puzzle
--- Jinn Koriech <[EMAIL PROTECTED]> wrote: > hi all, > but then to get the entirely new items out i use a > sub query which takes > for ever > > SELECT DISTINCT * FROM v_postcode_new WHERE postcode > NOT IN ( SELECT > postcode FROM v_postcode_old ) ORDER BY postcode > ASC; > > does anyone know of a quicker way to accomplish > this? Try using the "NOT EXIST" clause instead of the "NOT IN". The "EXIST" clause utilizes the index while the "IN" does not utilizes index (i.e. uses sequential scan therefore it is much slower). SELECT DISTINCT * FROM v_postcode_new WHERE NOT EXIST( SELECT postcode FROM v_postcode_old WHERE v_postcode_new.postcode = v_postcode_old.postcode) ORDER BY postcode ASC; ludwig. __ Do You Yahoo!? Yahoo! Health - Feel better, live better http://health.yahoo.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Table Sorting and Limit Question
--- Dawn Hollingsworth <[EMAIL PROTECTED]> wrote: > > > Currently we have a table with a sequence number( id > ) as a primary key, > a date field which is indexed and several other > columns. The user > interface allows the user to sort the data by date > and limits the result > set to 100 rows. > > The question is: > The user interface needs the capability to sort the > table by date but > pull out the hundred row set that contains id > say...542 for example. > > What would be the best way to do this taking into > account this table is > several hundred thousand rows? try also to index the id. Try : Select * from where id= order by date limit In this case you is 100 since you want to return at most 100 rows. hope that helps. ludwig. __ Do You Yahoo!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Need Help for select
--- Andre Schubert <[EMAIL PROTECTED]> wrote: > Hi all, > > i need help to build a select query or > plpgsql-fucntion > for the following tables. >> Is it possible to build a select query that selects > d.name for each a.name where > a.id = b.a_id and d.id = c.d_id and each b.c_id must > exist in c.b_id. > > Example: > a: b: c : d: > id | name a_id | c_idb_id | d_idid | > name > |--- ---|- ---|- > -| > 1 | A_Name11 | 1 1 | 1 1 | > D_Name1 > 2 | A_Name21 | 2 2 | 1 2 | > D_Name2 > 3 | A_Name32 | 1 3 | 2 3 | > D_Name3 > 4 | A_Name43 | 3 4 | 2 > 3 | 4 5 | 3 >4 | 5 > > i wish to have to following result: > | > A_Name1 | D_Name1 > A_Name3 | D_Name2 > A_Name4 | D_Name3 > > I hope someone could understand the problem You can use views to to simplify complicated queries Create a view that will join table A & B Create view view_ab(name,id) as select name,c_id from a,b where id = c_id; Create a view that will join table C & D Create view view_cd(name2,id2) as select name,b_id from c,d where id=d_id; Create a query that will join the views "view_ab" and "view_cd" Select name,name2 from view_ab,view_cd where id=id2; __ Do You Yahoo!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Trigger/Function problem
--- Andreas Johansson <[EMAIL PROTECTED]> > > ERROR: CreateTrigger: function fix_status() does > not exist Andreas : > > Why doesn't the trigger acknowledge that I want to > call fix_status with a > parameter for which table name I should use? > > I'm completely stuck and I someone out there can > help me. - Trigger procedures cannot have paramaters. Use the variable TG_ARGV[] to access the arguments passed to the trigger. ludwig. __ Do You Yahoo!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] reset sequence
--- Ricardo Javier Aranibar León
<[EMAIL PROTECTED]> wrote:
> Hi List,
>
> I need that somebody help me.
> First, I use PostgreSQL 7.0.3 for linux.
> I need reset my sequence but that it begin in 1
> again when a use nextval.
> And I have been search in
> http://archives.postgresql.org/ about reset
> sequence but I obtain this information:
>SELECT setval('name_sequence', 1, false);
try SELECT setval('name_sequence',1);
Maybe setval(,,) doesn't exist in
7.0.3.
regards,
ludwig lim
__
Do you Yahoo!?
Yahoo! News - Today's headlines
http://news.yahoo.com
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Tuning complicated query
Hi: Attached to the e-mail is the body of the query and the result of the EXPLAIN (Sorry for not placing the query and EXPLAIN in the e-mail body . The query is rather complicated and the EXPLAIN result is rather long ). The file demo.out.3 is the result of the EXPLAIN The file demo.sql is the sql statement. I would like your opinion on how to tune the query as posted in the attachment Note that I have indexes on the all the column customer_id on both sc_customer_attr and sc_add_points. I am wondering why sequential scan was used the on the clause a.customer_id = b.customer_id since the previous join condition has an "exist" subquery with LIMIT with filters out unneccesary customer_id before performing the join (a_customer_id = b.customer_id). Also I was wondering why the number of rows in the last sequential scan is still 7 million plus (most of the should already have been elimated by the subquery). Note that before the executing the query, the database has been VACUUMed and ANALYZEd. The result of EXPLAIN ANALYZE is almost similar to one produce by issuing the EXPLAIN. Any hints on tuning the query? thank you ludwig __ Do you Yahoo!? New DSL Internet Access from SBC & Yahoo! http://sbc.yahoo.com psql:demo.sql5:31: NOTICE: QUERY PLAN: Limit (cost=602630531.21..602630531.21 rows=10 width=69) -> Sort (cost=602630531.21..602630531.21 rows=218145 width=69) -> Aggregate (cost=602583597.46..602609774.87 rows=218145 width=69) -> Group (cost=602583597.46..602596686.16 rows=2181451 width=69) -> Sort (cost=602583597.46..602583597.46 rows=2181451 width=69) -> Merge Join (cost=602162862.44..602184219.45 rows=2181451 width=69) -> Sort (cost=600998172.01..600998172.01 rows=52125 width=49) -> Merge Join (cost=600994410.42..600994904.87 rows=52125 width=49) -> Sort (cost=13.25..13.25 rows=302 width=25) -> Seq Scan on sc_attr c (cost=0.00..3.30 rows=302 width=25) -> Sort (cost=600994397.17..600994397.17 rows=64485 width=24) -> Seq Scan on sc_customer_attr a (cost=0.00..600990276.11 rows=64485 width=24) SubPlan -> Limit (cost=736.25..736.25 rows=1 width=20) -> Subquery Scan z (cost=736.25..736.25 rows=1 width=20) -> Limit (cost=736.25..736.25 rows=1 width=20) -> Sort (cost=736.25..736.25 rows=1 width=20) -> Aggregate (cost=0.00..736.24 rows=1 width=20) -> Group (cost=0.00..736.24 rows=1 width=20) -> Index Scan using xie2sc_add_points on sc_add_points d (cost=0.00..736.24 rows=1 width=20) -> Sort (cost=1164690.44..1164690.44 rows=7354200 width=20) -> Seq Scan on sc_add_points b (cost=0.00..138679.20 rows=7354200 width=20) explain select count(distinct(b.customer_id)) as members, sum(b.total_loyalty) as sales, count(b.customer_id) as visits, c.attr_cd, c.attr_type_cd, c.description as description fromsc_customer_attr a, sc_add_points b, sc_attr c whereexists (select z.customer_id from (select d.customer_id, sum(d.total_loyalty) as points from sc_add_points d where d.transdate >= 19980100.00 and d.transdate <= 20020931.00 and d.company_cd = 1 and d.branch_cd = 13 and a.customer_id = d.customer_id group by d.customer_id order by points desc limit 100 ) as z ) and a.attr_cd = c.attr_cd and a.attr_type_cd = c.attr_type_cd and a.attr_type_cd = 2 and a.company_cd = c.company_cd and
[SQL] Temporary tables and indexes
Hi : Are the indices of a temporary table automatically "dropped" together its corresponding temporary table after a database session? ludwig. __ Do you Yahoo!? New DSL Internet Access from SBC & Yahoo! http://sbc.yahoo.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Viewing stored procedure code
Hi : 1) How do I view the body of a stored procedure in psql? 2) How do I know get the corresponding stored procedure of a particular trigger in psql? thanks in advance, ludwig. __ Do you Yahoo!? New DSL Internet Access from SBC & Yahoo! http://sbc.yahoo.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Slow performance on MAX(primary_key)
Hi Keith: --- Keith Gray <[EMAIL PROTECTED]> wrote: > Help, > > I have just been comparing some large table > performance > under 7.1 using the > > select max(primary key)from table; > > We are using this for various functions including > sequence. > Try using the following as alternative : SELECT primary_key FROM table ORDER BY primary_key desc LIMIT 1; This should work if primary_key is indexes. As of now, Max() doesn't utilizes the indices hence it always do a sequential scan. Hope that helps, regards, ludwig. __ Do you Yahoo!? New DSL Internet Access from SBC & Yahoo! http://sbc.yahoo.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Locking that will delayed a SELECT
Hi: Suppose I have a transaction (T1) which executes a complicated stored procedure. While T1 is executing, trasaction #2 (T2) begins to execute. T1 take more time to execute that T2 in such a way that T2 finished earlier than T1. The result is that t2 returns set of data before it can be modified by T1. Given the above scenario. Is there a way such that while T2 will only read that value updated by T1 (i.e. T2 must wait until T1 is finished) ? What locks should I used since a portion of T1 contains SELECT statements? Should I used the "SERIALIZABLE isolation". Thank you in advance. ludwig. __ Do you Yahoo!? New DSL Internet Access from SBC & Yahoo! http://sbc.yahoo.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] getting the current date
--- Joseph Syjuco <[EMAIL PROTECTED]> wrote: > > how can i get the current date (without the time > part) in sql. --> try SELECT current_date(); ludwig. __ Do you Yahoo!? New DSL Internet Access from SBC & Yahoo! http://sbc.yahoo.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] plpgsql cursors : dynamic or static?
Hi: Are cursors in plpgsql dynamic or static? For example : ... /* some code */ FOR rec in SELECT f1,f2 FROM table1 WHERE LOOP /* some codes that manipulate table1 */ END LOOP; Do the result set pointed to by the cursor remains the same even if performed some data manipulation inside the FOR..LOOP? Thank you in advance, ludwig. __ Do you Yahoo!? New DSL Internet Access from SBC & Yahoo! http://sbc.yahoo.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Simulating a SELECT..FOR UPDATE to LOCK and SELECT statement
Hi: Is there a way to emulate a SELECT..FOR UPDATE to series of LOCK/SELECT statement. I tried the following statements using 2 psql terminals. T1 | T2 1)BEGIN; | 2)SELECT x | BEGIN; FROM y | WHERE y=1 | FOR UPDATE;| 3) | SELECT x | FROM y | WHERE y=1 | FOR UPDATE; 4)COMMIT;| 5) | COMMIT; At point #3 T2 will wait, however changing the WHERE clause to other clause such as "WHERE y=2" will allow T2 to proceed. - I tried changing the SELECT..FOR UPDATE into LOCK SHARE MODE followed by a SELECT (but w/o FOR UPDATE) but it T2 is allowed to proceed even for the clause "where y=1". I am surprised because according to the docs (version 7.2), it says: ROW SHARE MODE Note: Automatically acquired by SELECT ... FOR UPDATE. I'm assuming that the SELECT..FOR UPDATE performs a lock in ROW SHARE MODE before the SELECT. I also tried changing the lock mode into SHARE ROW EXCLUSIVE MODE / EXCLUSIVE MODE but still T2 won't be allowed to passed even the for the clause "where y=2". Is there any to do it? I'm asking becuase our db libaries (using libpq) always a cursor when generating a SELECT statement thus I'm encourtering the following error message when I use SELECT..FOR UPDATE: Cursor must be READ ONLY. DECLARE/UPDATE is not supported. Another alternative would be studying libpq and removing the cursors in a SELECT statement. By the way is there any side effect / disadavtages when I remove the "DELCARE CURSOR" statement and change it to plain SELECT statememt in C++ codes? Thank you in advance, ludwig. __ Do you Yahoo!? New DSL Internet Access from SBC & Yahoo! http://sbc.yahoo.com ---(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] Locking that will delayed a SELECT
--- Tom Lane <[EMAIL PROTECTED]> wrote: > A simple answer is to have T1 grab an ACCESS > EXCLUSIVE lock on some > table to block T2's progress. If that locks out > third-party > transactions that you'd rather would go through, you > can probably use > a lesser form of lock --- but then both T1 and T2 > will have to cooperate > since each will need to explicitly take a lock. - Is there a possibility of having a lock that similar to a row level ACCESS EXCLUSIVE (i.e. ROW ACCESS EXCLUSIVE lock) in the future release of PostgreSQL? The ACCESS EXCLUSIVE lock also locks the rows not used in T1, making concurrent transactions almost impossible. regards, ludwig __ Do you Yahoo!? New DSL Internet Access from SBC & Yahoo! http://sbc.yahoo.com ---(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] Locking that will delayed a SELECT
--- Tom Lane <[EMAIL PROTECTED]> wrote: > Achilleus Mantzios <[EMAIL PROTECTED]> > writes: > >> The problem is solved > >> > >> a) Using SERIALIZABLE XACTION ISOLATION LEVEL > >> b) in T2 using "select for update" instead of > select. That way T2's > >> queries will wait untill T1's statements commit > or rollback. > > ISTM that SERIALIZABLE mode will not solve this > problem, since by > definition you want T2 to see results committed > after T2 has started. > > A simple answer is to have T1 grab an ACCESS > EXCLUSIVE lock on some > table to block T2's progress. If that locks out > third-party > transactions that you'd rather would go through, you > can probably use > a lesser form of lock --- but then both T1 and T2 > will have to cooperate > since each will need to explicitly take a lock. > If I will be using ACCESS EXCLUSIVE lock, should I should SELECT statement only in T1 instead SELECT...FOR UPDATE statement since SELECT...FOR UPDATE uses ROW SHARE MODE lock since the ACCESS EXCLUSIVE lock is in conflict with other lock mode (besides it is pointless to use other locks when using ACCESS EXCLUSIVE lock) ? *** For clarification *** In the SQL command reference of PostgreSQL: in SELECT statement section : "The FOR UPDATE clause allows the SELECT statement to perform exclusive locking of selected rows" in LOCK statement section : "ROW SHARE MODE Note: Automatically acquired by SELECT ... FOR UPDATE." - Isn't this two statements somewhat conflicting? Is the PostgreSQL meaning of SHARE lock and EXCLUSIVE LOCK similar to the definition of Elmasri/Navathe in the book "Fundamentals of Database Systems" where a SHARE lock is a "read lock", while an EXCLUSIVE lock is a "write lock"? Thank you in advance. ludwig. __ Do you Yahoo!? New DSL Internet Access from SBC & Yahoo! http://sbc.yahoo.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Restricting a VIEW.
--- Terry Yapt <[EMAIL PROTECTED]> wrote:
> Hello all,
>
> I have a doubt. In the next example, I have a table
> with two columns:
> - DATE
> - MONEY
>
> And a VIEW which SUM's the money GROUPing by
> 'month/year' (I cut off the day)...
>
> Ok.. I would like to be able to SELECT * FROM VIEW..
> but restricting by complete dates (dd/mm/)...
> (Last select in the example)
>
> I think it isn't possible, but I would like to know
> your opinion... Or if there is any workaround...
>
> Best regards..
>
> --==
> DROP TABLE ty_test;
> CREATE TABLE ty_test
> (datein date NOT NULL,
>money numeric(6,2) NOT NULL,
> PRIMARY KEY (datein)
> ) WITHOUT OIDS;
>
> INSERT INTO ty_test VALUES ('2002/10/01',10);
> INSERT INTO ty_test VALUES ('2002/10/15',20);
> INSERT INTO ty_test VALUES ('2002/11/15',30);
>
> DROP VIEW vw_ty_test;
> CREATE VIEW vw_ty_test AS
> SELECT
> TO_CHAR(datein,'MM/') AS datein2,
> SUM(money)
> FROM
> ty_test
> GROUP BY
> datein2;
>
> SELECT * FROM ty_test; -- All rows from table.
> SELECT * FROM vw_ty_test; -- All rows from view.
>
I don't the work around using a view but you can do
it without using a view:
SELECT
to_number(to_char(datein,'mm'),'99') as month,
to_number(to_char(datein,''),'') as year,
SUM(money)
FROM ty_test
WHERE datein BETWEEN
to_date('01/10/2002','mm/dd/') AND
to_date('09/10/2002','mm/ddy/')
ORDER BY
to_number(to_char(datein,'mm'),'99')
to_number(to_char(datein,''),'');
ludwig.
__
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
http://sbc.yahoo.com
---(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] Weird NULL behavior
Hi: Has anyone encountered this before? SELECT CAST ( (NULL*NULL) AS NUMERIC(2,0)); returns the following error message: Cannot cast type '"char"' to '"numeric"' But the following sql statements returns NULL: select NULL: select NULL * NULL; select cast ( NULL as NUMERIC(2,0)); Thank you in advance, ludwig. __ Do you Yahoo!? New DSL Internet Access from SBC & Yahoo! http://sbc.yahoo.com ---(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] Generating a cross tab (pivot table)
--- Christoph Haller <[EMAIL PROTECTED]> wrote: > It's obvious this approach is most inflexible. > As soon as there is a new vendor, one has to > re-write the query and add > SUM(CASE vendor WHEN 'mr. new' THEN ... , > > In an advanced example it is shown how to deal with > cross tabs in > general > using a stored procedure. I am going to translate > this and re-write it > for postgres, too (ok, I will try). I saw something that might somewhat a bit more flexible solution using SQL. I don't know it it work in PostgreSQL. I saw it at the MySQL site. The following is the URL: http://www.mysql.com/articles/wizard/index.html Has anyone tried this on a PostgreSQL database ? regards, ludwig __ Do you Yahoo!? New DSL Internet Access from SBC & Yahoo! http://sbc.yahoo.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Passing OLD/NEW as composite type PL/PGSQL
Hi: Can I pass the the variables OLD and NEW (type OPAQUE) to another function is expecting a composite type as parameter? Are opaque considered as composite type? Thank you in advance, ludwig. __ Do you Yahoo!? New DSL Internet Access from SBC & Yahoo! http://sbc.yahoo.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] More than 1 trigger of the same kind
Hi: Can I have more than 1 trigger of same kind on one table? (i.e. 2 AFTER INSERT TRIGGER) on 1 table? I'm planning to split up a large trigger function (about 200 lines) into 2 seperate triggers. Since PL/PGSQL functions cannot accepts OPAQUE as arguments, I have to create 2 triggers instead of just creating 2 functions and having the trigger function calling another function passing the record NEW and OLD. Does having more than 1 trigger of the same kind produces some side effect? I mean is the order of the trigger firing is always the same? Thank you in advance, ludwig. __ Do you Yahoo!? New DSL Internet Access from SBC & Yahoo! http://sbc.yahoo.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Some more weird NULL behavior
Hi:
I tried the following:
CREATE TABLE x(
a NUMERIC(5,0),
b VARCHAR(5)
);
CREATE TABLE y(
a INTEGER,
b VARCHAR(5)
);
INSERT INTO x(b) VALUES ('LUDZ');
INSERT INTO y(b) VALUES ('TEST');
SELECT x.b,y.b FROM x,y WHERE x.a=y.a
returns zero rows.
However:
SELECT b FROM x WHERE a IS NULL returns 'LUDZ'
SELECT b from y WHERE a IS NULL returns 'TEST'
I also tried
SELECT x.b,y.b
FROM x,y
WHERE x.a = CAST(y.a AS INTEGER);
But it also returns zero rows.
Thank you in advance,
ludwig.
__
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
http://sbc.yahoo.com
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Drop NOT NULL constraint !!!
--- [EMAIL PROTECTED] wrote: > > do a \d tablename > > for the name of the contraint. > say its $1 > the do > > psql> alter table drop contstraint "$1" > RESTRICT; > I alter a table with by adding a foriegn key constraint. ALTER TABLE sc_city ADD CONSTRAINT cons_fkey FOREIGN KEY state_cd REFERENCE sc_state(state_cd); After altering the table I executed "\d " show the following: Triggers: RI_ConstraintTrigger_56743429 I'm surprised that name of constraint doesn't appear even though I explictly name it. Is there a way to show the name of the constraint? Should I do : ALTER TABLE sc_city DROP CONSTRAINT RI_ConstraintTrigger+5674329 RESTRICT ? Thank you very much, ludwig __ Do you Yahoo!? New DSL Internet Access from SBC & Yahoo! http://sbc.yahoo.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] ISNULL FUNCTION
--- Héctor Iturre <[EMAIL PROTECTED]> wrote: > HI, >HERE IS AN ALTERNATIVE TO USE THE SQL SERVER > ISNULL() FUNCTION > > > select case when FIELD_NAME isnull then 'EXPRESION' > else FIELD_NAME end > from calfiscal > where impuesto = 1 try using SELECT coalesce(field_name,'EXPRESSION') FROM calfiscal WHERE impuestor = 1 OR SELECT case when FIELD_NAME IS NULL then 'EXPRESION' else FIELD_NAME end from calfiscal where impuesto = 1 regards, ludwig __ Do you Yahoo!? New DSL Internet Access from SBC & Yahoo! http://sbc.yahoo.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] convert NULL into a value
--- Jonathan Man <[EMAIL PROTECTED]> wrote: > Hi, > > There is a function on the Oracle. That is > NVL(field, 0) to convert null into a value (e.g. > ZERO). > > Can I use this function on the PostgreSQL?? > -- The equivalent function is PostgreSQL is SELECT COALESCE(field,0) regards, ludwig. __ Do you Yahoo!? New DSL Internet Access from SBC & Yahoo! http://sbc.yahoo.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Primary Key Help !
--- Waheed Rahuman <[EMAIL PROTECTED]> wrote: > Hi Greetings > My question is How many primary key i can assign in > a PostGresql Table > > Rowid| Parent1 | Parent2 > | Parent3 | Parent4 | > Parent5 | Parent6 | Parent7 >| Parent8 | Parent9 | > Parent10 | > > Here i want to make Parent1.Parent 10 as > Primary Key and ...this parent field i want to make > it more than 10 fieldsif i try to make primary > key more than 10 fields in ms access its say not > possible so please let me know how i can do that in > psql. or if there is an alternative way...i will be > glad Assuming that you have already created the table try doing the following: ALTER TABLE ADD CONSTRAINT PRIMARY KEY (parent1,parent2,parent10); Hope that helps, ludwig __ Do you Yahoo!? New DSL Internet Access from SBC & Yahoo! http://sbc.yahoo.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] A PL/PgSQL Question
Hi: I have the following PL/PgSQL code blocks: a) IF (condition) THEN RAISE EXCEPTION ''Cannot Insert''; END IF; b) IF (condition) THEN RAISE NOTICE ''Cannot Insert''; RETURN NULL; END IF; What is the difference between the first and second chunk PL/PgSQL INSERT/UPDATE trigger function code aside from the fact that first scenario will result in an "ABORT" state? Are there any instances where a NOTICE and a RETURN NULL statement is a much better than a RAISE EXCEPTION statement? Thank you very much, ludwig lim __ Do you Yahoo!? New DSL Internet Access from SBC & Yahoo! http://sbc.yahoo.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] CHECKS vs. BEFORE INSERT OR UPDATE TRIGGER
Hi: I am just wondering. Which one is executed first: CHECK Constraints or BEFORE INSERT OR UPDATE TRIGGER? Thank you very much, ludwig. __ Do you Yahoo!? New DSL Internet Access from SBC & Yahoo! http://sbc.yahoo.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] pl/pgsql question
--- Tim Perdue <[EMAIL PROTECTED]> wrote: > I have created a function in pl/pgsql to modify a > row before it gets put > into the database, but it seems my modification is > being ignored, and > the unmodified row is being inserted. > > I have confirmed with this RAISE EXCEPTION that my > "NEW" row is modified > properly, however it is not being stored in the db. > > NEW.start_date := NEW.start_date+delta; > -- RAISE EXCEPTION ''new start date: % > '',NEW.start_date; > NEW.end_date := NEW.end_date+delta; > > It's probably something very obvious, but I'm > mystified. > > CREATE TRIGGER projtask_insert_depend_trig AFTER > INSERT ON project_task > FOR EACH ROW EXECUTE PROCEDURE > projtask_insert_depend(); > > Try changing the "AFTER" to "BEFORE" CREATE TRIGGER projtask_insert_depend_trig BEFORE... Changes made to the "NEW" will not be reflect in the AFTER trigger since, the row is already inserted. __ Do you Yahoo!? New DSL Internet Access from SBC & Yahoo! http://sbc.yahoo.com ---(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] Deleting in order from a table
--- pginfo <[EMAIL PROTECTED]> wrote: > I have a table tableA ( ,order_num int). > > I will to delete some records from tableA but in asc > or desc > order_num-order. > > Is it possible to write delete from tableA where > (some conditions) order > by order_num ? > > Many thanks, > ivan. > Try the following: DELETE FROM tableA WHERE order_num IN ( SELECT order_num FROM tableA ORDER BY order_num LIMIT n OFFSET m); Use LIMIT to determine the number of rows to delete and OFFSET to determine the "starting row". ludwig __ Do you Yahoo!? New DSL Internet Access from SBC & Yahoo! http://sbc.yahoo.com ---(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] RFC: A brief guide to nulls
--- [EMAIL PROTECTED] wrote: > There have been a few posts recently where people > have had problems with > nulls. Anyone got comments on the below before I > submit it to techdocs? > > TIA > > - Richard Huxton > > A Brief Guide to NULLs > == > > What is a null? > === > A null is *not* an empty string. > A null is *not* a value. > A null is *not* a "special" value. > A null is the absence of a value. > > > What do nulls mean? > === > Well, they *should* mean one of two things: > 1. There is no applicable value > 2. There is a value but it is unknown Good job!, it sure helps people who don't much background on formal database theory. What about adding this to the section "What does nulls mean" --> 3) No value has yet been assigned to that particular attribute (field). I think it would also be nice if you can add a section on functions that deals with NULL such as nullif() and coalesce(). These functions help users interpret NULL values. best regards, ludwig __ Do you Yahoo!? New DSL Internet Access from SBC & Yahoo! http://sbc.yahoo.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
--- David Durst <[EMAIL PROTECTED]> wrote: > Can anyone tell me why postgres is creating a > implicit index when > I already have a PKEY specified > > Or am I just interpreting this all wrong? PostgreSQL uses UNIQUE INDEX to enforce PRIMARY KEY constraint. Therefore creating a PRIMARY KEY will automatically create a UNIQUE INDEX. ludwig. __ Do you Yahoo!? New DSL Internet Access from SBC & Yahoo! http://sbc.yahoo.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] plpgsql: debugging
--- Oliver Vecernik <[EMAIL PROTECTED]> wrote: > Hi, > > Searching Google I found a thread in July 2001 > concerning the facilities > for debugging plpgsql functions. The actual answer > was: it should be > improved. > > What is the best way to debug a plpgsql function? > > Oliver This may not be the best way since its a bit crude. Try using RAISE NOTICE every now then to monitor the values of variables in the screen and record it in log file. Example : RAISE NOTICE ''Initial value of variable = %'',v_variable; /* Do some computation ... */ RAISE NOTICE ''Value of variable after operation = %'',v_variable; hope that helps, ludwig __ Do you Yahoo!? New DSL Internet Access from SBC & Yahoo! http://sbc.yahoo.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] [ADMIN] how sub queries and joins differs funcationally
Hi shreedhar : --- shreedhar <[EMAIL PROTECTED]> wrote: > Can any body tell that how sub queries and joins > differs funcationally. > Because sub queries taking lot of time than joins. > The following could be the probable reasons: a) Your are using correlated queries - Correlated queries always re-evaluate the sub-query for each row processed. b) You have an IN clause - try using EXISTS clause, since it IN clause doesn't utilize the index when your subquery is another SELECT statement. ludwig. __ Do you Yahoo!? New DSL Internet Access from SBC & Yahoo! http://sbc.yahoo.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Inserting a tab character
--- Luke Pascoe <[EMAIL PROTECTED]> wrote:
> I have a table which defines various possible file
> delimiters (CHAR(1) NOT
> NULL), for the moment it'll only contain comma and
> tab. Inserting a comma is
> easy, but inserting a tab is proving somewhat more
> difficult.
>
> How do I do it in 'psql'?
>
--> Try using '\t' for tab.
Example :
INSERT INTO table1(f1) values ('\t');
I'm not sure if inserting a TAB character will
cause some side-effects for commands like COPY FROM /
TO since these commands use tab to delimit fields.
Hope that helps,
ludwig
Hope that helps,
ludwig.
__
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] TIME vs. TIMESTAMP data type
Hi: Are there cases when a TIME data type is a better choice over the TIMESTAMP data type? It seems that PostgreSQL (I'm using 7.2.3) encourage its users to use TIMESTAMP over TIME data type. I said this because of the following: a) More functions for DATE and TIMESTAMP data types such as to_date() and to_timestamp(). Howver, function to_time() does not exist. b) Same amount of storage for TIMESTAMP and for TIME. Time with time zone even need more storage space than a timestamp (12 bytes vs. 8 bytes). c) It's harder to TIMESTAMP to TIME and vice versa, while its easier to cast TIMESTAMP to DATE and vice versa. thank you very much, ludwig __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Lock timeout detection in postgres 7.3.1
--- Christoph Haller <[EMAIL PROTECTED]> wrote: > > I'm working on > PostgreSQL 7.2.3 on hppa-hp-hpux10.20, compiled by > GCC 2.95.2 > and found a similar behaviour. > > T1 (within psql): > BEGIN; DELETE FROM ; > DELETE n > > T2 (within psql): > BEGIN; DELETE FROM ; > > > The documentation says (within Server Runtime > Environment) > DEADLOCK_TIMEOUT (integer) > > This is the amount of time, in milliseconds, to > wait on a lock > before checking to see if there is a deadlock > condition or not. The > If I get this right, the T2 psql process should > terminate within one > second, shouldn't it? > The postgresql.conf file is as it was right after > the installation > #deadlock_timeout = 1000 > > So, I doubt this a bug, but still, there must be a > misunderstanding or > something else > I don't know about. Could someone please enlighten > us. > I don't think there is a deadlock in the example given above. If I'm not mistaken a deadlock occurs if both transactions are waiting for each other to release the lock (i.e T1 waits for T2 to release locks/resources while T2 is also waiting for T1 to release locks/resources. In the above example, T1 doesn't wait for T2 to do something before finishes the transaction (Only T2 is waiting for T1 to finish), hence the condition for deadlock is not met. ludwig. __ Do you Yahoo!? New DSL Internet Access from SBC & Yahoo! http://sbc.yahoo.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] TIME vs. TIMESTAMP data type
Hi Tomasz: --- Tomasz Myrta <[EMAIL PROTECTED]> wrote: > > Probably you are right, but you can cast into > timestamp before using these functions. > Do you really need to care amount of storage? I was just thinking if both TIMESTAMP and TIME have use the same amount of space (I was think TIME might use less space since it doesn't need to store month, year, day as compared to TIMESTAMP), and TIMESTAMP have more functions and is easier to cast, I might as well use TIMESTAMP. > Don't forget about INTERVAL type, which is very > useful for time calculations. > --> I'll check that one out. __ Do you Yahoo!? New DSL Internet Access from SBC & Yahoo! http://sbc.yahoo.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Some Questions
Hi: I would like to ask the following questions: a) Are foreign key constraint triggers guaranteed to execute first before any ordinary "BEFORE INSERT/UPDATE/DELETE" trigger is executed? (This is assuming that the foreign keys are declared as "NOT DEFERRABLE") b) Is "varchar" (without upper limit) the same as "text"? I do notice that when I create a view : create view v_test as (select 'test'::varchar(10) union select 'test1'::varchar(10)); a "\d v_test" on the psql prompt would always say the only only column the view is of type "character varying". Does a union of a varchar(n) column and a another varchar(n) column would automatically cast it to "character varying" (w/o upper limit) even if the upper limit of the 2 varchar columns are the same? thanks in advance, ludwig lim __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] Question on OUTER JOINS.
Hi: 1) Is the ON clause of an OUTER JOIN always evaluated first before the WHERE clause? 2) Given the ff SQL statement : SELECT employee_id, a.status as status FROM permissions a LEFT JOIN (select * from employee where employee_id = 3) as b on (a.status=b.status) WHERE a.status='test'; Is there a way to rewrite the query as a view such that one can do: select * from test_view where employee_id=3 and status='test'; Thank you very much, ludwig lim __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com ---(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] Question on OUTER JOINS.
--- Tom Lane <[EMAIL PROTECTED]> wrote: > Ludwig Lim <[EMAIL PROTECTED]> writes: > > 1) Is the ON clause of an OUTER JOIN always > > evaluated first before the WHERE clause? > > No; the planner will do whatever it thinks is the > most efficient way > (assuming it can prove that the reordering it wants > to do won't change > the query result). If re-ordering does change the result, is the ON clause evaluated first and the WHERE filters out the result of the OUTER JOIN? > > Is there a way to rewrite the query as a view > such > > that one can do: > > I'm really not clear on what you want here. Better > example please? > Sorry for not making it that clear. Is there way of rewritting : SELECT a.status, employee_id FROM permission a LEFT JOIN ( SELECT * FROM employee WHERE employee_id =5) as b ON (a.status = b.status) WHERE status='test' into a query that has no subselect in the FROM clause. I mean can the query above be rewritten into something like: SELECT a.status, b.employee_id FROM permission a LEFT JOIN employee b ON (a.status = b.status) WHERE a.status = 'test' and b.employee_id = 5; Thank you very much, ludwig __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
