Обсуждение: Scheme conversion MySQL to PGSQL

Hello *

I try to convert a Database scheme from mySQL to pgSQL and have
problems  with the line:

      KEY post_date     (post_date)

and later probably with the lines

      UNIQUE KEY user   (stat_login)
and
      KEY forum_id      (forum_id)

too.  How to solv this prolem?

Thanks
Michelle

----8<----------------------------------------------------------------
CREATE TABLE sqmf_forum (
      forum_id          serial          NOT NULL,
      forum_name        varchar(50)     NOT NULL,
      forum_description varchar(250)    NOT NULL,
      forum_visible     integer         NOT NULL default '0',
      display_order     integer         NOT NULL default '1',
      PRIMARY KEY       (forum_id)
);

CREATE TABLE sqmf_post (
      post_id           serial          NOT NULL,
      thread_id         integer         NOT NULL,
      post_login        varchar         NOT NULL,
      post_date         timestamp       NOT NULL,
      post_content      text            NOT NULL,
      PRIMARY KEY       (post_id),
      KEY post_date     (post_date)
);

CREATE TABLE sqmf_stat (
      stat_login        varchar(70)     NOT NULL,
      stat_post         integer         default '1',
      stat_thread       integer         default '1',
      PRIMARY KEY       (stat_login),
      UNIQUE KEY user   (stat_login)
);

CREATE TABLE sqmf_thread (
      thread_id         serial          NOT NULL,
      forum_id          integer         NOT NULL,
      thread_login      varchar(70)     NOT NULL,
      thread_date       datetime        NOT NULL,
      thread_title      varchar(200)    NOT NULL,
      thread_content    longtext        NOT NULL,
      nb_view           integer         NOT NULL default '0',
      nb_post           integer         NOT NULL default '1',
      last_post_date    datetime        NOT NULL,
      last_post_login   varchar(70)     NOT NULL,
      PRIMARY KEY       (thread_id),
      KEY forum_id      (forum_id)
);
----8<----------------------------------------------------------------


-- 
Michelle Konzack
00372-54541400

On 24 December 2017 18:52:39 CET, Michelle Konzack <linux4michelle@tamay-dogan.net> wrote:
>Hello *
>
>I try to convert a Database scheme from mySQL to pgSQL and have
>problems  with the line:
>
>      KEY post_date     (post_date)
>
>and later probably with the lines
>
>      UNIQUE KEY user   (stat_login)
>and
>      KEY forum_id      (forum_id)
>
>too.  How to solv this prolem?
>
>Thanks
>Michelle
>
>----8<----------------------------------------------------------------

That's just indexe. Create them later with create index ...

Regards, Andreas


--
2ndQuadrant - The PostgreSQL Support Company

On 24 December 2017 18:52:39 CET, Michelle Konzack <linux4michelle@tamay-dogan.net> wrote:

I try to convert a Database scheme from mySQL to pgSQL and have
problems  with the line:
    KEY post_date     (post_date)

and later probably with the lines
    UNIQUE KEY user   (stat_login)

That's just indexe. Create them later with create index ...

Hello James,

Am "=!/-!"-"$ hackte James Keener in die Tasten:
> What are the errors you're getting?

psql (9.1.24lts2)
Type "help" for help.

