Re: Scheme conversion MySQL to PGSQL

[Melvin Davidson <melvin6925@xxxxxxxxx>]

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

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.

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.