[PostgreSQL][8.0.4] Sequence Function Arguments are text Type, not regclass Type (ERROR: cannot cast type text to regclass)

Tadashi Shigeoka ·  Fri, March 2, 2012

In PostgreSQL 8.0.4, I passed a regclass type instead of text type as an argument to the sequence function like nextval((‘user_id_seq’::text)::regclass), which caused an error.

■ Error Content

ERROR:  cannot cast type text to regclass

■ Reference Information

注意: PostgreSQL 8.1以前においては、シーケンス関数の引数はregclass型ではなく、text型で、そして上記のテキスト文字列からOID値への変換はそれぞれの呼び出し実行時に起こりました。後方互換性のため、この仕組みはまだ存在しますが、内部的には関数が実行される前にtextからregclassへの暗黙的強制型変換として現在処理されています。

Note: In PostgreSQL 8.1 and earlier, sequence function arguments were text type, not regclass type, and the conversion from text strings to OID values occurred at each function call execution. For backward compatibility, this mechanism still exists, but internally it’s now processed as an implicit coercion from text to regclass before the function is executed.

・Reference: シーケンス操作関数

■ SQL Statement (Before Fix)

CREATE SEQUENCE user_id_seq INCREMENT 1 START 1000;

CREATE TABLE user ( 
  id integer DEFAULT nextval(('user_id_seq'::text)::regclass) NOT NULL,
  name varchar(50) NOT NULL,
  age integer NOT NULL
);

↓(Modified)

■ SQL Statement (After Fix)

CREATE SEQUENCE user_id_seq INCREMENT 1 START 1000;

CREATE TABLE user ( 
  id integer DEFAULT nextval('user_id_seq') NOT NULL,
  name varchar(50) NOT NULL,
  age integer NOT NULL
);

That’s all from the Gemba regarding PostgreSQL sequence function type compatibility.