DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

Snippets has posted 5883 posts at DZone. View Full User Profile

Get Tables Without A Primary Key (PostgreSQL)

  • submit to reddit
        Gets all tables without a primary key

SELECT t.nspname, t.relname
	SELECT c.oid, c.relname, n.nspname
	FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
	WHERE c.relkind = 'r' AND n.nspname IN('public') -- any other schemas in here
) AS t LEFT OUTER JOIN pg_constraint c ON c.contype = 'p' AND c.conrelid = t.oid
WHERE c.conname IS NULL