0

We are currently working on a project that requires transferring a MySQL database to PostgreSQL. I have never worked with PostgreSQL.

  • Would my MySQL knowledge be enough to handle PostgreSQL?
  • Are they the same and use the same syntax with queries and such? Or are they two different things?
  • What I am hearing from our group leader is that PostgreSQL is better than MySQL when working with coordinates. Why is that and is it true?
Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Sahl
  • 27
  • 4

3 Answers3

1

Of course - if you know SQL and relational databases well you can manage any database. However, there are syntactical differences that are very important.

PostgreSQL is more like Oracle. You might find that it takes a while to get used to the way that it does certain things.

But it's a wonderful database. You should have no issues.

duffymo
  • 305,152
  • 44
  • 369
  • 561
  • Thank you for the response. Is it also possible to access PostgreSQL via PHPMyAdmin or any other graphical interface like with MySQL? – Sahl Jan 19 '14 at 17:29
  • PostgreSQL has its own admin client: http://www.pgadmin.org/ – duffymo Jan 19 '14 at 17:31
0

Well all SQL databases share common language to talk to them (obviously it is SQL) when you are working normally with them. But when it comes to advanced features and administration each database has its own way.

So as an application developer who wants to execute DML statements, you would not encounter much difficulty. But if you want to develop stored procedures, triggers... you shall learn a new syntax and language.

By the way according to my studies, PostgreSQL has a more advanced query analyzer (parser) which can execute a query faster. (Well it is not a always rule, PostgreSQL just implements more algorithms to handle joins).

Amir Pashazadeh
  • 7,170
  • 3
  • 39
  • 69
0

Would my MySQL knowledge be enough to handle PostgreSQL?

I don't think so, a lot of advanced features are handled differently.

Are they the same and use the same syntax with queries and such? Or are they 2 different things?

They are two different things, based on the same SQL standard. Like Windows and Linux are POSIX operating systems.

The other question is that what I am hearing from our group leader is that PostgreSQL is better than MySQL when working with coordinates. I am not really sure why and how this is true.

Indeed, PostgreSQL have extra indexes (e.g. GiST, GIN) and many extensions, some of which are readily available. PostGIS comes to mind for working with coordinates.

ArtemGr
  • 11,684
  • 3
  • 52
  • 85
  • Err... References of mysql subqueries that don't work with Postgres without a CTE please? – Denis de Bernardy Jan 19 '14 at 13:54
  • 1
    @Denis: I've encountered the subquery problem when importing a legacy MySQL database into PostgreSQL using foreign tables. See http://pastebin.com/49LyeNYR. The problem is persistent, but I can't repeat it using only the native (non-foreign) PostgreSQL tables. Glad to know that subqueries work as expected with non-foreign tables. – ArtemGr Jan 19 '14 at 18:14
  • Interesting... By foreign tables, you mean foreign data wrappers, right? – Denis de Bernardy Jan 19 '14 at 18:34
  • Did you report it to pg-bugs or the fdw's maintainer? If not, I think you should. – Denis de Bernardy Jan 19 '14 at 19:52
  • 1
    Okay, I'll build a better test case and will report it to mysql_fdw and maybe pg-bugs. – ArtemGr Jan 19 '14 at 20:05
  • 1
    The fdw maintainer should likely be enough. David Page is a regular on the pg hackers list. http://pgxn.org/dist/mysql_fdw/1.0.0/ – Denis de Bernardy Jan 19 '14 at 20:12
  • @Denis: https://github.com/dpage/mysql_fdw/issues/11 – ArtemGr Jan 20 '14 at 07:36