Re: Scheme conversion MySQL to PGSQL

On Sun, Dec 24, 2017 at 1:04 PM, James Keener <jim(at)jimkeener(dot)com> 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(at)tamay-dogan(dot)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.