Message-ID: <434394B3.4070009@archonet.com>
Date: Wed, 05 Oct 2005 09:54:11 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla Thunderbird 1.0.6 (X11/20050912)
MIME-Version: 1.0
To: "R, Rajesh (STSD)" <rajesh.r2@hp.com>
Cc: pgsql-hackers@postgresql.org, pgsql-performance@postgresql.org
Subject: Re: Query in SQL statement
References: 
 <5CBFB210D9870F40B9E5A0FBD31F3A770283AC48@bgeexc01.asiapacific.cpqcorp.net>
In-Reply-To: 
 <5CBFB210D9870F40B9E5A0FBD31F3A770283AC48@bgeexc01.asiapacific.cpqcorp.net>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

R, Rajesh (STSD) wrote:
>  
> Am trying to port a mysql statement to postgres.
> 
> Please help me in finding the error in this,

Can I recommend the reference section of the manuals for this sort of 
thing? There is an excellent section detailing the valid SQL for the 
CREATE TABLE command.

Also - the pgsql-hackers list is for discussion of database development, 
and the performance list is for performance problems. This would be 
better posted on pgsql-general or -sql or -novice.

> CREATE SEQUENCE ai_id;

This line is causing the first error:
 > ERROR:  relation "ai_id" already exists

That's because you've already successfully created the sequence, so it 
already exists. Either drop it and recreate it, or stop trying to 
recreate it.

> CREATE TABLE badusers (
>   id int DEFAULT nextval('ai_id') NOT NULL,
>   UserName varchar(30),
>   Date  datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,

Well, "Date" is a type-name, "datetime" isn't and even if it was 
"0000-00-00" isn't a valid date is it?

>   Reason varchar(200),
>   Admin varchar(30) DEFAULT '-',
>   PRIMARY KEY (id),
>   KEY UserName (UserName),
>   KEY Date (Date)

The word "KEY" isn't valid here either - are you trying to define an 
index? If so, see the "CREATE INDEX" section of the SQL reference.

http://www.postgresql.org/docs/8.0/static/sql-commands.html

If you reply to this message, please remove the pgsql-hackers CC:
--
   Richard Huxton
   Archonet Ltd