[SQL] "ORDER BY" issue - is this a bug?
Greetings, I've been using postgresql for some time and currently am using it in three different environments (specific details below) w/ the first two being 6.5.x releases and the third 7.0.2-2: I've come across what I consider an odd sorting anomaly in 7.0.2-2 where running the following query: select headline from headlines where headline like 'Alb%' order by 1 ; The results in the 7.0.2-2 install come back case-insensitive and oblivious to punctuation. This seems to me to be a bug; case-insensitive ordering can be achieved with the use of UPPER() or LOWER() functions, otherwise why have the functions. Also, I've created three test databases on the 7.0.2-2 system each with a different specified encoding - List of databases Database | Owner | Encoding +--+--- headlines | deckard | WIN headlines2 | deckard | SQL_ASCII headlines3 | deckard | LATIN1 to see if the results are different. Each of the encodings produces the same anomalous results. Any help in this regard as well as resolving it would be appreciated. Details of systems and schema are appended below. -- Max Pyziur BRAMA - Gateway Ukraine [EMAIL PROTECTED] http://www.brama.com/ The Details: On the first two systems the results are as follows: headline - Albright Arrives in Ukraine to Boost Reforms Albright Calls on Ukraine to Announce Chernobyl Closure Albright Calls on Ukraine to Speed up Military Reforms Albright Hopeful on Chernobyl Cover Albright Meets Ukraine Officials Albright Reschedules Ukraine Visit Albright Throws Weight Behind Ukraine's Kuchma Albright To Hold Talks in Ukraine Albright plans to show support for reform in visit to Ukraine Albright plans to show support for reform in visit to Ukraine Albright rushes to kyiv ahead of putin Albright says U.S. still backs Kuchma Albright to perform balancing act in Central Asia Albright, in Kiev, Hails Russian START-2 Vote (14 rows) # On the 7.0.2-2 system it is: headline --- Albright Arrives in Ukraine to Boost Reforms Albright Calls on Ukraine to Announce Chernobyl Closure Albright Calls on Ukraine to Speed up Military Reforms Albright Hopeful on Chernobyl Cover Albright, in Kiev, Hails Russian START-2 Vote Albright Meets Ukraine Officials Albright plans to show support for reform in visit to Ukraine Albright plans to show support for reform in visit to Ukraine Albright Reschedules Ukraine Visit Albright rushes to kyiv ahead of putin Albright says U.S. still backs Kuchma Albright Throws Weight Behind Ukraine's Kuchma Albright To Hold Talks in Ukraine Albright to perform balancing act in Central Asia (14 rows) # 1 - Production - Solaris 2.6 running Postgresql 6.5.2 on a small Sparc 2 compiled from source 2 - Development - RH5.2 Linux 2.0.36 running Postgresql 6.5.3 installed from rpms 3 - Development - RH6.2 Linux 2.2.14-5.0smp running Postgresql 7.0.2-2 installed from stock rpms Database table schema: headlines=> \d headlines Table= headlines +--+--+---+ | Field | Type| Length| +--+--+---+ | headline_id | int4 not null default nextval ( | 4 | | headline | text | var | | url | text | var | | postdate | date | 4 | | source | text | var | | flags| text | var | | posttime | timestamp| 4 | +--+--+---+ Indices: headlines_headline_id_key hl_pdate hl_s_pd hl_srce -- Max Pyziur BRAMA - Gateway Ukraine [EMAIL PROTECTED] http://www.brama.com/
[SQL] Re: [BUGS] "ORDER BY" issue - is this a bug?
Max Pyziur <[EMAIL PROTECTED]> writes: > I've come across what I consider an odd sorting anomaly in 7.0.2-2 where running > the following query: > select headline from headlines where headline like 'Alb%' order by 1 ; > The results in the 7.0.2-2 install come back case-insensitive and > oblivious to punctuation. That's pretty bizarre (not to say difficult to believe). What LOCALE setting are you running the postmaster in? regards, tom lane
[SQL] Re: [BUGS] "ORDER BY" issue - is this a bug?
Tom Lane wrote: > > Max Pyziur <[EMAIL PROTECTED]> writes: > > I've come across what I consider an odd sorting anomaly in 7.0.2-2 where running > > the following query: > > select headline from headlines where headline like 'Alb%' order by 1 ; > > > The results in the 7.0.2-2 install come back case-insensitive and > > oblivious to punctuation. > > That's pretty bizarre (not to say difficult to believe). What LOCALE > setting are you running the postmaster in? On none of the installations - the two 6.5.x and the 7.0.2-2 one - I don't have any locale set. I get (what I think are) correct results with the first two. Does 7.0.2-2 require the setting of locale. If so, where is it documented and/or how is it done? > regards, tom lane -- Max Pyziur BRAMA - Gateway Ukraine [EMAIL PROTECTED] http://www.brama.com/
[SQL] Re: [BUGS] "ORDER BY" issue - is this a bug?
Max Pyziur <[EMAIL PROTECTED]> writes: >> That's pretty bizarre (not to say difficult to believe). What LOCALE >> setting are you running the postmaster in? > On none of the installations - the two 6.5.x and the 7.0.2-2 one - I > don't have any locale set. I get (what I think are) correct results > with the first two. > Does 7.0.2-2 require the setting of locale. AFAIK its behavior should be the same as 6.5 for LOCALE issues. That's why I suspect an environment difference. I can assure you there is no code in the backend that will do case-insensitive, punctuation-insensitive comparisons --- much less any to do so without request. I'm betting that either this is your error, or the strcmp() library function is doing it; and as far as I've heard, only LOCALE environment variables might affect the behavior of strcmp(). It also seems possible that no sort is happening at all (which would be a planner bug), and the ordering you're getting is just whatever happens to be in the underlying table. Does EXPLAIN show that the query is being done with an explicit sort? regards, tom lane
Re: [SQL] Re: [BUGS] "ORDER BY" issue - is this a bug?
Tom Lane wrote: > > Max Pyziur <[EMAIL PROTECTED]> writes: > >> That's pretty bizarre (not to say difficult to believe). What LOCALE > >> setting are you running the postmaster in? > > > On none of the installations - the two 6.5.x and the 7.0.2-2 one - I > > don't have any locale set. I get (what I think are) correct results > > with the first two. > > > Does 7.0.2-2 require the setting of locale. > > AFAIK its behavior should be the same as 6.5 for LOCALE issues. That's > why I suspect an environment difference. Thanks for your quick replies. I dropped my databases, uninstalled the 7.0.2-2 rpms and installed 6.5.3 rpms on my development RH6.2 Linux 2.2.14-5.0 system and I still get the anomalous query result on that box. > I can assure you there is no code in the backend that will do > case-insensitive, punctuation-insensitive comparisons --- much less any > to do so without request. I'm betting that either this is your error, > or the strcmp() library function is doing it; and as far as I've heard, > only LOCALE environment variables might affect the behavior of strcmp(). There is a $LANG variable which is set to en_US; is this what might be causing the problem? I've tried unsetting it (unset LANG) and still get the problem. Is there something else which I should be looking at? > It also seems possible that no sort is happening at all (which would be > a planner bug), and the ordering you're getting is just whatever happens > to be in the underlying table. Does EXPLAIN show that the query is > being done with an explicit sort? Running EXPLAIN the results are: headlines1=> explain select headline from headlines where headline like 'Alb%' order by 1 ; NOTICE: QUERY PLAN: Sort (cost=221.15 rows=1 width=12) -> Seq Scan on headlines (cost=221.15 rows=1 width=12) EXPLAIN > regards, tom lane Thanks again, Max Pyziur BRAMA - Gateway Ukraine [EMAIL PROTECTED] http://www.brama.com/
Re: [SQL] Trigger
There is a sample in postgres documentation. (See below). the only problem is for using langage plpgsql. If it is not understand by your database, you must use command createlang plpgsql dbname as the owner of the database. CREATE TABLE emp ( empname text, salary int4, last_date datetime, last_user name); CREATE FUNCTION emp_stamp () RETURNS OPAQUE AS BEGIN -- Check that empname and salary are given IF NEW.empname ISNULL THEN RAISE EXCEPTION ''empname cannot be NULL value''; END IF; IF NEW.salary ISNULL THEN RAISE EXCEPTION ''% cannot have NULL salary'', NEW.empname; END IF; -- Who works for us when she must pay for? IF NEW.salary < 0 THEN RAISE EXCEPTION ''% cannot have a negative salary'', NEW.empname; END IF; -- Remember who changed the payroll when NEW.last_date := ''now''; NEW.last_user := getpgusername(); RETURN NEW; END; ' LANGUAGE 'plpgsql'; CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp FOR EACH ROW EXECUTE PROCEDURE emp_stamp(); - Original Message - From: Craig May <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, September 06, 2000 10:27 PM Subject: [SQL] Trigger > > Could someone send me a quick example of a trigger. >
[SQL] Isolation and Concurrency in PG functions?
Hail PG Gurus, Can somebody please tell us about concurrency in PG functions? I am porting some VERY big functions to PG for a data warehousing system (that is GPL'd BTW) and it seems that the results of one portion of the function (e.g. a create table or a series of inserts) are invisible to the other parts which obviously causes the function (and following functions) to fail and is completely driving me nuts because I see the results when I do the queries interactively. What can I do about this? Is autocommit off in functions? How can I bring it on? This becomes worse because transactions are not supported in functions (anybody knows when will this be around?). Thanks, -Roberto Mello -- Roberto Mello, [EMAIL PROTECTED] - GNU/Linux Reg.User #96240 Computer Science - Utah State University USU Free Software and GNU/Linux Club, President http://fslc.usu.edu - http://www.brasileiro.net/roberto
[SQL] Re: [GENERAL] Porting from mysql to psql (UNIX_TIMESTAMP()?)
i thick it's epoch() in PostgreSQLcheck your documentation. Zlatko Calusic wrote: > Hi! > > As subject says, we are currently porting all of our data, programs > and logic from mysql to postgresql. One of the things we have yet to > resolve is how to replace mysql's UNIX_TIMESTAMP() function we used > extensively in PosgreSQL? > > Function works like this in mysql: > > mysql> select start from connection limit 1; > +-+ > | start | > +-+ > | 2000-07-03 20:12:37 | > +-+ > 1 row in set (0.01 sec) > > mysql> select UNIX_TIMESTAMP(start) from connection limit 1; > +---+ > | UNIX_TIMESTAMP(start) | > +---+ > | 962647957 | > +---+ > 1 row in set (0.00 sec) > > Is there any similar functionality (returning unixish number of > seconds since 1970 from the timestamp field) in PostgreSQL? > > I tried all of the available date/time functions, type casting but all > to no avail. > > TIA, > -- > Zlatko > > P.S Is it bad manners crossposting to two pgsql mailing list? Still > new to PostgreSQL, still learning...
