CODEWITHSUNDEEP
postgresqlenums
tuope
tuope

Reputation: 77

PostgreSQL: changing a column of enum type

This is the definition of the enumerated type: 

CREATE TYPE khstate AS ENUM ('none', 'form', 'test', 'finished');

Now, in a database table I have a row with columns 'id' (type SERIAL) and 'state' (type khstate). The value of the 'id' column is 100004, and the value of the 'state' column is 'none'. How do I change it to, say, 'form'? The table definition is as follows.

CREATE TABLE IF NOT EXISTS khs (  
    id SERIAL UNIQUE,  
    state khstate DEFAULT 'none',  
    PRIMARY KEY (id)  
);

I have tried queries such as

UPDATE tablename SET state = 'form' WHERE id = 100004;

ERROR: invalid input value for enum khstate: "form" 

UPDATE tablename SET state = 'form'::khstate WHERE id = 100004;

ERROR: invalid input value for enum khstate: "form"

but without success. I am using the latest BitNami LAPPstack, so the version of PostgreSQL is 9.1.1.

I havent added any RULE or TRIGGER constraints, nor have I used transactions explicitly.

I tried to google for a solution, but all I could find were questions how to change the enum type itself, rather than the value of a column.

Upvotes: 2

Views: 6997

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656716

The error message indicates that 'form' is not a registered value for your enum type khstate.

I also notice that in your question you create a table named khs, but the UPDATE example uses tablename instead.

There may be a mixup of tables, schemas or databases. Diagnose your problem by running this query in the same session that gives you the error message:

SELECT enum_range(NULL::khstate)

Do you see form in the array of values? I expect, you don't.

More about enum support functions in the manual.

Upvotes: 3

Related Questions