Apparently postgresql doesn't actually remove 'dropped' columns, instead it renames them something like ........pg.dropped.23........, fills them with NULL and marks them as unused using the attisdroppped column of the pg_attribute table.
For example:
DROP TABLE IF EXISTS test_persons;
CREATE TABLE test_persons (
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255),
State varchar(2),
Dummy varchar(255)
);
INSERT INTO test_persons
(PersonID, LastName, FirstName, Address, City, State, Dummy)
VALUES
(1, 'Sawyer', 'Tom', 'Apple Street', 'Riverside', 'MO', 'Dummy'),
(2, 'Finn', 'Huck', 'Back Street', 'Riverside', 'MO', 'Dummy')
;
ALTER TABLE test_persons
DROP COLUMN Dummy;
SELECT
a.attname,
a.attisdropped
FROM
pg_attribute a,
pg_class c,
pg_tables t,
pg_namespace nsp
WHERE
a.attrelid = c.oid
AND c.relname = tablename
AND c.relnamespace = nsp.oid
AND a.attnum > 0
AND nspname = CURRENT_SCHEMA()
AND schemaname = nspname
AND tablename = 'test_persons'
;
Apparently postgresql doesn't actually remove 'dropped' columns, instead it renames them something like
........pg.dropped.23........
, fills them with NULL and marks them as unused using theattisdroppped
column of thepg_attribute
table.For example:
yeilds:
and consequently
yields
Suggestion: Add an additional
AND NOT a.attisdropped
clause to theWHERE
.