PostgreSQL

[Eric]

I need anyone knowing postgresql to look over the following and letting me know if there are any mistakes, any improvements that can be made, etc.

I'm converting MySQL to PostgreSQL;

postgresql

CREATE SEQUENCE admin_adminid_seq;
CREATE TABLE admin (
	adminid integer DEFAULT nextval('admin_adminid_seq') NOT NULL,
	username VARCHAR(32) NOT NULL,
	password CHAR(32) NOT NULL,
	primary key (adminid)
);
CREATE INDEX admin_username_idx ON admin USING btree (username);

CREATE SEQUENCE config_id_seq;
CREATE TABLE config (
	id integer DEFAULT nextval('config_id_seq') NOT NULL,
	name VARCHAR(25) NOT NULL,
	value VARCHAR(255) NOT NULL,
	primary key (id)
);
CREATE INDEX config_name_idx ON config USING btree (name);

CREATE SEQUENCE categories_catid_seq;
CREATE TABLE categories (
	catid integer DEFAULT nextval('categories_catid_seq') NOT NULL,
	title VARCHAR(100) NOT NULL,
	description TEXT NOT NULL,
	primary key (catid)
);

CREATE SEQUENCE domains_domainid_seq;
CREATE TABLE domains (
	domainid integer DEFAULT nextval('domains_domainid_seq') NOT NULL,
	catid INT(10) UNSIGNED NOT NULL DEFAULT '0',
	domain VARCHAR(100) NOT NULL,
	description TEXT NOT NULL,
	registrar VARCHAR(100) NOT NULL,
	expiry INT(11) UNSIGNED NOT NULL DEFAULT '0',
	price DECIMAL(10,2) NOT NULL DEFAULT '0.00',
	status varchar(13) check (state in ('For Sale', 'Not For Sale', 'Make Offer', 'Sold')) NOT NULL,
	added INT(11) UNSIGNED NOT NULL DEFAULT '0',
	issite smallint(1) UNSIGNED NOT NULL DEFAULT '0',
	hidden smallint(1) UNSIGNED NOT NULL DEFAULT '0',
	primary key (domainid)
);

CREATE INDEX domains_catid_idx ON domains USING btree (catid);
CREATE INDEX domains_hidden_idx ON domains USING btree (hidden);

Original mysql

CREATE TABLE admin (
	adminid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	username VARCHAR(32) NOT NULL,
	password CHAR(32) NOT NULL,
	PRIMARY KEY (adminid),
	KEY username (username)
);

CREATE TABLE config (
	id INT UNSIGNED NOT NULL AUTO_INCREMENT,
	name VARCHAR(25) NOT NULL,
	value VARCHAR(255) NOT NULL,
	PRIMARY KEY (id),
	KEY name (name)
);

CREATE TABLE categories (
	catid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	title VARCHAR(100) NOT NULL,
	description TEXT NOT NULL,
	PRIMARY KEY (catid)
);

CREATE TABLE domains (
	domainid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	catid INT(10) UNSIGNED NOT NULL DEFAULT '0',
	domain VARCHAR(100) NOT NULL,
	description TEXT NOT NULL,
	registrar VARCHAR(100) NOT NULL,
	expiry INT(11) UNSIGNED NOT NULL DEFAULT '0',
	price DECIMAL(10,2) NOT NULL DEFAULT '0.00',
	status ENUM('For Sale', 'Not For Sale', 'Make Offer', 'Sold') NOT NULL,
	added INT(11) UNSIGNED NOT NULL DEFAULT '0',
	issite TINYINT(1) UNSIGNED NOT NULL DEFAULT '0',
	hidden TINYINT(1) UNSIGNED NOT NULL DEFAULT '0',
	PRIMARY KEY (domainid),
	KEY catid (catid),
	KEY hidden (hidden)
);

Any help would be much appreciated

 

[Eric]

*bump* no one eh?

 

[Kate]

Didn't test but looks OK
I usually write something along these lines (must be equivalent)

CREATE SEQUENCE admin_adminid_seq;
CREATE TABLE admin (
     adminid    integer PRIMARY KEY DEFAULT nextval('admin_adminid_seq'),
...

 

[Eric]

Thanks Kate

I suppose I should find somewhere I can test this.

--
EDIT:

We're glad that you're fond of this member, but please give some rep points to some other members before giving it to sdsinc again.