Thread: Scheme conversion MySQL to PGSQL

Scheme conversion MySQL to PGSQL

From

"Michelle Konzack"

Date:

24 December 2017, 23:52:39

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

Re: Scheme conversion MySQL to PGSQL

From

James Keener

Date:

25 December 2017, 00:04:13

What are the errors you're getting?

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

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

Jim

On December 24, 2017 12:52:39 PM EST, 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<

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<

--
Sent from my Android device with K-9 Mail. Please excuse my brevity.

Re: Scheme conversion MySQL to PGSQL

From

Andreas Kretschmer

Date:

25 December 2017, 00:18:04

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

Re: Scheme conversion MySQL to PGSQL

From

Melvin Davidson

Date:

25 December 2017, 00:20:55

On Sun, Dec 24, 2017 at 1:04 PM, James Keener <jim@jimkeener.com> wrote:

On December 24, 2017 12:52:39 PM EST, 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<

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<

--
Sent from my Android device with K-9 Mail. Please excuse my brevity.

In PostgreSQL it would be:

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

CREATE TABLE sqmf_thread (
thread_id serial NOT NULL,
forum_id integer NOT NULL,
thread_login varchar(70) NOT NULL,
thread_date timestamp NOT NULL,
thread_title varchar(200) NOT NULL,
thread_content text NOT NULL,
nb_view integer NOT NULL default '0',
nb_post integer NOT NULL default '1',
last_post_date timestamp NOT NULL,
last_post_login varchar(70) NOT NULL,
CONSTRAINT sqmf_thread_pk PRIMARY KEY (thread_id)

);

CREATE INDEX sqmf_thread_idx

ON sqmf_thread

USING BTREE (forum_id);

Note: in PostgreSQL datetime is timestamp.

Also sqmf_stat_pk, sqmf_stat_uq, sqmf_thread_pk and sqmf_thread_idx are just suggested names, but all constraint & index names must be unique

https://www.postgresql.org/docs/9.6/static/sql-createtable.html

In the futuire, please include PostgreSQL version & O/S

Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

Re: Scheme conversion MySQL to PGSQL

From

"Igal @ Lucee.org"

Date:

25 December 2017, 02:54:46

On 12/24/2017 10:18 AM, Andreas Kretschmer wrote:

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 ...

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?

Thanks,

Igal Sapir
Lucee Core Developer
Lucee.org

Re: Scheme conversion MySQL to PGSQL

From

"Michelle Konzack"

Date:

25 December 2017, 03:04:21

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

Re: Scheme conversion MySQL to PGSQL

From

"Michelle Konzack"

Date:

25 December 2017, 03:20:38

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

Re: Scheme conversion MySQL to PGSQL

From

"Igal @ Lucee.org"

Date:

25 December 2017, 03:25:49

Michelle,

On 12/24/2017 1:20 PM, Michelle Konzack wrote:

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,

Igal Sapir
Lucee Core Developer
Lucee.org

Re: Scheme conversion MySQL to PGSQL

From

Alban Hertroys

Date:

25 December 2017, 16:13:40

> 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.