Re: hi all - Mailing list pgsql-general

Kusuma Pabba wrote:
> when creating tables,
>
> in my sql i have used create table
> CREATE TABLE `users` (
>  `user_id` int(11) NOT NULL auto_increment,
>  `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',
>  `changed_by` int(11) default NULL,
>  `date_changed` datetime default NULL,
>  `voided` tinyint(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`),
>  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`)
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
>
>
> while in pgsql i am thinking of to use the same as follows:
>
> CREATE TABLE users (
>  user_id int(11) NOT NULL serial,
>  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',
>  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),
>  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
>
>
> Regards
> kusuma.p
>
CREATE TABLE users (
      user_id serial NOT NULL ,
      user_name varchar(50) NOT NULL,
      first_name varchar(50),
      middle_name varchar(50),
      last_name varchar(50),
      password varchar(50),
      salt varchar(50),
      secret_question varchar(255),
      secret_answer varchar(255),
      creator int,
      date_created timestamp  NOT NULL default to_timestamp('0000-00-00
00:00:00','YYYY-MM-DD HH24:MI:SS'),
      changed_by int,
      date_changed timestamp,
      voided smallint NOT NULL default '0',
      voided_by int,
      date_voided timestamp,
      void_reason varchar(255),
      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);



postgres=# \d users
                                                           Table
"public.users"
     Column      |            Type
|                                         Modifiers

-----------------+-----------------------------+-------------------------------------------------------------------------------------------
 user_id         | integer                     | not null default
nextval('users_user_id_seq'::regclass)
 user_name       | character varying(50)       | not null
 first_name      | character varying(50)       |
 middle_name     | character varying(50)       |
 last_name       | character varying(50)       |
 password        | character varying(50)       |
 salt            | character varying(50)       |
 secret_question | character varying(255)      |
 secret_answer   | character varying(255)      |
 creator         | integer                     |
 date_created    | timestamp without time zone | not null default
to_timestamp('0000-00-00 00:00:00'::text, 'YYYY-MM-DD HH24:MI:SS'::text)
 changed_by      | integer                     |
 date_changed    | timestamp without time zone |
 voided          | smallint                    | not null default
0::smallint
 voided_by       | integer                     |
 date_voided     | timestamp without time zone |
 void_reason     | character varying(255)      |
Indexes:
    "users_pkey" PRIMARY KEY, btree (user_id)
    "users_user_creator" btree (creator)
    "users_user_who_changed_user" btree (changed_by)
    "users_user_who_voided_user" btree (voided_by)
Foreign-key constraints:
    "users_user_creator" FOREIGN KEY (creator) REFERENCES users(user_id)
    "users_user_who_changed_user" FOREIGN KEY (changed_by) REFERENCES
users(user_id)
    "users_user_who_voided_user" FOREIGN KEY (voided_by) REFERENCES
users(user_id)


postgres=# insert into users (user_name) values ('foo');
INSERT 0 1
postgres=# select * from users;
 user_id | user_name | first_name | middle_name | last_name | password |
salt | secret_question | secret_answer | creator |
date_created      | changed_by | date_changed | voided | voided_by |
date_voided | void_reason

---------+-----------+------------+-------------+-----------+----------+------+-----------------+---------------+---------+------------------------+------------+--------------+--------+-----------+-------------+-------------
       1 | foo       |            |             |           |
|      |                 |               |         | 0001-01-01 00:00:00
BC |            |              |      0 |           |             |
(1 row)

postgres=#