Has anyone seen this error before?
SQL Error [42703]: ERROR: column “line_item_static_type” does not exist
Where: SQL statement “CREATE INDEX line_item_static_type_idx ON public.line_item_static USING btree (line_item_static_type, line_item_static_type_related_id)”
PL/pgSQL function recreate_line_item_static() line 21 at EXECUTE
I’m getting it when calling this function:
–Create Function to Create New Table
CREATE OR REPLACE FUNCTION recreate_line_item_static() RETURNS VOID AS $$
DECLARE
idx RECORD;
index_name TEXT;
BEGIN
— Drop the table if it exists
IF EXISTS (SELECT FROM pg_tables WHERE tablename = ‘line_item_static’) THEN
EXECUTE ‘DROP TABLE line_item_static’;
END IF;
-- Create a new table with the same structure as line_item
EXECUTE 'CREATE TABLE line_item_static AS SELECT * FROM line_item WHERE 1=0';
-- Write data from line_item into line_item_static
EXECUTE 'INSERT INTO line_item_static SELECT * FROM line_item';
--Copy indexes from line_item to line_item_static
FOR idx IN (SELECT indexdef, indexname FROM pg_indexes WHERE tablename = 'line_item') LOOP
index_name := 'line_item_static_' || idx.indexname;
IF NOT EXISTS (SELECT 1 FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE c.relname = index_name AND n.nspname = 'public') THEN
EXECUTE REPLACE(REPLACE(idx.indexdef, 'line_item', 'line_item_static'), idx.indexname, index_name);
END IF;
END LOOP;
END;
$$ LANGUAGE plpgsql;
These two columns don’t exist in the line_item table (line_item_static_type, line_item_static_type_related_id) – they should be line_item_type and line_item_type related_id.
This is what is coming back in the pg_indexes table for the original index – CREATE INDEX line_item_type_idx ON public.line_item USING btree (line_item_type, line_item_type_related_id).
The index was originally named line_item_line_item_type_idx which I thought may be the cause, but renaming it didn’t fix the issue. I also tried recreating the indexes at the end of the query, however I want to be able to copy in existing indexes each time the function is run, so any new indexes won’t get pulled in if the indexes are created instead of copied.
Christian Delroy is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.