Goal Find columns of tables (base tables, views, materialized views, foreign tables) that have the name first_name or last_name. Such column names do not take into account that different cultures use different personal name components and the number of possible components is more than two. If in a culture, the surname is presented before the given name, then the column names causes confusion.
Type Problem detection Each row in the result could represent a flaw in the design
Reliability High Few or no false-positive results
License MIT (opens in new tab)
Fixing Suggestion Instead of column name first_name use given_name. Instead of column name last_name use surname. Consider replacing the two columns with the single column person_name. In case of base tables and foreign tables it is possible to rename the columns. In case of derived tables one has to drop and recreate the tables.
Data Source system catalog only
SQL Query 
SELECT n.nspname AS table_schema, c.relname AS table_name, 
CASE WHEN c.relkind='r' THEN 'BASE TABLE'
WHEN c.relkind='v' THEN 'VIEW'
WHEN c.relkind='m' THEN 'MATERIALIZED VIEW'
WHEN c.relkind='f' THEN 'FOREIGN TABLE' END AS table_type,
a.attname AS column_name 
FROM pg_class AS c INNER JOIN pg_namespace AS n ON c.relnamespace=n.oid
INNER JOIN pg_attribute AS a ON c.oid=a.attrelid
WHERE c.relkind  IN ('r','v', 'm','f') AND a.attnum>=1 AND a.attisdropped='f'
AND (a.attname~*'^first[_]*name$' OR a.attname~*'^last[_]*name$')
ORDER BY n.nspname, c.relname, a.attname;

SQL statements that help generate fixes for the identified problem.

Fix Action #1
SELECT format('ALTER TABLE %1$I.%2$I RENAME COLUMN %3$I TO given_name;', n.nspname, c.relname, a.attname) AS statements
FROM pg_class AS c INNER JOIN pg_namespace AS n ON c.relnamespace=n.oid
INNER JOIN pg_attribute AS a ON c.oid=a.attrelid
WHERE c.relkind  IN ('r','f') AND a.attnum>=1 AND a.attisdropped='f'
AND a.attname~*'^first[_]*name$'
ORDER BY n.nspname, c.relname, a.attname;

Rename the base table or foreign table column first_name.

Fix Action #2
SELECT format('ALTER TABLE %1$I.%2$I RENAME COLUMN %3$I TO surname;', n.nspname, c.relname, a.attname) AS statements
FROM pg_class AS c INNER JOIN pg_namespace AS n ON c.relnamespace=n.oid
INNER JOIN pg_attribute AS a ON c.oid=a.attrelid
WHERE c.relkind  IN ('r','f') AND a.attnum>=1 AND a.attisdropped='f'
AND a.attname~*'^last[_]*name$'
ORDER BY n.nspname, c.relname, a.attname;

Rename the base table or foreign table column last_name.

SQL Query to Generate FixDescription
SELECT format('ALTER TABLE %1$I.%2$I RENAME COLUMN %3$I TO given_name;', n.nspname, c.relname, a.attname) AS statements
FROM pg_class AS c INNER JOIN pg_namespace AS n ON c.relnamespace=n.oid
INNER JOIN pg_attribute AS a ON c.oid=a.attrelid
WHERE c.relkind  IN ('r','f') AND a.attnum>=1 AND a.attisdropped='f'
AND a.attname~*'^first[_]*name$'
ORDER BY n.nspname, c.relname, a.attname;
Rename the base table or foreign table column first_name.
SELECT format('ALTER TABLE %1$I.%2$I RENAME COLUMN %3$I TO surname;', n.nspname, c.relname, a.attname) AS statements
FROM pg_class AS c INNER JOIN pg_namespace AS n ON c.relnamespace=n.oid
INNER JOIN pg_attribute AS a ON c.oid=a.attrelid
WHERE c.relkind  IN ('r','f') AND a.attnum>=1 AND a.attisdropped='f'
AND a.attname~*'^last[_]*name$'
ORDER BY n.nspname, c.relname, a.attname;
Rename the base table or foreign table column last_name.

Collections

This query belongs to the following collections:

Find problems about names

A selection of queries that return information about the names of database objects. Contains all the types of queries - problem detection, software measure, and general overview.

Find problems automatically

Queries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not .

NameDescription
Find problems about namesA selection of queries that return information about the names of database objects. Contains all the types of queries - problem detection, software measure, and general overview.
Find problems automaticallyQueries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not .

Categories

This query is classified under the following categories:

Naming

Queries of this category provide information about the style of naming.

NameDescription
NamingQueries of this category provide information about the style of naming.