Re: [GENERAL] Case of strings
Is there anyway I can force the PgSQL to accept case
equivalence, or must I
add upper()/lower() to force the case and then make string
tests?
Ie Where upper(A)=upper('String')
I think you already answered your own question as pgsql
document does in section
9.4. String Functions and Operators
Regards,
CN
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Simplyfying many equals in a join
Is there a shorthand notation when performing a multi-table join and one column is to be equaled in all tables? Is this you are looking for? SELECT t1.c7,t2.c6 FROM t1,t2 USING (c1,c2,c3) WHERE t1.c4='2004-2-28' AND t2.c5='xyz' performs the same as SELECT t1.c7,t2.c6 FROM t1,t2 WHERE t1.c1=t2.c1 and t1.c2=t2.c2 and t1.c3=t2.c3 and t1.c4='2004-2-28' AND t2.c5='xyz' CN ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] PostgreSQL Advocacy, Thoughts and Comments
Jason Tesser [EMAIL PROTECTED] MySQL cannot even handle sub-queries yet. Ohh! Really? Allow me to pay my highest respect to the genius mySQL programmers! I completely have no clue on how to construct any single tiny database on a DBMS having no sub-query capability. Being too dumb, I solicit mySQL programmers' help by showing me employee FOO's birthday and his/her latest job title effective on or before 2003-1-1 from the following tables: CREATE TABLE t1 (employee TEXT,BirthDay DATE); CREATE TABLE t2 (employee TEXT,EffectiveDate DATE,JobTitle TEXT); And make the result like this: FOO 1980-1-1 programmer Please do not give me the answer that you will merge these two tables to form one like this: CREATE TABLE t1 (employee TEXT,BirthDay DATE,EffectiveDate DATE,JobTitle TEXT); Regards, CN ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] TIMEZONE not working?
Hello! Tom, Not at all. TIMESTAMP WITHOUT TIMEZONE will not react to timezone environment at all. Absolutely right! I seemed to have trouble understanding lengthy, though good, documentation. Here are some minor issues I have encountered: - SQL commands like SET TIMEZONE TO NZDT are illegal while Table B-4 in Appendix B says they are recognized. - Command SET TIMEZONE TO +08:30 is also illegal. - I don't fully understand the statement in section 8.5 of the documentation: [QUOTE] Note: When timestamp values are stored as double precision floating-point numbers (currently the default), the effective limit of precision may be less than 6. timestamp values are stored as seconds since 2000-01-01, and microsecond precision is achieved for dates within a few years of 2000-01-01, but the precision degrades for dates further away. [/QUOTE] Does this mean double timestamp, the default storage type, allows dates starting from 2000-1-1? I just inserted and selected the value '1999-1-1' without problem. [QUOTE] When timestamp values are stored as eight-byte integers (a compile-time option), microsecond precision is available over the full range of values. However eight-byte integer timestamps have a reduced range of dates from 4713 BC up to 294276 AD. [/QUOTE] Dos this mean that 8-byte timestamp accepts only up to year AD 806 (=294276/365)? Table 8-9 looks to me that pgsql accepts up to AD 5874897 days. As always, thank you very much for the help! Best Regards, CN ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] PostgreSQL is much faster than MySQL, only when...
I'm curious as to what type of application you run and what first prompted you to switch to postgresql? I would like to add my 2 cents to this although I have not been asked by anyone about this :-) If I remember correctly the fact about 7 years ago... when PostgreSQL already supported - sub-query - transaction - triggers - stored procedures (functions) , mySQL did not have any of these critical and essential capabilities required by any serious bussiness applications. When I noticed that fact, I have never turned my head back to mySQL again since then. Why? Because I thought I would take a train instead of a plane (if it indeed is), when I am 100% sure that the plane will not fly due to bad wether. Regards, CN ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] cache lookup failed
Suppose you create a function function1 and a trigger that calls function1. Then you drop function1 and re-create function1. Now you will find that the trigger can't see function1's existence. trigger (and other object too) refeerences functions using OID, not the function name. I also feel it being inconvient for postgresql to use OID's instead of object names. CN You too can have your own email address from Eurosport. http://www.eurosport.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: [GENERAL] plpgsql/cursor
can i declare and fetch from a cursor inside a plpgsql function? (pg version 7.0.3) In 7.1, the pl/pgsql document says you can code like this: DECLARE rec record; BEGIN for rec in SELECT * from MyTable LOOP update tableX set tableX=rec.fieldA; END LOOP; END; CN You too can have your own email address from Eurosport. http://www.eurosport.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] How to implement transaction in plpgsql?
If I remember correctly, the document in function section sas that people can not use transaction statements (begin, commit, rollback) IN any function. Instead, we should use these transaction statements OUTSIDE function. Good luck. CN You too can have your own email address from Eurosport. http://www.eurosport.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] need help
Once we start a Postmaster process, will it keep running forever? It will if you run it in background like postmaste -i -D /home/mydir/pgsql-database-dir How can we restart/stop??(the commands???) The preferred command might be pg_ctl that comes along with postgresql distribution: pg_ctl start pg_ctl stop killing the postmaster process (kill pid) also works although this approach is not recommended. Also, should we start the Postmaster only as the postgres superuser??? I would say this is a management issue and it is you who decide whether to start postmaster by postgres or not. I have not seen in the document saying that this is the only way to go. Is it possible to have users for postgres database alsolike in linuxwe have users? Yes. you can define users somewhere. The document already explains. CN CN You too can have your own email address from Eurosport. http://www.eurosport.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] What Is The Firing Order?
I don't think oid order would help, because what happens if you've say got a trigger and then want to add another before it? I'd guess the most general way would be to give triggers some kind of numeric ordering not associated with anything else, but then you need ways to set it on create trigger and probably an alter trigger way to change it. :( I'd guess that there'd be a default value if you didn't set it, and that triggers of the same value would run in indeterminate order like before. Being an ignorant end user, I would like to add some humble and stupid opinions. I too feel using creation timestamps of FK constraint and custom trigger to determine the firing order being inconvient for me due to 2 problems: Problem 1: I do a lot of table creations like this: CREATE TABLE table1 ( CONSTRAINT fk1 FOREIGN KEY (field1) REFERENCES table2 (field1) ON UPDATE CASCADE ON DELETE CASCADE, PRIMARY KEY (field1,field2), field1 TEXT, field2 TEXT, field3 TEXT ); Then, I create the trigger for table1 UPDATE event. If the execution order is determined by timestamp or oid, then I really am happy _now_. However, as pointed by you experts, problem happens when I want to change my mind and want my trigger be fired before fk1. Since trigger can only be created after table is created, there is no way for me to make the trigger fired before FK. Am I correct? Problem 2: Suppose I dump the database and drop it and then restore it from the dump file, and my machine runs too fast, can it happen that postgresql creates the same timestamps for the FK and trigger? Best Regards, CN You too can have your own email address from Eurosport. http://www.eurosport.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
[GENERAL] Temp Table In SQL Function
Hello! I am trying to create a sql function that returns 2 values using temporary table as the media: database1=# CREATE FUNCTION SaveNumeric(int2,int2) RETURNS INTEGER AS ' database1'# CREATE TEMP TABLE mytemp(a int2,b int2); database1'# INSERT INTO mytemp VALUES ($1+1,$2+100); database1'# SELECT 1; database1'# ' LANGUAGE 'sql'; ERROR: Relation 'mytemp' does not exist But below seems to work: database1=# CREATE FUNCTION Drop2Numeric() RETURNS INTEGER AS ' database1'# DROP TABLE mytemp; database1'# SELECT 1; database1'# ' LANGUAGE 'sql'; CREATE Why the creation of SaveNumeric(int2,int2) fails while the creation of Drop2Numeric() succeeds? Regards, CN You too can have your own email address from Eurosport. http://www.eurosport.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
