Re: [SQL] order by x DESC, y ASC indexing problem

2002-10-03 Thread Tom Lane
Vincent-Olivier Arsenault <[EMAIL PROTECTED]> writes: > How to have the planner use an index in the case of a query like : > SELECT * FROM TABLE1 ORDER BY X DESC, Y ASC; A normal index on (X,Y) is useless for this query, because neither scan direction in the index corresponds to the sort ordering

Re: [SQL] order by x DESC, y ASC indexing problem

2002-10-03 Thread Bruno Wolff III
On Thu, Oct 03, 2002 at 19:30:59 -0700, Josh Berkus <[EMAIL PROTECTED]> wrote: > Vincent, > > > SELECT * FROM TABLE1 ORDER BY X DESC, Y ASC; > > > > (X is a date and Y a varchar) > > > > What would that index be? > > > > Is there a function I can use, to invert x (the date), so that I can >

Re: [SQL] order by x DESC, y ASC indexing problem

2002-10-03 Thread Bruno Wolff III
On Thu, Oct 03, 2002 at 22:07:40 -0400, Vincent-Olivier Arsenault <[EMAIL PROTECTED]> wrote: > Hello, > > I saw a posting regarding this issue in august, with no solution... Then you didn't read the entire thread. You can create a new operator class to get the desired behavior. --

Re: [SQL] order by x DESC, y ASC indexing problem

2002-10-03 Thread Josh Berkus
Vincent, > SELECT * FROM TABLE1 ORDER BY X DESC, Y ASC; > > (X is a date and Y a varchar) > > What would that index be? > > Is there a function I can use, to invert x (the date), so that I can > make a query / index set like : PostgreSQL's sorting mechanism can use an index either ascending o

[SQL] order by x DESC, y ASC indexing problem

2002-10-03 Thread Vincent-Olivier Arsenault
Hello, I saw a posting regarding this issue in august, with no solution... How to have the planner use an index in the case of a query like : SELECT * FROM TABLE1 ORDER BY X DESC, Y ASC; (X is a date and Y a varchar) What would that index be? Is there a function I can use, to invert x (the d

Re: [SQL] drop constraint primary key

2002-10-03 Thread Tom Lane
"Marie G. Tuite" <[EMAIL PROTECTED]> writes: > Running above, am trying to drop the primary key, but get the follwing > error. > project=# alter table class_teacher_rlt drop constraint > class_teacher_rlt_pkey restrict; > ERROR: ALTER TABLE / DROP CONSTRAINT: class_teacher_rlt_pkey does not exist

[SQL] drop constraint primary key

2002-10-03 Thread Marie G. Tuite
project=# select version(); version - PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.96 (1 row) Running above, am trying to drop the primary key, but get the follwing error. project=# alter table cl

Re: [SQL] epoch to date

2002-10-03 Thread Achilleus Mantzios
On Thu, 3 Oct 2002, Roberto Mello wrote: > Hi all, > > Maybe I missed something obvious, but is there a built-in function to > convert epoch to date? I couldn't find it in the documentation for extract > and friends. well to compute epoch timestamp # SELECT (now() -(extract(epoch from now())::i

Re: [SQL] Formatting current_time output

2002-10-03 Thread Karel Zak
On Thu, Oct 03, 2002 at 10:46:19AM -0400, Thomas Good wrote: > > SELECT TO_CHAR(SYSDATE,'HH:MI AM') FROM DUAL; -- Oracle > SELECT TIME_FORMAT(current_time,'%l:%i %p'); -- MySQL SELECT TO_CHAR(now(),'HH:MI AM'); -- PostgreSQL :-) -- Karel Zak <[EMAIL PROTECTED]> h

Re: [SQL] Formatting current_time output

2002-10-03 Thread Thomas Good
On Thu, 3 Oct 2002, Josh Berkus wrote: > Thomas, > > > SELECT TO_CHAR(SYSDATE,'HH:MI AM') FROM DUAL; -- Oracle > > SELECT TIME_FORMAT(current_time,'%l:%i %p'); -- MySQL > > > > Returned: 10:58 AM > > > > I've found lpad(current_time,5); which gets me 1/3 of the way. > > Is there a function I h

Re: [SQL] Formatting current_time output

2002-10-03 Thread Charles H. Woloszynski
On 7.2.2, you can use select to_char(now(), 'HH12:MI AM'); (using current_time raises an error, but now() works fine). Charlie Josh Berkus wrote: >Thomas, > > > >>SELECT TO_CHAR(SYSDATE,'HH:MI AM') FROM DUAL; -- Oracle >>SELECT TIME_FORMAT(current_time,'%l:%i %p'); -- MySQL >> >>Returned:

Re: [SQL] Formatting current_time output

2002-10-03 Thread Achilleus Mantzios
SELECT to_char(now(), 'HH24:MI AM'); (in 7.2.1) == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [E

Re: [SQL] Formatting current_time output

2002-10-03 Thread Josh Berkus
Thomas, > SELECT TO_CHAR(SYSDATE,'HH:MI AM') FROM DUAL; -- Oracle > SELECT TIME_FORMAT(current_time,'%l:%i %p'); -- MySQL > > Returned: 10:58 AM > > I've found lpad(current_time,5); which gets me 1/3 of the way. > Is there a function I haven't found? Um, what's wrong with: SELECT to_char(cu

[SQL] epoch to date

2002-10-03 Thread Roberto Mello
Hi all, Maybe I missed something obvious, but is there a built-in function to convert epoch to date? I couldn't find it in the documentation for extract and friends. Thanks, -Roberto -- +|Roberto Mello -http://www.brasileiro.net/ |--+ + Computer Science Graduate S

[SQL] Formatting current_time output

2002-10-03 Thread Thomas Good
Hi. Anyone have any tips on how I can approximate the following: SELECT TO_CHAR(SYSDATE,'HH:MI AM') FROM DUAL; -- Oracle SELECT TIME_FORMAT(current_time,'%l:%i %p'); -- MySQL Returned: 10:58 AM I've found lpad(current_time,5); which gets me 1/3 of the way. Is there a function I haven't foun

Re: [SQL] Updating from select

2002-10-03 Thread Thrasher
Hi Manfred, Such a silly thing blocked me, and I was about to write a stored procedure. Thanks a lot !! BTW, does anyone knows about a RH7.2 or newer RPM of v7.3? Do I need any other tools not included in RH7.2 to compile it? Thrasher Manfred Koizar wrote: > On Wed, 02 Oct 2002 19:11:19 +020