Thread: List table with same column name
Hi! How do I list all the tables in the database which has a same column name?. Thanks
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<t2.relname and a1.attname = a2.attname; > > Thanks > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > -- -Achilleus
On Thu, 23 Oct 2003 achill@matrix.gatewaynet.com 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<t2.relname and a1.attname = a2.attname; 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<t2.relname and a1.attname = a2.attname and 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? 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 >
On Thu, 23 Oct 2003, Peter Childs wrote: > > > On Thu, 23 Oct 2003 achill@matrix.gatewaynet.com 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<t2.relname and a1.attname = a2.attname; > > 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<t2.relname and a1.attname = a2.attname and > 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<y implies x!=y ? > > 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
On Thu, 23 Oct 2003 achill@matrix.gatewaynet.com wrote: > On Thu, 23 Oct 2003, Peter Childs wrote: > > > > > > > On Thu, 23 Oct 2003 achill@matrix.gatewaynet.com 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<t2.relname and a1.attname = a2.attname; > > > > 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<t2.relname and a1.attname = a2.attname and > > 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<y implies x!=y ? 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. 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 > >
On Thu, 23 Oct 2003, Peter Childs wrote: > > > On Thu, 23 Oct 2003 achill@matrix.gatewaynet.com wrote: > > > On Thu, 23 Oct 2003, Peter Childs wrote: > > > > > > > > > > > On Thu, 23 Oct 2003 achill@matrix.gatewaynet.com 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<t2.relname and a1.attname = a2.attname; > > > > > > 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<t2.relname and a1.attname = a2.attname and > > > 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<y implies x!=y ? > > 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<y was not actually x<y for postgresql, that would mean a general alarm, heads up, or whatever else we might call it! > > 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 majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > -- -Achilleus