Moving from SQL Anywhere to PostGres - First Time

[Robert Paresi]

Hello,

We have 700 user install base using Sybase SQL Anywhere 9.02

We are looking at migrating these installations over to PostGres

1. Very Very Short Answer Please - why should we?

2. Does anyone have a utility or migration application to read SQL Anywhere
to go to PostGres

3. Does PostGres handle column descriptions (ie: you can give each column a
50 character description) and then access it via SQL Result Set (like I can
do in Sybase)

4. Is there any Date/TimeStamp issues and conversions I need to know about.
I use simply a DATE field and a TIME field - but do not use DATE/TIME stamp
fields together.

5. What UI/Developer tools (GUI) are available to manage the database as
well as add/change columns rather than doing it via SQL commands.

Thank you.

-Robert

[Chris Browne]

Post by Robert Paresi
Hello,
We have 700 user install base using Sybase SQL Anywhere 9.02
We are looking at migrating these installations over to PostGres
1. Very Very Short Answer Please - why should we?

Cheaper? Perhaps faster? It's tough to guess, absent of metrics as to
why you might prefer Postgres.

Post by Robert Paresi
2. Does anyone have a utility or migration application to read SQL
Anywhere to go to PostGres

Usually, what's done here is to use whatever tools are built-in to:
a) Pull out the DDL defining the schema;
b) Dump the data as a series of INSERT statements.

I suggest you take a peek at the "conversion" page on the development
wiki:
<http://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL>

Post by Robert Paresi
3. Does PostGres handle column descriptions (ie: you can give each
column a 50 character description) and then access it via SQL Result
Set (like I can do in Sybase)

Sure.

You can attach comments to virtually any sort of object:
<http://www.postgresql.org/docs/9.0/static/sql-comment.html>

That page describes how comments can be pulled out.

Post by Robert Paresi
4. Is there any Date/TimeStamp issues and conversions I need to know
about. I use simply a DATE field and a TIME field - but do not use
DATE/TIME stamp fields together.

TIMESTAMP and TIMESTAMPTZ are the notable "date and time" types in
Postgres. They have the fairly considerable merit over DATE + TIME that
you can do rather simpler comparisons using "full" timestamps.

Thus, you can replace stuff like:

select * from txn_table a, other_thing b
where
a.date_col > b.date_col or
(a.date_col = b.date_col and a.time_col > b.time_col)

with
select * from txn_table a, other_thing b
where a.timestamp_col > b.timestamp_col

Probably the latter is faster, but the *important* part is that it's
enormously simpler, and your developers won't be prone to make mistakes
by missing comparisons, getting them wrong, or by having the queries so
much more complex that they just don't understand them.

Post by Robert Paresi
5. What UI/Developer tools (GUI) are available to manage the database
as well as add/change columns rather than doing it via SQL commands.

There's a barrel-load of graphical tools:
<http://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tools>

Of the "free software" tools, PGAdmin III is the one that is most
strongly maintained alongside the Postgres project, so is quite well
commendable.
--
http://linuxdatabases.info/info/multiplexor.html
Rules of the Evil Overlord #87. "My vats of hazardous chemicals will
be covered when not in use. Also, I will not construct walkways above
them." <http://www.eviloverlord.com/>

[Andy Colson]

Post by Robert Paresi
Hello,
We have 700 user install base using Sybase SQL Anywhere 9.02
We are looking at migrating these installations over to PostGres
1. Very Very Short Answer Please - why should we?

You should not. Does not sound like you have a problem to solve. If it
aint broke, dont fix it.

Or maybe you are having problems? A better question might be "Can PG
solve ___?"

Post by Robert Paresi
2. Does anyone have a utility or migration application to read SQL
Anywhere to go to PostGres

Perl?


-Andy

[Joshua D. Drake]

Post by Robert Paresi
Hello,
We have 700 user install base using Sybase SQL Anywhere 9.02
We are looking at migrating these installations over to PostGres
1. Very Very Short Answer Please - why should we?

* Lower TCO
* More flexibility
* A growing platform, not a shrinking one

(note: I am not arguing about SQL Anywhere and its capabilities. It is a
fine database)

Post by Robert Paresi
2. Does anyone have a utility or migration application to read SQL Anywhere
to go to PostGres

I don't know of one.

