Thread: PGSQL vs. SQL Server questions
I am interested in PGSQL as an alternative to MS SQL Server but am not aware of its capabilities. I have already written a number of SQL queries to create tables, views and insert data into a database in SQL Server. Can someone please advise me of the following, or alternatively point me to a relevant source of info :
Is there a tool like SQL Query Analyser that will allow me to run my existing SQL statements a PGSQL database?
Can you setup database views in PGSQL?
Are the main SQL Server datatypes matched in PGSQL (ie. varchar, decimal, int, datetime)?
Is an OBDC compliant driver for PGSQL that will enable my COM objects to work against both PGSQL and SQL Server by merely varying the connection string?
Thanks for any assistance you can offer.
Am Die, 2003-06-10 um 09.07 schrieb Lee Matthews: > Is there a tool like SQL Query Analyser that will allow me to run my > existing SQL statements a PGSQL database? I don't really understand this question. If you mean a command line tool where you can enter your queries and play around: yes: psql If you mean something that analyzes your queries and optimizes it: yes: VACUUM ANALYZE and EXPLAIN > Can you setup database views in PGSQL? yes You can also create functions and triggers if you want to insert into the views. > Are the main SQL Server datatypes matched in PGSQL (ie. varchar, > decimal, int, datetime)? AFAIK yes There's also a type: text With 'text' you don't have to care about size at all. > Thanks for any assistance you can offer. HTH -- e-Trolley Sayegh & John, Nabil Sayegh Tel.: 0700 etrolley /// 0700 38765539 Fax.: +49 69 8299381-8 PGP : http://www.e-trolley.de
Lee, Hi, I'm Josh, I admin both Postgres and MSSQL professionally. > Is there a tool like SQL Query Analyser that will allow me to run my > existing SQL statements a PGSQL database? There is currently no free Query Analyzer equivalent. PSQL, the command-line interface, allows dynamic query execution, but it is text-mode rather than a GUI. Both the PGAccess and PGAdmin GUI projects have graphical query windows, but they are somewhat more primitive than Query Analyzer. EMS HiTech's pay-for PostgreSQL GUI has an interface that is a lot more like QA (with context completion, even). For more information on the various GUIs available for PostgreSQL, see: http://techdocs.postgresql.org/guides/GUITools > Can you setup database views in PGSQL? Yes. In fact, PGSQL supports more complex views than MSSQL does, more sophisiticated procedures, and *updatable* views as well as read-only views. I think you will be pleased at the functionality that PGSQL has which MSSQL does not possess. > Are the main SQL Server datatypes matched in PGSQL (ie. varchar, decimal, > int, datetime)? Postgres has more data types than MSSQL; particularly because of the ability to create your own data types and domains in Postgres. For example, we have network and geometric data types as well as the ones you mention. However, keep in mind that the internal implementation of our data types differs from their analogs in MSSQL. For example, Postgres Timestamp is considerably more flexible and powerful than MSSQL's Datetime ... but the differences take some getting used to. > Is an OBDC compliant driver for PGSQL that will enable my COM objects to > work against both PGSQL and SQL Server by merely varying the connection > string? I can't answer that one; Postgres has an ODBC driver, but how smoothly you can transition your COM objects depends on too many factors to count. I suggest that you test it. -- -Josh Berkus Aglio Database Solutions San Francisco
Hi Lee, Josh's reply was very insightful indeed... =) Anyway, as for your last question: Yes, there's and ODBC compliant driver for PostgreSQL and it's been working fine for us, at least in the last 2 years. (XP, NT, 2000, '98, Red Hat Linux) And yes, you simply need to vary the connection string to connect to either SQL Server or Postgres. HTH. >> Is an OBDC compliant driver for PGSQL that will enable my COM objects to >> work against both PGSQL and SQL Server by merely varying the connection >> string? >I can't answer that one; Postgres has an ODBC driver, but how smoothly you can >transition your COM objects depends on too many factors to count. I suggest >that you test it. -----Original Message----- From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Josh Berkus Sent: Thursday, June 12, 2003 4:25 AM To: Lee Matthews; 'pgsql-novice@postgresql.org' Subject: Re: [NOVICE] PGSQL vs. SQL Server questions <-- snipped -->
Josh Berkus wrote: >Lee, > >Hi, I'm Josh, I admin both Postgres and MSSQL professionally. > > > >>Is there a tool like SQL Query Analyser that will allow me to run my >>existing SQL statements a PGSQL database? >> >> > >There is currently no free Query Analyzer equivalent. PSQL, the command-line >interface, allows dynamic query execution, but it is text-mode rather than a >GUI. Both the PGAccess and PGAdmin GUI projects have graphical query >windows, but they are somewhat more primitive than Query Analyzer. EMS >HiTech's pay-for PostgreSQL GUI has an interface that is a lot more like QA >(with context completion, even). > IMHO, there is one (and I use it), its called Aqua Data Studio (http://www.aquafold.com/). Its as powerful as M$s Qury Analyzer, you can browse the DB Structure and the functions and stuff. Its Java based, so its almost platfrom independet. Cheers, Dani
I forgot to say: its only free for personla use. But at a price of 49$ for a single user, its still very moderate.
Dani, > its only free for personla use. > But at a price of 49$ for a single user, its still > very moderate. Cool! Could you add a blurb on it for the PostgreSQL GUI page: http://techdocs.postgresql.org/guides/GUITools If you don't know how to use Wikis, e-mail me the blurb. -- Josh Berkus Aglio Database Solutions San Francisco
That's great news. I was hoping so...as none of the select queries I am using are particularly complex...and I am using the properties of the recordset object to do updates. Although I suspect it will be a little slower than ADO which I am using with SQL Server. I will dowload the ODBC doco and do a bit of research. Thanks for the reply Lee -----Original Message----- From: Mel Jamero [mailto:mel@gmanmi.tv] Sent: Thursday, 12 June 2003 6:02 PM To: josh@agliodbs.com; Lee Matthews; pgsql-novice@postgresql.org Subject: RE: [NOVICE] PGSQL vs. SQL Server questions Hi Lee, Josh's reply was very insightful indeed... =) Anyway, as for your last question: Yes, there's and ODBC compliant driver for PostgreSQL and it's been working fine for us, at least in the last 2 years. (XP, NT, 2000, '98, Red Hat Linux) And yes, you simply need to vary the connection string to connect to either SQL Server or Postgres. HTH. >> Is an OBDC compliant driver for PGSQL that will enable my COM objects >> to work against both PGSQL and SQL Server by merely varying the >> connection string? >I can't answer that one; Postgres has an ODBC driver, but how smoothly >you can >transition your COM objects depends on too many factors to count. I suggest >that you test it. -----Original Message----- From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Josh Berkus Sent: Thursday, June 12, 2003 4:25 AM To: Lee Matthews; 'pgsql-novice@postgresql.org' Subject: Re: [NOVICE] PGSQL vs. SQL Server questions <-- snipped -->