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:
Is it possible to change the natural order of columns in Postgres?
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 facebook_id
and 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).