Post by Robert Paresi
3. Does PostGres handle column descriptions (ie: you can give each column a
50 character description) and then access it via SQL Result Set (like I can
do in Sybase)

You are able to use COMMENT ON, with pretty much every object that you
can create.

Post by Robert Paresi
4. Is there any Date/TimeStamp issues and conversions I need to know about.
I use simply a DATE field and a TIME field - but do not use DATE/TIME stamp
fields together.

We have those types:

http://www.postgresql.org/docs/9.0/static/datatype-datetime.html

Post by Robert Paresi
5. What UI/Developer tools (GUI) are available to manage the database as
well as add/change columns rather than doing it via SQL commands.

You can use psql (text based), pgadmin, navicat, and about 4 dozen
others.

Sincerely,

Joshua D. Drake

Post by Robert Paresi
Thank you.
-Robert

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt

[Michael Gould]

Robert,

We used Data Manager from JP to do this. Worked well. He recently added
the ability to read OEM versions of ASA. How's come your moving away from
SQL Anywhere?

Best Regards

Mike Gould

Post by Robert Paresi
Hello,
We have 700 user install base using Sybase SQL Anywhere 9.02
We are looking at migrating these installations over to PostGres
1. Very Very Short Answer Please - why should we?
2. Does anyone have a utility or migration application to read SQL Anywhere
to go to PostGres
3. Does PostGres handle column descriptions (ie: you can give each column a
50 character description) and then access it via SQL Result Set (like I can
do in Sybase)
4. Is there any Date/TimeStamp issues and conversions I need to know about.
I use simply a DATE field and a TIME field - but do not use DATE/TIME stamp
fields together.
5. What UI/Developer tools (GUI) are available to manage the database as
well as add/change columns rather than doing it via SQL commands.
Thank you.
-Robert
--
http://www.postgresql.org/mailpref/pgsql-general

--
Michael Gould, Managing Partner
Intermodal Software Solutions, LLC
904.226.0978
904.592.5250 fax

[Robert Paresi]

Hi,

Price and features. There are some changes in V12 which is causing us to
do some steps with our support and resellers. We've always wanted to move
away from Sybase so we can make more of a profit off our software and lower
costs for us and our clients. Sybase is a wonderful product, and when
things stay the same - we stay the same. But, since V12 is requiring us to
make internal changes, step changes, documentation changes, training changes
... then it's a good time for us to stop and look at alternatives if we were
going to look at them anyway.

If we can save $100K a year moving to PostGres and give our customers better
pricing, then it only makes sense to move - if PostGres does the same thing
that Sybase does.

They are a premium software vendor and have a premium product, but charge a
premium price for it. They've made huge changes that affect us, and won't
make any exceptions or changes for us, so now is the time to start looking
at our options.

-Robert


"Michael Gould" wrote in message news:***@intermodalsoftwaresolutions.net...

Robert,

We used Data Manager from JP to do this. Worked well. He recently added
the ability to read OEM versions of ASA. How's come your moving away from
SQL Anywhere?

Best Regards

Mike Gould

Post by Robert Paresi
Hello,
We have 700 user install base using Sybase SQL Anywhere 9.02
We are looking at migrating these installations over to PostGres
1. Very Very Short Answer Please - why should we?
2. Does anyone have a utility or migration application to read SQL Anywhere
to go to PostGres
3. Does PostGres handle column descriptions (ie: you can give each column a
50 character description) and then access it via SQL Result Set (like I can
do in Sybase)
4. Is there any Date/TimeStamp issues and conversions I need to know about.
I use simply a DATE field and a TIME field - but do not use DATE/TIME stamp
fields together.
5. What UI/Developer tools (GUI) are available to manage the database as
well as add/change columns rather than doing it via SQL commands.
Thank you.
-Robert
--
http://www.postgresql.org/mailpref/pgsql-general

--
Michael Gould, Managing Partner
Intermodal Software Solutions, LLC
904.226.0978
904.592.5250 fax
--
Sent via pgsql-general mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Craig Ringer]

Price and features. There are some changes in V12 which is causing us to
do some steps with our support and resellers. We've always wanted to
move away from Sybase so we can make more of a profit off our software
and lower costs for us and our clients. Sybase is a wonderful product,
and when things stay the same - we stay the same. But, since V12 is
requiring us to make internal changes, step changes, documentation
changes, training changes ... then it's a good time for us to stop and
look at alternatives if we were going to look at them anyway.

