Thread: problem migratin from ms sql

I wish to migrate from ms sql server to postgresql... an application (windows executable) not developed by me then no sources. We buyed for a lot of money so we must run it on windows clients.
All works greatly, and with more performance too, from php I can do few scripts without any problem.
But there is a problem: for some reports this application create a temporary table... with ms sql / sybase (t-sql) syntax !
create table #mytable( title_id char(6), total_money_made money ) insert into #mytable select title_id, total_money_made=sum( ytd_sales*price ) from titles group by title_id select * from #mytable drop table #mytable Of course odbc driver for postgresql reports a syntax error due to the table name !
There is a way to do this with postgres ?!?

Thank you in advance, best regards
Bruno

On Tue, Apr 19, 2005 at 05:35:05PM +0200, Bruno Depero wrote:
>
> create table #mytable( title_id char(6), total_money_made money ) insert
> into #mytable select title_id, total_money_made=sum( ytd_sales*price )
> from titles group by title_id select * from #mytable drop table #mytable
>
> Of course odbc driver for postgresql reports a syntax error due to the
> table name !

See "Identifiers and Key Words" in the "SQL Syntax" chapter of the
documentation, in particular the discussion of quoted identifiers.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

On Tue, Apr 19, 2005 at 17:35:05 +0200,
  Bruno Depero <bruno.depero@digicar.com> wrote:
> I wish to migrate from ms sql server to postgresql... an application
> (windows executable) not developed by me then no sources. We buyed for a
> lot of money so we must run it on windows clients.
> All works greatly, and with more performance too, from php I can do few
> scripts without any problem.
> But there is a problem: for some reports this application create a
> temporary table... with ms sql / sybase (t-sql) syntax !
> create table #mytable( title_id char(6), total_money_made money ) insert
> into #mytable select title_id, total_money_made=sum( ytd_sales*price )
> from titles group by title_id select * from #mytable drop table #mytable
>
> Of course odbc driver for postgresql reports a syntax error due to the
> table name !
> There is a way to do this with postgres ?!?

If you can't change the program to quote the table names, you do have the
option to hack the ODBC driver or Postgres itself to allow # in table
names.