0

We will migrate the database from mysql to postgresql in our product(through java). So we need to change the mysql query to postgresql query in java application. How to create the table i.e., databasename.tablename in postgresql. For mysql, we can directly create the table e.g create table information.employee. Here database name is "information" and table name is "employee" . Is it possible to achieve same query in postgresql.

I searched google it says cross database reference is not possible. Please help me. I saw pg_class table it contains the table names in the specific database, like wise databse and tables relationships are stored in any other table.

Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
Prathika Kesavan
  • 27
  • 6
  • Please clarify your question. If you want information on how to convert statements from MySQL to PostgrSQL, please provide some of the MySQL statements or specifications. If you want information on how to reference between what MySQL calls databases, then provide examples of how you do it today in MySQL. – zrvan Jan 04 '12 at 12:32
  • Hi zrvan, Thank you for your reply. Yes i want to convert the mysql statement in to postgresql. For example mysql has n number of databases.here they accessequery like select * from dbname.tablename; Is it possible to achieve the same query in postgresql? – Prathika Kesavan Jan 09 '12 at 09:21
  • prathika: Please edit your question and add some of the actual MySQL queries (or statements that are similar enough if the original contain sensitive information). If you need assistance with both table creation statements as well as actual data extraction queries, please add examples of both kinds, as they appear in their current, MySQL, form. – zrvan Jan 09 '12 at 09:54

2 Answers2

3

This is normally done using schemas rather than databases, which is more or less like how MySQL organizes it anyway.

Instead of 

create database xyz

use 

create schema xyz

When you create tables, create them:

create table xyz.myTable

you will need to update your search path to see them on the psql command line tool, or if you want to query them without using the schema explicitly. The default schema is public, so when you create a table without a schema name, it ends up in public. If you modify your search_path as below, the default schema becomes the first in the list: xyz.

set search_path=xyz,public,pg_catalog;

and you must not have spaces in that statement. You can do it globally for a user/role too:

alter role webuser set search_path=xyz,public,pg_catalog;

Also, don't forget that postgresql string matches are case sensitive by default (this one catches people out a lot). If you want to have different physical locations for the files for each schema, you can do that with tablespaces. If you have a look at the postgresql documentation page, they have info on how to do it, it's pretty easy.

PlexQ
  • 3,154
  • 2
  • 21
  • 26
  • One doesn't **need** to update his search_path to work with other schemas, they could be specified explicitly in the SQL statements (that's my preferred way of writing them BTW). Updating one's search_path is a convenience. – Milen A. Radev Jan 04 '12 at 13:15
  • This is true, you don't need to, but, if you want the relations to show up on the psql command line for things like \d, you'll need to, and it's pretty damn confusing when they don't show up, so I'd really just recommend doing it for sanity's sake -- I'll update to reflect. – PlexQ Jan 04 '12 at 13:42
2

database in MySQL == schema in PostgreSQL. So you will most probably want to migrate all your mysql dbs into one postgres db. Then you will be able to do "cross-database" queries.

See my answer to this question: Relationship between catalog, schema, user, and database instance

Community
  • 1
  • 1
filiprem
  • 6,721
  • 1
  • 29
  • 42