Cyclopedia Square

Summary: ads are gone, send me bitcoin to say thanks instead. I have always kept notes for myself to remind myself how to do various things. I have also kept a journal since a very young age where I often include opinions and thoughts on various matters. At some point, years ago, I got the idea that I should post some of the notes and thoughts in case they would help someone else. That requires a lot more work than just typing stuff quickly into a text file, though, so I needed a little more motivation. I mean, sharing and helping others is great motivation, but when I read about Adsense from Google, and how Google could show people links to products and services related to my posts, and I could make a few bucks if people thought those links were useful and clicked on them, and how it could help out the people providing those products and services as well, it felt like this great intertwined synergy of helping people out and possibly even gaining some monetary reward as well. An...

I wanted to know which tables referenced a certain other table in my database, but being a very occasional user of SQL, I didn't know where to begin to find that. Fortunately, the internet had the answer , from Tom Lane himself actually. Just run this query on your Postgresql database: select confrelid::regclass, af.attname as fcol, conrelid::regclass, a.attname as col from pg_attribute af, pg_attribute a, (select conrelid,confrelid,conkey[i] as conkey, confkey[i] as confkey from (select conrelid,confrelid,conkey,confkey, generate_series(1,array_upper(conkey,1)) as i from pg_constraint where contype = 'f') ss) ss2 where af.attnum = confkey and af.attrelid = confrelid and a.attnum = conkey and a.attrelid = conrelid; That shows all foreign key relationships in your database. If you just want to see which tables reference a particular table, do this (replace my_table and my_referenced_column with the table column you want to see t...