Обсуждение: FW: tablename.columnname support
-----Original Message----- From: Vijayendra M Agrawal [mailto:vijayendra.agrawal@wipro.com] Sent: Sunday, September 17, 2000 11:24 AM To: The Hermit Hacker Subject: RE: [ADMIN] tablename.columnname support Dear Hermit, I didn't ask the question just for the sake of asking... The design in my module is such that for any requests from the client side, I have to do some DB operation. The operation could be either SELECT, UPDATE, INSERT or DELETE in the database depending on the request id and the data in the database. So, I am having a meta-data table in the database, which will tell me what kind of operation to do on a particular request id, what are the tables involved, what are the columns involved, what are the keys for these tables, and what are the relations(join conditions) between the tables involved. So, here in column names, I am storing columns in the tablename.columnname format, becuase columns can be from many tables. Here comes the problem specified in my query earliar... If you have an idea, ORACLE supports tablename.columnname format in all INSERT, SELECT, UPDATE and DELETE queries. Anyway, thanks for your reply... Thanks and regards, Vijay -----Original Message----- From: The Hermit Hacker [mailto:scrappy@hub.org] Sent: Saturday, September 16, 2000 8:50 PM To: vijayendra mohan agrawal Cc: PostGre SQL Mailing List Subject: Re: [ADMIN] tablename.columnname support On Sat, 16 Sep 2000, vijayendra mohan agrawal wrote: > Hi all, > > say, we have a table created by : > create table a ( a char(11), b char(22) ); > > For adding data into table 'a', ORACLE supports column name format as > tablename.columnname as follows : > insert into a ( a.a, a.b ) values ( 'xyz', 'abc') ; > > But, PostGreSQL doesn't support... It gives parse error as follows : > ERROR: parser: parse error at or near "." > > Can this feature be added in PostGreSQL ??? stupid question, but why would you use that format instead of doing: INSERT INTO a ( a, b ) VALUES ( 'xyz', 'abc' ); Why the tablename. in front?
vijayendra mohan agrawal wrote: > The design in my module is such that for any requests from the client side, > I have to do some DB operation. The operation could be either SELECT, > UPDATE, INSERT or DELETE in the database depending on the request id and the > data in the database. So, I am having a meta-data table in the database, > which will tell me what kind of operation to do on a particular request id, > what are the tables involved, what are the columns involved, what are the > keys for these tables, and what are the relations(join conditions) between > the tables involved. So, here in column names, I am storing columns in the > tablename.columnname format, becuase columns can be from many tables. I see your point. But couldn't you just strip out the dbname prefix when talking to PostgreSQL? Best Regards, Haroldo. -- ----------------------+------------------------ Haroldo Stenger | hstenger@ieee.org Montevideo, Uruguay. | hstenger@adinet.com.uy ----------------------+------------------------ Visit UYLUG Web Site: http://www.linux.org.uy -----------------------------------------------
Hie , I've just install PostgreSQL 7.0.2 on i686-pc-linux-gnu (Pentium 233 with 64 Mo RAM) But i've also PostgreSQL 6.5.2 " " (P 450 with 256 Mo RAM) I've dump one of my big database (500000 rows) on the new one . I've made some test and one of my querie take 7 sec on the old server 1 sec on the new The EXPLAIN seem to be different . Is it a improvement or a mistake ? With this good result , Postgresql 7.0 equals Mysql ! I'll be very happy , if you confirm that PostgreSQL have such improve the speed ... This is the request : SELECT DISTINCT n.username from newlog2000 n , realms r where ( (n.username like 'loic@%' ) and (r.host = 'toto.fr' ) and ( substr(n.username,strpos(n.username,'@')+1) = r.domain ) ) ; NOTICE: QUERY PLAN: (on the new server PostgreSQL 7.0 200 Mhz 64 Mo RAM ) Unique (cost=253.85..254.07 rows=9 width=24) -> Sort (cost=253.85..253.85 rows=91 width=24) -> Nested Loop (cost=0.00..250.90 rows=91 width=24) -> Seq Scan on realms r (cost=0.00..22.50 rows=10 width=12) -> Index Scan using newuser2000 on newlog2000 n (cost=0.00..4.74 rows=905 width=12) NOTICE: QUERY PLAN: (on the old server PostgreSQL 6.5 450 Mhz 256 Mo RAM) Unique (cost=14107.55 rows=7897 width=24) -> Sort (cost=14107.55 rows=7897 width=24) -> Nested Loop (cost=14107.55 rows=7897 width=24) -> Index Scan using newuser2000 on newlog2000 n (cost=9773.30 rows=658 width=12) -> Seq Scan on realms r (cost=6.59 rows=120 width=12) Thanks for all , and sorry for my english .... loic