OK, so you're facing vendor price push and backward-compat issues. Since
you have to make changes anyway, you're trying to decide whether it's
worth making somewhat bigger changes to move to a royalty-free platform.
Makes sense; you're not just looking at change for change's sake.

It'd help if you enumerated the features (outside core SQL) that you use
heavily in Sybase, how you access Sybase, what replication if any you
use, how you handle backups, all that sort of thing. All that makes a
difference.

For example, if you're using standard SQL and accessing Sybase via JDBC
(or even ODBC), a transition might not be too bad. If you're using
native client libraries for Sybase T-SQL and using lots of
Sybase-specific extensions, the license cost savings may not be worth
the dev costs involved in making a move.

If we can save $100K a year moving to PostGres and give our customers
better pricing, then it only makes sense to move - if PostGres does the
same thing that Sybase does.

Imagine a two-circle venn diagram with a large inner overlap. The large
inner overlap, consisting of the capabilities and features shared by
both databases, is most of the area. There's still plenty on the outside
of the shared area that only one database or the other does.

What you need to do is figure out how much of that "sybase only" section
you use. To do that, you need to figure out how you use Sybase, which
features you use are part of the SQL standards, etc.

They are a premium software vendor and have a premium product, but
charge a premium price for it.

I tend to think of them as small fry compared to IBM, Oracle and
Microsoft. They're certainly more significant than folks like Cache,
Progress, etc though.

--
Craig Ringer

[Uwe Schroeder]

Post by Robert Paresi
Hi,
Price and features. There are some changes in V12 which is causing us to
do some steps with our support and resellers. We've always wanted to move
away from Sybase so we can make more of a profit off our software and lower
costs for us and our clients. Sybase is a wonderful product, and when
things stay the same - we stay the same. But, since V12 is requiring us
to make internal changes, step changes, documentation changes, training
changes ... then it's a good time for us to stop and look at alternatives
if we were going to look at them anyway.
If we can save $100K a year moving to PostGres and give our customers
better pricing, then it only makes sense to move - if PostGres does the
same thing that Sybase does.

This is quite a jump. I've worked with Sybase on military projects - pretty
much all the application logic coded in Sybase stored procedures. This would
be a scenario I'm not happy to convert to any other database - maybe except MS
SQL because that's historically an off-spring of Sybase and rather similar at
the base of things (although a decade of MS intervention sure changed things a
lot).

However, if your app doesn't rely on stored procedures heavily, the switch may
well be worth it. I've used postgresql long before it was even called that -
even back in the day when it was postgres and there was no sql whatsoever.
Still, this database has yet to fail me. I've written software for government,
military and financial industry and most of the projects I worked on where I
recommended postgres(sql) as the DB backend are still around, maintained and
run like a charm. One of my insurance clients had a postgresql 8.2 database
running for well over a year with nobody checking in on it (see -> recession -
they fired all their IT staff). The old server is still running just fine - just
checked in on it the other day and I was the first to log on in over a year -
runs, no questions asked.

So yes, if you want a database that just does the job I'd really recommend
postgresql. Sure, you can do the same with mucho $$$$ commercial products, but
personally I'd rather shoot a message to the mailing list here and get a very
professional/knowledegable/qualified answer within hours - that's much better
than any commercial big business support I've experienced (usually you call
the support for your vendor and you get someone who basically has no clue what
you're talking about - been there, done that - worked for Sun Microsystems 3rd
level operating system support for years and was always amazed that only few
people else could answer the questions of customers who paid 7+ digit support
contracts - i.e. BMW, Audi etc.)

Post by Robert Paresi
They are a premium software vendor and have a premium product, but charge a
premium price for it. They've made huge changes that affect us, and won't
make any exceptions or changes for us, so now is the time to start looking
at our options.

Have to say: here you get a premium product at cost of zip, nada, nothing and
when you shout out to the mailing list - and you provide useable information -
chances are you'll get premium support from people like Tom Lane. No company I
know of can beat that kind of "easy access to those who know what they're
talking about"

Don't get me wrong: Sybase is a good product and has been around for years.
But if you're on your road to change, going open source is definitely an option
to look into.

