pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedQuery in SQL statement
10+ messages / 7 participants
[nested] [flat]
* Query in SQL statement
@ 2005-09-29 13:05 R, Rajesh (STSD) <rajesh.r2@hp.com>
2005-09-29 13:28 ` Re: Query in SQL statement Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2005-10-05 08:54 ` Re: Query in SQL statement Richard Huxton <dev@archonet.com>
0 siblings, 2 replies; 10+ messages in thread
From: R, Rajesh (STSD) @ 2005-09-29 13:05 UTC (permalink / raw)
To: pgsql-hackers@postgresql.org; pgsql-performance
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Query in SQL statement
2005-09-29 13:05 Query in SQL statement R, Rajesh (STSD) <rajesh.r2@hp.com>
@ 2005-09-29 13:28 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2005-09-30 23:48 ` Re: Query in SQL statement Jim C. Nasby <jnasby@pervasive.com>
1 sibling, 1 reply; 10+ messages in thread
From: Christopher Kings-Lynne @ 2005-09-29 13:28 UTC (permalink / raw)
To: R, Rajesh (STSD) <rajesh.r2@hp.com>; +Cc: pgsql-hackers@postgresql.org; pgsql-performance
> 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
You have just copied the Mysql code to Postgresql. It will in no way
work. Your default for 'Date' is illegal in postgresql and hence it
must allow NULLs. There is no such thing as a 'datetime' type. There
is no such thing as 'Key'. Also your mixed case identifiers won't be
preserved. You want:
CREATE TABLE badusers (
id SERIAL PRIMARY KEY,
UserName varchar(30),
Date timestamp,
Reason varchar(200),
Admin varchar(30) DEFAULT '-'
);
CREATE INDEX UserName_Idx ON badusers(Username);
CREATE INDEX Date_Idx ON badusers(Date);
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Query in SQL statement
2005-09-29 13:05 Query in SQL statement R, Rajesh (STSD) <rajesh.r2@hp.com>
2005-09-29 13:28 ` Re: Query in SQL statement Christopher Kings-Lynne <chriskl@familyhealth.com.au>
@ 2005-09-30 23:48 ` Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 0 replies; 10+ messages in thread
From: Jim C. Nasby @ 2005-09-30 23:48 UTC (permalink / raw)
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: R, Rajesh (STSD) <rajesh.r2@hp.com>; pgsql-hackers@postgresql.org; pgsql-performance
On Thu, Sep 29, 2005 at 09:28:38PM +0800, Christopher Kings-Lynne wrote:
>
> >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
>
> You have just copied the Mysql code to Postgresql. It will in no way
> work. Your default for 'Date' is illegal in postgresql and hence it
> must allow NULLs. There is no such thing as a 'datetime' type. There
> is no such thing as 'Key'. Also your mixed case identifiers won't be
> preserved. You want:
>
> CREATE TABLE badusers (
> id SERIAL PRIMARY KEY,
> UserName varchar(30),
> Date timestamp,
> Reason varchar(200),
> Admin varchar(30) DEFAULT '-'
> );
>
> CREATE INDEX UserName_Idx ON badusers(Username);
> CREATE INDEX Date_Idx ON badusers(Date);
Actually, to preserve the case you can wrap everything in quotes:
CREATE ...
"UserName" varchar(30)
Of course that means that now you have to do that in every statement
that uses that field, too...
SELECT username FROM badusers
ERROR
SELECT "UserName" FROM badusers
bad user
I suggest ditching the CamelCase and going with underline_seperators.
I'd also not use the bareword id, instead using bad_user_id. And I'd
name the table bad_user. But that's just me. :)
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Query in SQL statement
2005-09-29 13:05 Query in SQL statement R, Rajesh (STSD) <rajesh.r2@hp.com>
@ 2005-10-05 08:54 ` Richard Huxton <dev@archonet.com>
1 sibling, 0 replies; 10+ messages in thread
From: Richard Huxton @ 2005-10-05 08:54 UTC (permalink / raw)
To: R, Rajesh (STSD) <rajesh.r2@hp.com>; +Cc: pgsql-hackers@postgresql.org; pgsql-performance
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Query in SQL statement
@ 2005-09-29 13:30 Obe, Regina DND\MIS <robe.dnd@cityofboston.gov>
0 siblings, 0 replies; 10+ messages in thread
From: Obe, Regina DND\\MIS @ 2005-09-29 13:30 UTC (permalink / raw)
To: 'R, Rajesh (STSD)' <rajesh.r2@hp.com>; 'pgsql-hackers@postgresql.org' <pgsql-hackers@postgresql.org>; pgsql-performance; 'pgsql-novice@postgresql.org' <pgsql-novice@postgresql.org>
I think this question may be more appropriate for
pgsql-novice@postgresql.org.
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.r2@hp.com]
Sent: Thursday, September 29, 2005 9:05 AM
To: pgsql-hackers@postgresql.org; pgsql-performance@postgresql.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@postgresql.org so that your
message can get through to the mailing list cleanly
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [HACKERS] Query in SQL statement
@ 2005-10-01 19:51 Roger Hand <RHand@kailea.com>
2005-10-01 21:15 ` Re: [HACKERS] Query in SQL statement Tom Lane <tgl@sss.pgh.pa.us>
2005-10-04 20:43 ` Re: [HACKERS] Query in SQL statement Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 2 replies; 10+ messages in thread
From: Roger Hand @ 2005-10-01 19:51 UTC (permalink / raw)
To: Jim C. Nasby <jnasby@pervasive.com>; Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: R, Rajesh (STSD) <rajesh.r2@hp.com>; pgsql-hackers@postgresql.org; pgsql-performance
> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org
> [mailto:pgsql-performance-owner@postgresql.org]On Behalf Of Jim C. Nasby
> Sent: Friday, September 30, 2005 4:49 PM
> Subject: Re: [PERFORM] [HACKERS] Query in SQL statement
> I suggest ditching the CamelCase and going with underline_seperators.
> I'd also not use the bareword id, instead using bad_user_id. And I'd
> name the table bad_user. But that's just me. :)
I converted a db from MS SQL, where tables and fields were CamelCase, and
just lowercased the ddl to create the tables.
So table and fields names were all created in lowercase, but I didn't have to change
any of the application code: the SELECT statements worked fine with mixed case.
-- sample DDL
CREATE TABLE testtable
(
fieldone int4
)
insert into TestTable (fieldone) values (11);
-- These statements will both work:
-- lowercase
SELECT fieldone FROM testtable;
-- CamelCase
SELECT FieldOne FROM TestTable;
-Roger
> Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [HACKERS] Query in SQL statement
2005-10-01 19:51 Re: [HACKERS] Query in SQL statement Roger Hand <RHand@kailea.com>
@ 2005-10-01 21:15 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 10+ messages in thread
From: Tom Lane @ 2005-10-01 21:15 UTC (permalink / raw)
To: Roger Hand <RHand@kailea.com>; +Cc: Jim C. Nasby <jnasby@pervasive.com>; Christopher Kings-Lynne <chriskl@familyhealth.com.au>; R, Rajesh (STSD) <rajesh.r2@hp.com>; pgsql-hackers@postgresql.org; pgsql-performance
"Roger Hand" <RHand@kailea.com> writes:
>> I suggest ditching the CamelCase and going with underline_seperators.
>> I'd also not use the bareword id, instead using bad_user_id. And I'd
>> name the table bad_user. But that's just me. :)
> I converted a db from MS SQL, where tables and fields were CamelCase, and
> just lowercased the ddl to create the tables.
> So table and fields names were all created in lowercase, but I didn't have to change
> any of the application code: the SELECT statements worked fine with mixed case.
Yeah, the only time this stuff really bites you is if the application
sometimes double-quotes mixed-case names and sometimes doesn't. If it's
consistent then you don't have an issue ...
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [HACKERS] Query in SQL statement
2005-10-01 19:51 Re: [HACKERS] Query in SQL statement Roger Hand <RHand@kailea.com>
@ 2005-10-04 20:43 ` Jim C. Nasby <jnasby@pervasive.com>
1 sibling, 0 replies; 10+ messages in thread
From: Jim C. Nasby @ 2005-10-04 20:43 UTC (permalink / raw)
To: Roger Hand <RHand@kailea.com>; +Cc: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; R, Rajesh (STSD) <rajesh.r2@hp.com>; pgsql-hackers@postgresql.org; pgsql-performance
On Sat, Oct 01, 2005 at 12:51:08PM -0700, Roger Hand wrote:
> > -----Original Message-----
> > From: pgsql-performance-owner@postgresql.org
> > [mailto:pgsql-performance-owner@postgresql.org]On Behalf Of Jim C. Nasby
> > Sent: Friday, September 30, 2005 4:49 PM
> > Subject: Re: [PERFORM] [HACKERS] Query in SQL statement
>
> > I suggest ditching the CamelCase and going with underline_seperators.
> > I'd also not use the bareword id, instead using bad_user_id. And I'd
> > name the table bad_user. But that's just me. :)
>
> I converted a db from MS SQL, where tables and fields were CamelCase, and
> just lowercased the ddl to create the tables.
>
> So table and fields names were all created in lowercase, but I didn't have to change
> any of the application code: the SELECT statements worked fine with mixed case.
>
> -- sample DDL
> CREATE TABLE testtable
> (
> fieldone int4
> )
> insert into TestTable (fieldone) values (11);
That will usually work (see Tom's reply), but fieldone is a heck of a
lot harder to read than field_one. But like I said, this is the coding
conventions I've found work well; YMMV.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [PERFORM] Query in SQL statement
@ 2005-10-05 09:03 R, Rajesh (STSD) <rajesh.r2@hp.com>
2005-10-05 09:09 ` Re: Query in SQL statement Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 10+ messages in thread
From: R, Rajesh (STSD) @ 2005-10-05 09:03 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-hackers@postgresql.org; pgsql-performance
Thanks.
I've already understood that
I need to post it in another list.
Sorry for wasting your precious time.
--
Rajesh R
-----Original Message-----
From: Richard Huxton [mailto:dev@archonet.com]
Sent: Wednesday, October 05, 2005 2:24 PM
To: R, Rajesh (STSD)
Cc: pgsql-hackers@postgresql.org; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Query in SQL statement
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Query in SQL statement
2005-10-05 09:03 Re: [PERFORM] Query in SQL statement R, Rajesh (STSD) <rajesh.r2@hp.com>
@ 2005-10-05 09:09 ` Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 10+ messages in thread
From: Richard Huxton @ 2005-10-05 09:09 UTC (permalink / raw)
To: R, Rajesh (STSD) <rajesh.r2@hp.com>; +Cc: pgsql-performance
R, Rajesh (STSD) wrote:
> Thanks.
> I've already understood that
> I need to post it in another list.
>
> Sorry for wasting your precious time.
No time wasted. It was a perfectly reasonable question, just to the
wrong lists.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2005-10-05 09:09 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-09-29 13:05 Query in SQL statement R, Rajesh (STSD) <rajesh.r2@hp.com>
2005-09-29 13:28 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2005-09-30 23:48 ` Jim C. Nasby <jnasby@pervasive.com>
2005-10-05 08:54 ` Richard Huxton <dev@archonet.com>
2005-09-29 13:30 Re: Query in SQL statement Obe, Regina DND\MIS <robe.dnd@cityofboston.gov>
2005-10-01 19:51 Re: [HACKERS] Query in SQL statement Roger Hand <RHand@kailea.com>
2005-10-01 21:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-10-04 20:43 ` Jim C. Nasby <jnasby@pervasive.com>
2005-10-05 09:03 Re: [PERFORM] Query in SQL statement R, Rajesh (STSD) <rajesh.r2@hp.com>
2005-10-05 09:09 ` Richard Huxton <dev@archonet.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox