Get table and column information in postgres

Use the information_schema catalog - http://www.postgresql.org/docs/9.1/static/information-schema.html

Get table and columns from the public schema

	select
	tables.table_name,
	columns.column_name
	from information_schema.tables
	inner join information_schema.columns
	on information_schema.columns.table_name = information_schema.tables.table_name
	where tables.table_schema = 'public'
	and tables.table_type = 'BASE TABLE'
	order by tables.table_name, columns.column_name;

view raw select_table_and_columns_from_postgres.sql hosted with ❤ by GitHub

This can be useful, maybe you want to auto generate some text that creates foreign keys based on a naming convention - for instance let's say we have a ruby/rails method called make_fk_unless_exists

	select
	tables.table_name,
	columns.column_name,
	('make_fk_unless_exists :' || tables.table_name || ', :' || columns.column_name || ', :' || REPLACE(columns.column_name, '_id', 's')) as code
	from information_schema.tables
	inner join information_schema.columns
	on information_schema.columns.table_name = information_schema.tables.table_name
	where tables.table_schema = 'public'
	and columns.column_name LIKE '%id'
	and columns.column_name <> 'id'
	and tables.table_type = 'BASE TABLE'
	order by tables.table_name, columns.column_name;

view raw create_fk_script_postgres.sql hosted with ❤ by GitHub

The 'code' column would render something like

make_fk_unless_exists :projects, :milestone_id, :milestones
make_fk_unless_exists :tasks, :project_id, :projects
...