To sum it up: I'd put Postgresql on par with Oracle or DB2 any day of the week
- except it's not subject to exorbitant license fees and the support is much
better. It's not the fastest database on the market, but if you want something
that just works relieably, PostgreSQL will serve you well.

Post by Robert Paresi
-Robert
Robert,
We used Data Manager from JP to do this. Worked well. He recently added
the ability to read OEM versions of ASA. How's come your moving away from
SQL Anywhere?
Best Regards
Mike Gould

Post by Robert Paresi
Hello,
We have 700 user install base using Sybase SQL Anywhere 9.02
We are looking at migrating these installations over to PostGres
1. Very Very Short Answer Please - why should we?
2. Does anyone have a utility or migration application to read SQL

Anywhere

Post by Robert Paresi
to go to PostGres
3. Does PostGres handle column descriptions (ie: you can give each
column

a

Post by Robert Paresi
50 character description) and then access it via SQL Result Set (like I

can

Post by Robert Paresi
do in Sybase)
4. Is there any Date/TimeStamp issues and conversions I need to know

about.

Post by Robert Paresi
I use simply a DATE field and a TIME field - but do not use DATE/TIME

stamp

Post by Robert Paresi
fields together.
5. What UI/Developer tools (GUI) are available to manage the database as
well as add/change columns rather than doing it via SQL commands.
Thank you.
-Robert

--
Michael Gould, Managing Partner
Intermodal Software Solutions, LLC
904.226.0978
904.592.5250 fax

[Vick Khera]

Post by Uwe Schroeder
Have to say: here you get a premium product at cost of zip, nada, nothing and
when you shout out to the mailing list - and you provide useable information -
chances are you'll get premium support from people like Tom Lane. No company I
know of can beat that kind of "easy access to those who know what they're
talking about"

And with the licensing costs you save, you can even go to the next
level and hire truly exceptional consultants who also lurk about on
these lists.

And Robert, is your email really ***@lastname.net? If you want
to participate, be sure your email replies will work.

[Jasen Betts]

Post by Robert Paresi
Hello,
We have 700 user install base using Sybase SQL Anywhere 9.02
We are looking at migrating these installations over to PostGres
1. Very Very Short Answer Please - why should we?

postgres is the best database in the universe. :)

Post by Robert Paresi
2. Does anyone have a utility or migration application to read SQL Anywhere
to go to PostGres

sed ? Any stock utility is likely to make choices that are sub optimal for
your needs. with 700 changeovers pending I would want complete control over
how each column is handled.

Post by Robert Paresi
3. Does PostGres handle column descriptions (ie: you can give each column a
50 character description) and then access it via SQL Result Set (like I can
do in Sybase)

you can put descriptions on columns, I have not hit a length limit.
(probably several megabytes like other text fields).

Getting descriptions in the same same result set as column data is probably
impractical (see length limit for a possible reason why this has not been
implemented). fetching them separately en-masse and caching them or fetching
or individually afterwards should not be greatly difficult, resultsets are
returned with the column and table indicated.

Post by Robert Paresi
4. Is there any Date/TimeStamp issues and conversions I need to know about.
I use simply a DATE field and a TIME field - but do not use DATE/TIME stamp
fields together.

time goes upto 24:00:00.999999 just incase you come across a day that long.
so unless that's going to cause problems there should be no issues.
the range supported by date is starts in pre-history and continues
for a few million years into the future.

Post by Robert Paresi
5. What UI/Developer tools (GUI) are available to manage the database as
well as add/change columns rather than doing it via SQL commands.

I hear there are several, But I can't understand why someone would want to
perform non-graphical tasks using a GUI.

[Robert Paresi]

Post by Jasen Betts
I hear there are several, But I can't understand why someone would want to
perform non-graphical tasks using a GUI.

Because it is easier.

I've managed to do everything I need - as well as write a full query
editor/report writer for PostGreSQL.

It is being beta tested now, and I plan on releasing it within the next 2 or
3 weeks.

It adds about 8 extensions to the PostGreSQL language - and does many
things.

You'll see why it is nice to use a nice/pleasant GUI interface.

ie:

/* Check to make sure all email addresses have valid domain names */
select lastname,firstname,email from profiles
domain check on email

OR

/* return a list, subtotalling adults and child by state */
select state,lastname,firstname,email,adults,child from profile
order by state
subtotal adults,child on state

