If I had a table with 3 columns - say A, B and D - and I had to introduce a new one - say C to replace the current position of D. I would use the following method:
- Introduce 2 new columns as C and D2.
- Copy the contents of D to D2.
- Delete D.
- Rename D2 to D.
The new order would be A, B, C and D.
I thought this was a legitimate practice as (so far) it produced no issues.
However, today I came across a problem when a function carrying out a statement on the same table returned the following error:
table row type and query-specified row type do not match
And the following detail:
Query provides a value for a dropped column at ordinal position 13
I tried restarting PostgreSQL, doing a
VACUUM FULL and finally deleting and re-creating the function as suggested here and here but these solutions did not work (aside from the fact that they try tackling a situation where a system table has been altered).
Having the luxury of working with a very small database I exported it, deleted it and then re-imported it and that fixed the issue with my function.
I was aware of the fact that one should not mess around with the natural order of columns by modifying system tables (getting hands dirty with
pg_attribute, etc.) as seen here:
Judging by the error thrown by my function I now realize that shifting the order of columns with my method is also a no-no. Can anyone shine some light as to why what I am doing is also wrong?
Postgres version is 9.6.0.
Here is the function:
CREATE OR REPLACE FUNCTION "public"."__post_users" ("facebookid" text, "useremail" text, "username" text) RETURNS TABLE (authentication_code text, id integer, key text, stripe_id text) AS ' -- First, select the user: WITH select_user AS (SELECT users.id FROM users WHERE useremail = users.email), -- Second, update the user (if user exists): update_user AS (UPDATE users SET authentication_code = GEN_RANDOM_UUID(), authentication_date = current_timestamp, facebook_id = facebookid WHERE EXISTS (SELECT * FROM select_user) AND useremail = users.email RETURNING users.authentication_code, users.id, users.key, users.stripe_id), -- Third, insert the user (if user does not exist): insert_user AS (INSERT INTO users (authentication_code, authentication_date, email, key, name, facebook_id) SELECT GEN_RANDOM_UUID(), current_timestamp, useremail, GEN_RANDOM_UUID(), COALESCE(username, SUBSTRING(useremail FROM ''([^@]+)'')), facebookid WHERE NOT EXISTS (SELECT * FROM select_user) RETURNING users.authentication_code, users.id, users.key, users.stripe_id) -- Finally, select the authentication code, ID, key and Stripe ID: SELECT * FROM update_user UNION ALL SELECT * FROM insert_user' LANGUAGE "sql" COST 100 ROWS 1 VOLATILE CALLED ON NULL INPUT SECURITY INVOKER
I performed the renaming/reordering on both column
stripe_id (a new column was added before these, which is the reason for the renaming, but is not touched by this query).
Having the columns in a certain order is purely out of interest for order. However, the reason for asking this question is out of concern that a simple renaming and deleting of a column may trigger real issues for somebody using functions in production mode (as happened to myself).