Re: [PERFORM] [HACKERS] Query in SQL statement
On Sat, Oct 01, 2005 at 12:51:08PM -0700, Roger Hand wrote: -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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 [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] [HACKERS] Query in SQL statement
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] [HACKERS] Query in SQL statement
Roger Hand [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Query in SQL statement
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 '-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 [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[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 '-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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Query in SQL statement
I think this question may be more appropriate for [EMAIL PROTECTED] 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:[EMAIL PROTECTED] 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 '-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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Query in SQL statement
CREATE SEQUENCE ai_id; CREATE TABLE badusers ( id int DEFAULT nextval('ai_id') NOT NULL, UserName varchar(30), Date datetime DEFAULT '-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); ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly