Обсуждение: SQL Statements question, why I get errors...
I work a software company and our software is run on databases.. We have no problem using MS SQL, DB2, ect... One of my customers wanted to try and use POSTGRESQL.. He tried it and is having problems, so I downloaded it and am running it on my Red Hat 7.3 box.. I am running version 7.3.2 of Postgresql.. I hope this is the right place to ask this.. When we first launch our program we create several tables... Here is an example of one table we create: create table test (f1 int, f2 char(30), f3 char(30), f4 char(30)) create unique index itest on test (f1 asc) The first statement works fine.. However the second statement is when i get this error: Error: parser: parse error at or near "asc" at character 43 Does anyone know of how I can get the last statement to work with this database???? Thanks...
"Plist," > I work a software company and our software is run on databases.. We have > no problem using MS SQL, DB2, ect... One of my customers wanted to try > and use POSTGRESQL.. He tried it and is having problems, so I downloaded > it and am running it on my Red Hat 7.3 box.. I am running version 7.3.2 > of Postgresql.. I hope this is the right place to ask this.. When we > first launch our program we create several tables... Here is an example > of one table we create: You're in for a long, painful process if you think you can port a production application to PostgreSQL (or between *any* two database platforms) without an expert DBA. Perhaps you should consider a consultant? > create table test (f1 int, f2 char(30), f3 char(30), f4 char(30)) > create unique index itest on test (f1 asc) Some databases (MSSQL) require seperate indexes for ascending (ASC) and descending (DESC) sorts. Postgresql does not; since 7.2.0, we have been able to use the same index for both types of sorts. As such, Postgresql does not support the "ASC" syntax in index creation. Simply change your statement to: create unique index itest on test (f1); ... and it will work. -- Josh Berkus Aglio Database Solutions San Francisco
> "Plist," > >> I work a software company and our software is run on databases.. We >> have no problem using MS SQL, DB2, ect... One of my customers wanted >> to try and use POSTGRESQL.. He tried it and is having problems, so I >> downloaded it and am running it on my Red Hat 7.3 box.. I am running >> version 7.3.2 of Postgresql.. I hope this is the right place to ask >> this.. When we first launch our program we create several tables... >> Here is an example of one table we create: > > You're in for a long, painful process if you think you can port a > production application to PostgreSQL (or between *any* two database > platforms) without an expert DBA. Perhaps you should consider a > consultant? > >> create table test (f1 int, f2 char(30), f3 char(30), f4 char(30)) >> create unique index itest on test (f1 asc) > > Some databases (MSSQL) require seperate indexes for ascending (ASC) and > descending (DESC) sorts. Postgresql does not; since 7.2.0, we have been > able to use the same index for both types of sorts. As such, > Postgresql does not support the "ASC" syntax in index creation. Simply > change your statement to: > > create unique index itest on test (f1); > > ... and it will work. > I have tried that and yes it did work.. Just don't know if I want to tell my programmers to go in change all there code for postgresql... Thanks for the info.. Ron > -- > Josh Berkus > Aglio Database Solutions > San Francisco
On Sun, Feb 23, 2003 at 17:17:53 -0500, plist@horistjr.com wrote: > I work a software company and our software is run on databases.. We have > no problem using MS SQL, DB2, ect... One of my customers wanted to try > and use POSTGRESQL.. He tried it and is having problems, so I downloaded > it and am running it on my Red Hat 7.3 box.. I am running version 7.3.2 > of Postgresql.. I hope this is the right place to ask this.. When we > first launch our program we create several tables... Here is an example > of one table we create: > > create table test (f1 int, f2 char(30), f3 char(30), f4 char(30)) > create unique index itest on test (f1 asc) > > The first statement works fine.. However the second statement is when i > get this error: > > Error: parser: parse error at or near "asc" at character 43 > > Does anyone know of how I can get the last statement to work with this > database???? Postgresql doesn't allow you to specify directions for indexes. So leave the "asc" off. For an index on a single column it doesn't really matter anyway (clustered tables might be an exception). Currently multicolumn indexes all have to be in the same direction. If you need one that has mixed directions, the current solution is to create a new operator class that is backwards from normal and use that operator class instead of the normal one in the index.