Re: hi all

On Tue, Feb 17, 2009 at 5:54 AM, Kusuma Pabba <kusumap@ncoretech.com> wrote:
> while in pgsql i am thinking of to use the same as follows:
>
> CREATE TABLE users (
>  user_id int(11) NOT NULL serial,

I see this construct in mysql all the time.  Funny thing is most mysql
users think it means an 11 character wide int, i.e 12345678901 would
be a legitimate number to store in it.  In reality it just means to
justify the number on output with left padded spaces to 11 characters.

Either way, it won't work in pgsql.  Same goes for smallint(1) etc.
ints don't have precision, and only MySQL supports a syntax that
defines the OUTPUT of the type and not the actual precision, like it
seems to.

>  user_name varchar(50) NOT NULL,
>  first_name varchar(50) default NULL,
>  middle_name varchar(50) default NULL,
>  last_name varchar(50) default NULL,
>  password varchar(50) default NULL,
>  salt varchar(50) default NULL,
>  secret_question varchar(255) default NULL,
>  secret_answer varchar(255) default NULL,
>  creator int(11) default NULL,
>  date_created datetime NOT NULL default '0000-00-00 00:00:00',

No such type as datetime.  Perhaps you meant timestamp? Or maybe date
(i.e. no time portion)

>  changed_by int(11) default NULL,
>  date_changed datetime default NULL,
>  voided smallint(1) NOT NULL default '0',
>  voided_by int(11) default NULL,
>  date_voided datetime default NULL,
>  void_reason varchar(255) default NULL,
>  PRIMARY KEY  (user_id),
>  KEY users_user_creator (creator),
>  KEY users_user_who_changed_user (changed_by),
>  KEY users_user_who_voided_user (voided_by),

The KEY keyword is not part of pgsql.  You'll need to create separate indexes.

>  CONSTRAINT users_user_creator FOREIGN KEY (creator) REFERENCES users
> (user_id),
>  CONSTRAINT users_user_who_changed_user FOREIGN KEY (changed_by) REFERENCES
> users (user_id),
>  CONSTRAINT users_user_who_voided_user FOREIGN KEY (voided_by) REFERENCES
> users (user_id)
> ) ;
>
> will that be valid to create a table like this
> if no what all have to  be replaced
> thanks for any help

I'd recommend just running it and fixing errors until it works.
Here's the basic working version I came up with, might or might not do
what you need:

CREATE TABLE users (
 user_id serial NOT NULL,
 user_name varchar(50) NOT NULL,
 first_name varchar(50) default NULL,
 middle_name varchar(50) default NULL,
 last_name varchar(50) default NULL,
 password varchar(50) default NULL,
 salt varchar(50) default NULL,
 secret_question varchar(255) default NULL,
 secret_answer varchar(255) default NULL,
 creator int default NULL,
 date_created date NOT NULL,
 changed_by int default NULL,
 date_changed date default NULL,
 voided smallint NOT NULL default '0',
 voided_by int default NULL,
 date_voided timestamp default NULL,
 void_reason varchar(255) default NULL,
 PRIMARY KEY  (user_id),
 CONSTRAINT users_user_creator FOREIGN KEY (creator) REFERENCES users (user_id),
 CONSTRAINT users_user_who_changed_user FOREIGN KEY (changed_by)
REFERENCES users (user_id),
 CONSTRAINT users_user_who_voided_user FOREIGN KEY (voided_by)
REFERENCES users (user_id)
) ;

create index users_user_creator on users (creator);
create index users_user_who_changed_user on users (changed_by);
create index users_user_who_voided_user on users (voided_by);