Re: Query in SQL statement

From: "Obe, Regina DND\\MIS" <robe(dot)dnd(at)cityofboston(dot)gov>
To: "'R, Rajesh (STSD)'" <rajesh(dot)r2(at)hp(dot)com>, "'pgsql-hackers(at)postgresql(dot)org'" <pgsql-hackers(at)postgresql(dot)org>, "'pgsql-performance(at)postgresql(dot)org'" <pgsql-performance(at)postgresql(dot)org>, "'pgsql-novice(at)postgresql(dot)org'" <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Query in SQL statement
Date: 2005-09-29 13:30:17
Message-ID: 35F9812087218F47B050D41D1B58298B086F3D@dnd5.dnd.boston.cob
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-novice pgsql-performance

I think this question may be more appropriate for
pgsql-novice(at)postgresql(dot)org(dot)

Anyrate for the below. Sounds like you maybe already have a table or
sequence called ai_id;

Try doing a DROP SEQUENCE ai_id;

First

Also if you plan to use this sequence only for this table it would be better
to use serial8 which will automatically create the sequence for you. Then
you don't even need that first part. Also you should avoid naming fields
things like Date which tend to be keywords in many kinds of databases.

Try changing your logic to something like

CREATE TABLE badusers (
id serial8,
UserName varchar(30),
Date timestamp DEFAULT now() NOT NULL,
Reason varchar(200),
Admin varchar(30) DEFAULT '-',
PRIMARY KEY (id)
);

CREATE INDEX badusers_username
ON badusers
USING btree
(username);

CREATE INDEX badusers_date
ON badusers
USING btree
(date);

-----Original Message-----
From: R, Rajesh (STSD) [mailto:rajesh(dot)r2(at)hp(dot)com]
Sent: Thursday, September 29, 2005 9:05 AM
To: pgsql-hackers(at)postgresql(dot)org; pgsql-performance(at)postgresql(dot)org
Subject: [HACKERS] Query in SQL statement


Am trying to port a mysql statement to postgres.

Please help me in finding the error in this,

CREATE SEQUENCE ai_id;
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,
Reason varchar(200),
Admin varchar(30) DEFAULT '-',
PRIMARY KEY (id),
KEY UserName (UserName),
KEY Date (Date)
);

Am always getting foll. Errors,

ERROR: relation "ai_id" already exists
ERROR: syntax error at or near "(" at character 240

Thanks,
Rajesh R

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
message can get through to the mailing list cleanly

Browse pgsql-hackers by date

  From Date Subject
Next Message Tino Wildenhain 2005-09-29 13:40:08 Re: postgresql clustering
Previous Message Christopher Kings-Lynne 2005-09-29 13:28:38 Re: Query in SQL statement

Browse pgsql-novice by date

  From Date Subject
Next Message Ennio-Sr 2005-09-29 19:36:57 postgres arithmetic: raising to nth power
Previous Message ann hedley 2005-09-29 13:14:41 perl DBI::Pg metacharacter \i com_list.sql

Browse pgsql-performance by date

  From Date Subject
Next Message Gaetano Mendola 2005-09-29 13:46:52 Re: Monitoring Postgresql performance
Previous Message Gaetano Mendola 2005-09-29 13:29:30 Re: Delete query takes exorbitant amount of time