postgres=# DROP DATABASE sqmail_forum_tdnet;
DROP DATABASE
postgres=# \i
/srv/CONFIG_webmail.tamay-dogan.net/htdocs/plugins/forum/database.sql
CREATE DATABASE
ALTER DATABASE
GRANT
You are now connected to database "sqmail_forum_tdnet" as user
"postgres".
psql:/srv/CONFIG_webmail.tamay-dogan.net/htdocs/plugins/forum/database.sql:14:
NOTICE:  CREATE TABLE will create implicit sequence
"sqmf_forum_forum_id_seq" for serial column "sqmf_forum.forum_id"
psql:/srv/CONFIG_webmail.tamay-dogan.net/htdocs/plugins/forum/database.sql:14:
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"sqmf_forum_pkey" for table "sqmf_forum"
CREATE TABLE
psql:/srv/CONFIG_webmail.tamay-dogan.net/htdocs/plugins/forum/database.sql:24:
NOTICE:  CREATE TABLE will create implicit sequence
"sqmf_post_post_id_seq" for serial column "sqmf_post.post_id"
psql:/srv/CONFIG_webmail.tamay-dogan.net/htdocs/plugins/forum/database.sql:24:
ERROR:  type "post_date" does not exist
LINE 8:       KEY post_date (post_date)
                  ^
psql:/srv/CONFIG_webmail.tamay-dogan.net/htdocs/plugins/forum/database.sql:32:
ERROR:  syntax error at or near "user"
LINE 6:       UNIQUE user (stat_login)
                     ^
psql:/srv/CONFIG_webmail.tamay-dogan.net/htdocs/plugins/forum/database.sql:47:
NOTICE:  CREATE TABLE will create implicit sequence
"sqmf_thread_thread_id_seq" for serial column "sqmf_thread.thread_id"
psql:/srv/CONFIG_webmail.tamay-dogan.net/htdocs/plugins/forum/database.sql:47:
ERROR:  type "forum_id" does not exist
LINE 13:       KEY forum_id (forum_id)
                   ^

> I don't think unique key is the correct syntax.
> https://www.postgresql.org/docs/current/static/indexes-unique.html

I found it already. it is only "UNIQUE"

> I also don't think the key can be named the same as the field.

This is, what I have to figure out.

> Jim

-- 
Michelle Konzack
00372-54541400

Hello Igal

Am 2017-12-24 hackte Igal @ Lucee.org in die Tasten:
> I agree with Andreas.  First create the tables, then create the
> indexes.  If you can copy the data before creating the indexes then
> you
> will probably save some time on all of the INSERTs.
>
> I am doing a similar thing migrating from SQL Server, and I am
> considering to publish an open source Migrator.
>
> Can you tell me about some of the data types that you had to change
> moving from MySQL to Postgres?

I convert only those four CREATE TABLE from mySQL to pgSQL which
is the forum pluging for squirrelmail.

auto_increment  ->   serial
int(NN)         ->   int
datetime        ->   timestamptz  (attention:  it is NOT timestamp)
longtext        ->   text

ENGINE=MyISAM   hast to be removed.

This are things, which I found on the internet.

Greetings
Michelle

-- 
Michelle Konzack
00372-54541400

Hello Igal

Am 2017-12-24 hackte Igal @ Lucee.org in die Tasten:

I am doing a similar thing migrating from SQL Server, and I am
considering to publish an open source Migrator.

Can you tell me about some of the data types that you had to change
moving from MySQL to Postgres?

I convert only those four CREATE TABLE from mySQL to pgSQL which
is the forum pluging for squirrelmail.

auto_increment  ->   serial
int(NN)         ->   int
datetime        ->   timestamptz  (attention:  it is NOT timestamp)
longtext        ->   text

Thank you.  My migration tool (written in Java) already supports migrating a SQL Server database to Postgres with most data types.

Perhaps I will improve it further when I have some more time to support MySQL source databases as well, in which case I will use the information that you sent as a starting point.

Best,

> On 24 Dec 2017, at 18:52, Michelle Konzack <linux4michelle@tamay-dogan.net> wrote:

> CREATE TABLE sqmf_stat (
>      stat_login        varchar(70)     NOT NULL,
>      stat_post         integer         default '1',
>      stat_thread       integer         default '1',
>      PRIMARY KEY       (stat_login),
>      UNIQUE KEY user   (stat_login)
> );

Just a minor observation, but a PRIMARY KEY is UNIQUE by definition. There's no need for that UNIQUE key "user" on the
samefield. 

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.