Kusuma Pabba wrote:
> when creating tables,
>
> in my sql i have used create table
By "my SQL" I assume you mean MySQL? It took me a minute to figure out
that you didn't mean "in my sql code" but rather "in the product MySQL".
Having read your proposed table definition: I very strongly recommend
that you read the PostgreSQL manual, at least the parts on the query
language, data types, supported functions, and DML. It will save you a
lot of hassle and you will learn a lot.
> CREATE TABLE users (
> user_id int(11) NOT NULL serial,
serial is a pseudo-type. You'd use it instead of `int' here, eg:
user_id SERIAL
It's actually almost equivalent shorthand for:
CREATE SEQUENCE users_id_seq;
CREATE TABLE users (
user_id integer default nextval('users_id_seq')
> date_created datetime NOT NULL default '0000-00-00 00:00:00',
PostgreSQL doesn't have a "datetime" type. You're probably looking for
"timestamp".
MySQL has a bizarre rule where the first datetime/timestamp column in a
table is automatically set to the time of record creation when a record
is inserted. PostgreSQL does not do this. You probably want something like:
date_created timestamp not null default current_timestamp,
A more robust approach that protects this field from being messed with
by the application would use a trigger (or, in Pg 8.4, column-level
permissions - HOORAY!).
> changed_by int(11) default NULL,
Pg doesn't have digit counts on integer types. Just use "integer", or if
you want a potentially really large value, use bigint.
> voided smallint(1) NOT NULL default '0',
You can use "boolean" here.
> KEY users_user_creator (creator),
Pg doesn't recognise this syntax. You're presumably trying to define an
index? If so, use CREATE INDEX after you define the table.
--
Craig Ringer