GAUSS-00551 -- GAUSS-00560

GAUSS-00551: "could not determine which collation to use for view column '%s'"

SQLSTATE: 42P22

Description: The collation to be used by a view column cannot be determined.

Solution: Use the COLLATE clause to assign a collation to the column.

Example:

1
2
3

CREATE VIEW v1 as select t1.a || t2.a from t1,t2;
ERROR:  could not determine which collation to use for view column "?column?"
HINT:  Use the COLLATE clause to set the collation explicitly.

GAUSS-00552: "view must have at least one column"

SQLSTATE: 42P16

Description: The number of columns defining a view is smaller than 1.

Solution: Ensure that the number of columns defining a view is greater than or equal to 1.

GAUSS-00553: "'%s' is not a view"

SQLSTATE: 42809

Description: The object is not a view.

Solution: Ensure the object is a view.

GAUSS-00554: "cannot drop columns from view"

When using CREATE OR REPLACE VIEW for an existing view, the new definition must preserve the original view's column names, sequence, and data types, or it will trigger an error.

Solution: Columns in a defined view cannot be deleted. The new view definition must return the same columns as the original view. That is, you cannot reduce the columns of a view by running CREATE OR REPLACE VIEW.

For example, when the myview view is created based on table tbl1 and tbl2, attempting to reduce the view's columns with CREATE OR REPLACE VIEW will result in an error.

CREATE TABLE tbl1(a int, b text);
CREATE TABLE tbl2(a int, b text);

CREATE VIEW myview as select * from tbl1;
CREATE VIEW

CREATE OR REPLACE VIEW myview as select a from tbl1;
ERROR:  cannot drop columns from view

In this case, you can delete the original view and create a new view.

DROP VIEW myview;
DROP VIEW

CREATE OR REPLACE VIEW myview as select a from tbl1;
CREATE VIEW

GAUSS-00555: "cannot change name of view column '%s' to '%s'"

SQLSTATE: 42P16

Description: When using CREATE OR REPLACE VIEW for an existing view, the new definition must preserve the original view's column names, sequence, and data types, or it will trigger an error.

Solution: After a view is defined, the column names in it cannot be modified.

CREATE OR REPLACE VIEW myview as select b,a from tbl1;
ERROR:  cannot change name of view column "a" to "b"

In this case, you can delete the original view and create a new view.

DROP VIEW myview;
DROP VIEW

CREATE OR REPLACE VIEW myview as select b,a from tbl1;
CREATE VIEW

GAUSS-00556: "cannot change data type of view column '%s' from %s to %s"

SQLSTATE: 42P16

Description: If CREATE OR REPLACE VIEW is used to create a view, but a view with the same name already exists, the new view definition must return the same columns (with the same column name sequence and data type) as the original view (additional columns can be added), otherwise, an error is reported.

Solution: Ensure that the new view and the original view have the same column data type.

For example, the following error is reported when a column type is changed using CREATE OR REPLACE VIEW:

CREATE OR REPLACE VIEW view1 as select * from tbl2;
ERROR:  cannot change data type of view column "b" from integer to text

You can run the CREATE OR REPLACE VIEW command to add a column.

CREATE OR REPLACE VIEW myview as select tbl1.*,tbl2.b as c from tbl1 ,tbl2 where tbl1.a = tbl2.a;
CREATE VIEW

GAUSS-00557: "unexpected parse analysis result"

SQLSTATE: XX000

Description: Internal system error.

Solution: Contact technical support.

GAUSS-00558: "views must not contain SELECT INTO"

SQLSTATE: 0A000

Description: Statements used to define a view contain the SELECT INTO statement.

Solution: Ensure that a single SELECT statement is executed to query the results.

GAUSS-00559: "views must not contain data-modifying statements in WITH"

SQLSTATE: 0A000

Description: Statements used to define a view contain the WITH clause.

Solution: Ensure that a single SELECT statement is executed to query the results.

GAUSS-00560: "CREATE VIEW specifies more column names than columns"

SQLSTATE: 42601

Description: The number of columns defining a view is greater than that of columns in the returned results.

Solution: Ensure that the number of columns defining a view is not greater than that of columns in the returned results.