This document is shared with customised GaussDB(DWS) Data Types (Compound Types) in huawei cloud community.

The CREATE TYPE syntax defines a new data TYPE in the database.

  • Compound type — essentially the same as a table, but does not create an actual table.
  • Basic type – New basic type that needs to specify corresponding external input and output functions.
  • Shell type — placeholders
  • Enumeration type – a list of labels that are not empty strings

The compound type

grammar
CREATE TYPE name AS
    ( [ attribute_name data_type [ COLLATE collation ] [, ... ] ] )
Copy the code
Break down

1. First create a new compound type containing two INT4 data types.

postgres=# CREATE TYPE point_comlex AS (x INT, y INT);
CREATE TYPE
Copy the code

Create a table based on creating a new data type.

postgres=# CREATE TABLE position(no INT4, coordinate point_comlex)DISTRIBUTE BY ROUNDROBIN;
CREATE TABLE
Copy the code

3. Query data.

postgres=# SELECT * FROM position; No | coordinate - + -- -- -- -- -- -- -- -- -- -- -- -- 1 | (row 1) - (1, 1) note: Postgres =# SELECT coordinate. X FROM position; ERROR: missing FROM-clause entry for table "coordinate" LINE 1: SELECT coordinate.x FROM position; ^ CONTEXT: referenced column: xCopy the code

4. Insert data.

Postgres =# INSERT INTO position VALUES(1, (1, 1)); Postgres =# INSERT INTO position(coordinate. X) VALUES(2); INSERT 0 1 postgres=# SELECT * FROM position; No | coordinate - + -- -- -- -- -- -- -- -- -- -- -- -- 1 | | (1, 1) (2) (2 rows)Copy the code

5. Update data.

Postgres =# UPDATE position SET coordinate=(10,20) WHERE no=1; UPDATE 1 postgres=# SELECT * FROM position; No | coordinate - + -- -- -- -- -- -- -- -- -- -- -- - | | (2) 1 (10, 20) (2 rows) - single field UPDATE postgres = # UPDATE position SET coordinate. The y = 2 WHERE no is null; UPDATE 1 postgres=# SELECT * FROM position; No | coordinate - + -- -- -- -- -- -- -- -- -- -- -- -- 1 | | (10, 20) (2, 2) (2 rows) postgres = # UPDATE position SET position. Coordinate. Y = 3 WHERE no is null; UPDATE 1 postgres=# SELECT * FROM position; No | coordinate - + -- -- -- -- -- -- -- -- -- -- -- -- 1 | | (10, 20) (2 rows) (2, 3)Copy the code

eggs

If the table name, column name and field name are the same, there will be ambiguity. How does the database handle ambiguity?

Postgres =# CREATE TYPE newType AS(no INT, info text); Postgres =# CREATE TABLE info(no INT, info newtype)DISTRIBUTE BY ROUNDROBIN; Postgres =# INSERT INTO info VALUES(1, (1, 'MIKE')); Postgres =# SELECT * FROM info; no | info ----+---------- 1 | (1,MIKE) (1 row)Copy the code

Info.info can be a table name if the table name and column name are the same as the field name in the compound type. The column name can also be the column name. The field name, what is it actually?

postgres=# UPDATE info SET info.info='JACK' WHERE no=1;
NOTICE:  update field 'info' of column 'info', though it's ambiguous.
UPDATE 1
postgres=# SELECT * FROM info;
 no |   info   
----+----------
  1 | (1,JACK)
(1 row)
Copy the code

We can see from the execution prompts that the database has found ambiguity. And it is the fields in the columns that are ultimately updated.

As you can see from this, the database processing for ambiguous updates has a defined priority, here is the column name. Field name > table name. The column name.

Another question is, what happens to the database if the schema name is the same?

-- CREATE SCHEMA postgres=# CREATE SCHEMA info; Postgres =# SET CURRENT_SCHEMA=info; Postgres =# CREATE TYPE newType AS(no INT, info text); Postgres =# CREATE TABLE info(no INT, info newtype)DISTRIBUTE BY ROUNDROBIN; Postgres =# INSERT INTO info VALUES(1, (1, 'MIKE')); Postgres =# UPDATE info SET info.info='JACK' WHERE no=1; NOTICE: update field 'info' of column 'info', though it's ambiguous. UPDATE 1 postgres=# SELECT * FROM info; No | info - + -- -- -- -- -- -- -- -- -- - | 1 row (1) - (1, JACK) info. Info. The info represents the name of the table. Postgres =# UPDATE info SET info.info.info='TOM' WHERE no=1; UPDATE 1 postgres=# SELECT * FROM info; No | info - + -- -- -- -- -- -- -- -- - | 1 row (1) - (1, TOM) info. Info. No representative is the name of the table. Column name. Field name, this is ambiguous but does not seem to prompt, why? Postgres =# UPDATE info SET info.info.no=2 WHERE no=1; postgres=# UPDATE info SET info.info.no=2; UPDATE 1 postgres=# SELECT * FROM info; No | info - + -- -- -- -- -- -- -- -- - | 1 row (1) - (2, TOM) info. Info. Info. The info update error, that is to say after the SET can't use the schema name, Postgres =# UPDATE info SET info.info.info='JACK' WHERE no=1; postgres=# UPDATE info SET info.info.info='JACK' WHERE no=1; ERROR: cannot assign to field "info" of column "info" because its type text is not a composite type LINE 1: UPDATE info SET info.info.info.info='JACK' WHERE no=1; ^ CONTEXT: referenced column: infoCopy the code

You can see that the UDPATE SET cannot have a schema, otherwise an error will be reported.

Postgres =# CREATE TABLE test(an INT)DISTRIBUTE BY ROUNDROBIN; Postgres =# UPDATE test SET info.test.a=1; ERROR: column "info.test" of relation "test" does not exist LINE 1: UPDATE test SET info.test.a=1; ^Copy the code

As you can see from the above error message, the database considers info.test after SET to be a column of the test table. The table definition does not, so an error must be reported.

Click to follow, the first time to learn about Huawei cloud fresh technology ~