On Sun, Dec 24, 2017 at 1:04 PM, James Keener <j...@jimkeener.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 < > linux4miche...@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.