OR

/* highlight all credits in red */
select company,balance from accounts
highlight balance where balance < 0 bgcolor=COLOR:RED


-Robert

Post by Jasen Betts
Hello,
We have 700 user install base using Sybase SQL Anywhere 9.02
We are looking at migrating these installations over to PostGres
1. Very Very Short Answer Please - why should we?

postgres is the best database in the universe. :)

Post by Jasen Betts
2. Does anyone have a utility or migration application to read SQL Anywhere
to go to PostGres

sed ? Any stock utility is likely to make choices that are sub optimal for
your needs. with 700 changeovers pending I would want complete control over
how each column is handled.

Post by Jasen Betts
3. Does PostGres handle column descriptions (ie: you can give each column a
50 character description) and then access it via SQL Result Set (like I can
do in Sybase)

you can put descriptions on columns, I have not hit a length limit.
(probably several megabytes like other text fields).

Getting descriptions in the same same result set as column data is probably
impractical (see length limit for a possible reason why this has not been
implemented). fetching them separately en-masse and caching them or fetching
or individually afterwards should not be greatly difficult, resultsets are
returned with the column and table indicated.

Post by Jasen Betts
4. Is there any Date/TimeStamp issues and conversions I need to know about.
I use simply a DATE field and a TIME field - but do not use DATE/TIME stamp
fields together.

time goes upto 24:00:00.999999 just incase you come across a day that long.
so unless that's going to cause problems there should be no issues.
the range supported by date is starts in pre-history and continues
for a few million years into the future.

Post by Jasen Betts
5. What UI/Developer tools (GUI) are available to manage the database as
well as add/change columns rather than doing it via SQL commands.

I hear there are several, But I can't understand why someone would want to
perform non-graphical tasks using a GUI.
--
⚂⚃ 100% natural
--
Sent via pgsql-general mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Dmitriy Igrishin]

Post by Jasen Betts

Post by Robert Paresi
Hello,
We have 700 user install base using Sybase SQL Anywhere 9.02
We are looking at migrating these installations over to PostGres
1. Very Very Short Answer Please - why should we?

postgres is the best database in the universe. :)

Post by Robert Paresi
2. Does anyone have a utility or migration application to read SQL

Anywhere

Post by Robert Paresi
to go to PostGres

sed ? Any stock utility is likely to make choices that are sub optimal for
your needs. with 700 changeovers pending I would want complete control over
how each column is handled.

Post by Robert Paresi
3. Does PostGres handle column descriptions (ie: you can give each

column a

Post by Robert Paresi
50 character description) and then access it via SQL Result Set (like I

can

Post by Robert Paresi
do in Sybase)

you can put descriptions on columns, I have not hit a length limit.
(probably several megabytes like other text fields).
Getting descriptions in the same same result set as column data is probably
impractical (see length limit for a possible reason why this has not been
implemented). fetching them separately en-masse and caching them or fetching
or individually afterwards should not be greatly difficult, resultsets are
returned with the column and table indicated.

Post by Robert Paresi
4. Is there any Date/TimeStamp issues and conversions I need to know

about.

Post by Robert Paresi
I use simply a DATE field and a TIME field - but do not use DATE/TIME

stamp

Post by Robert Paresi
fields together.

time goes upto 24:00:00.999999 just incase you come across a day that long.
so unless that's going to cause problems there should be no issues.
the range supported by date is starts in pre-history and continues
for a few million years into the future.

Post by Robert Paresi
5. What UI/Developer tools (GUI) are available to manage the database as
well as add/change columns rather than doing it via SQL commands.

I hear there are several, But I can't understand why someone would want to
perform non-graphical tasks using a GUI.

I can't understand how to develop and maintain even small
data model without diagramming tool which can represent
entities and relationships between them.
Using pure psql and paper with a pen - is nothing more than LOL.

I recommend dbWrench as a diagramming tool for Postgres.

Post by Jasen Betts
--
⚂⚃ 100% natural
--
http://www.postgresql.org/mailpref/pgsql-general

[Gary Chambers]

Post by Dmitriy Igrishin
I recommend dbWrench as a diagramming tool for Postgres.

I can also recommend SchemaSpy (http://schemaspy.sourceforge.net/), a superb
command line-based schema browser and ERD generator.

-- Gary Chambers