Thread: PGSQL vs. SQL Server questions

PGSQL vs. SQL Server questions

From
Lee Matthews
Date:
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.

Re: PGSQL vs. SQL Server questions

From
Nabil Sayegh
Date:
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


Re: PGSQL vs. SQL Server questions

From
Josh Berkus
Date:
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


Re: PGSQL vs. SQL Server questions

From
"Mel Jamero"
Date:
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 -->


Re: PGSQL vs. SQL Server questions

From
Dani Oderbolz
Date:
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



Re: PGSQL vs. SQL Server questions

From
Dani Oderbolz
Date:
I forgot to say:
its only free for personla use.
But at a price of 49$ for a single user, its still
very moderate.


Re: PGSQL vs. SQL Server questions

From
Josh Berkus
Date:
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

Re: PGSQL vs. SQL Server questions

From
Lee Matthews
Date:
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 -->