Re: hi all - Mailing list pgsql-general

From Craig Ringer
Subject Re: hi all
Date
Msg-id 499B9A1B.1020105@postnewspapers.com.au
Whole thread Raw
In response to hi all  (Kusuma Pabba <kusumap@ncoretech.com>)
List 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

pgsql-general by date:

Previous
From: Scott Marlowe
Date:
Subject: Re: transfering tables into other schema
Next
From: Tom Lane
Date:
Subject: Re: Appending \o output instead of overwriting the output file