From: | Craig Ringer <craig(at)postnewspapers(dot)com(dot)au> |
---|---|
To: | Kusuma Pabba <kusumap(at)ncoretech(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: hi all |
Date: | 2009-02-18 05:18:19 |
Message-ID: | 499B9A1B.1020105@postnewspapers.com.au |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2009-02-18 06:30:33 | Re: Appending \o output instead of overwriting the output file |
Previous Message | Scott Marlowe | 2009-02-18 05:11:03 | Re: transfering tables into other schema |