Re: [SQL] List table with same column name
On Thu, 23 Oct 2003, Peter Childs wrote: > > > On Thu, 23 Oct 2003 [EMAIL PROTECTED] wrote: > > > On Thu, 23 Oct 2003, Peter Childs wrote: > > > > > > > > > > > On Thu, 23 Oct 2003 [EMAIL PROTECTED] wrote: > > > > > > > On Thu, 23 Oct 2003, Abdul Wahab Dahalan wrote: > > > > > > > > > Hi! > > > > > How do I list all the tables in the database which has a same column name?. > > > > > > > > SELECT t1.relname,a1.attname,t2.relname from pg_class t1,pg_attribute > > > > a1,pg_class t2,pg_attribute a2 where a1.attrelid=t1.oid and t1.relkind='r' > > > > and a1.attnum>0 and a2.attrelid=t2.oid and t2.relkind='r' and a2.attnum>0 > > > > and t1.relname > > > > > That will not work. > > > > > > SELECT t1.relname,a1.attname,t2.relname from pg_class t1,pg_attribute > > > a1,pg_class t2,pg_attribute a2 where a1.attrelid=t1.oid and > > > t1.relkind='r'and a1.attnum>0 and a2.attrelid=t2.oid and t2.relkind='r' > > > and a2.attnum>0 and t1.relname > > a1.attisdropped=false and a2.attisdropped=false and t1.relname != > > > t2.relname; > > > > > > Why? > > > > > > Well two bugs. > > > 1> Dropped Columns needed for 7.3. > > > 2> Do you really need to know that column a in table 1 also appears in > > > table 1? > > > > Have you ever thought that x > Yes but when I tested it due to pure intrest to told me that > column a in table 1 also appeared in table 1. Most strange. By adding x!= you probably influenced the ordering of the rows, which gave you the impression of "solving" the "bug", while actually the "bug" in the first place, was an effect of a too large xterm that gives the illusion of a left item to be the same as the right item of a adjacent line. Now seriously, if the meaning of x > Peter Childs > > > > > > > > > Peter Childs > > > > > > > > > > > > > > > > > Thanks > > > > > > > > > > > > > > > ---(end of broadcast)--- > > > > > TIP 4: Don't 'kill -9' the postmaster > > > > > > > > > > > > > -- > > > > -Achilleus > > > > > > > > > > > > ---(end of broadcast)--- > > > > TIP 5: Have you checked our extensive FAQ? > > > > > > > >http://www.postgresql.org/docs/faqs/FAQ.html > > > > > > > > > > > -- > > -Achilleus > > > > > > ---(end of broadcast)--- > TIP 3: 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 > -- -Achilleus ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] List table with same column name
On Thu, 23 Oct 2003 [EMAIL PROTECTED] wrote: > On Thu, 23 Oct 2003, Peter Childs wrote: > > > > > > > On Thu, 23 Oct 2003 [EMAIL PROTECTED] wrote: > > > > > On Thu, 23 Oct 2003, Abdul Wahab Dahalan wrote: > > > > > > > Hi! > > > > How do I list all the tables in the database which has a same column name?. > > > > > > SELECT t1.relname,a1.attname,t2.relname from pg_class t1,pg_attribute > > > a1,pg_class t2,pg_attribute a2 where a1.attrelid=t1.oid and t1.relkind='r' > > > and a1.attnum>0 and a2.attrelid=t2.oid and t2.relkind='r' and a2.attnum>0 > > > and t1.relname > > > That will not work. > > > > SELECT t1.relname,a1.attname,t2.relname from pg_class t1,pg_attribute > > a1,pg_class t2,pg_attribute a2 where a1.attrelid=t1.oid and > > t1.relkind='r'and a1.attnum>0 and a2.attrelid=t2.oid and t2.relkind='r' > > and a2.attnum>0 and t1.relname > a1.attisdropped=false and a2.attisdropped=false and t1.relname != > > t2.relname; > > > > Why? > > > > Well two bugs. > > 1> Dropped Columns needed for 7.3. > > 2> Do you really need to know that column a in table 1 also appears in > > table 1? > > Have you ever thought that x > > > > Peter Childs > > > > > > > > > > > > > Thanks > > > > > > > > > > > > ---(end of broadcast)--- > > > > TIP 4: Don't 'kill -9' the postmaster > > > > > > > > > > -- > > > -Achilleus > > > > > > > > > ---(end of broadcast)--- > > > TIP 5: Have you checked our extensive FAQ? > > > > > >http://www.postgresql.org/docs/faqs/FAQ.html > > > > > > > -- > -Achilleus > > ---(end of broadcast)--- TIP 3: 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: [SQL] List table with same column name
On Thu, 23 Oct 2003, Peter Childs wrote: > > > On Thu, 23 Oct 2003 [EMAIL PROTECTED] wrote: > > > On Thu, 23 Oct 2003, Abdul Wahab Dahalan wrote: > > > > > Hi! > > > How do I list all the tables in the database which has a same column name?. > > > > SELECT t1.relname,a1.attname,t2.relname from pg_class t1,pg_attribute > > a1,pg_class t2,pg_attribute a2 where a1.attrelid=t1.oid and t1.relkind='r' > > and a1.attnum>0 and a2.attrelid=t2.oid and t2.relkind='r' and a2.attnum>0 > > and t1.relname > That will not work. > > SELECT t1.relname,a1.attname,t2.relname from pg_class t1,pg_attribute > a1,pg_class t2,pg_attribute a2 where a1.attrelid=t1.oid and > t1.relkind='r'and a1.attnum>0 and a2.attrelid=t2.oid and t2.relkind='r' > and a2.attnum>0 and t1.relname a1.attisdropped=false and a2.attisdropped=false and t1.relname != > t2.relname; > > Why? > > Well two bugs. > 1> Dropped Columns needed for 7.3. > 2> Do you really need to know that column a in table 1 also appears in > table 1? Have you ever thought that x > Peter Childs > > > > > > > > > Thanks > > > > > > > > > ---(end of broadcast)--- > > > TIP 4: Don't 'kill -9' the postmaster > > > > > > > -- > > -Achilleus > > > > > > ---(end of broadcast)--- > > TIP 5: Have you checked our extensive FAQ? > > > >http://www.postgresql.org/docs/faqs/FAQ.html > > > -- -Achilleus ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] List table with same column name
On Thu, 23 Oct 2003 [EMAIL PROTECTED] wrote: > On Thu, 23 Oct 2003, Abdul Wahab Dahalan wrote: > > > Hi! > > How do I list all the tables in the database which has a same column name?. > > SELECT t1.relname,a1.attname,t2.relname from pg_class t1,pg_attribute > a1,pg_class t2,pg_attribute a2 where a1.attrelid=t1.oid and t1.relkind='r' > and a1.attnum>0 and a2.attrelid=t2.oid and t2.relkind='r' and a2.attnum>0 > and t1.relname0 and a2.attrelid=t2.oid and t2.relkind='r' and a2.attnum>0 and t1.relname Dropped Columns needed for 7.3. 2> Do you really need to know that column a in table 1 also appears in table 1? Peter Childs > > > > > Thanks > > > > > > ---(end of broadcast)--- > > TIP 4: Don't 'kill -9' the postmaster > > > > -- > -Achilleus > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faqs/FAQ.html > ---(end of broadcast)--- TIP 3: 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: [SQL] List table with same column name
On Thu, 23 Oct 2003, Abdul Wahab Dahalan wrote: > Hi! > How do I list all the tables in the database which has a same column name?. SELECT t1.relname,a1.attname,t2.relname from pg_class t1,pg_attribute a1,pg_class t2,pg_attribute a2 where a1.attrelid=t1.oid and t1.relkind='r' and a1.attnum>0 and a2.attrelid=t2.oid and t2.relkind='r' and a2.attnum>0 and t1.relname > Thanks > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > -- -Achilleus ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] List table with same column name
Hi! How do I list all the tables in the database which has a same column name?. Thanks ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster