Question 89505 in PostgreSQL Question posted in
The official documentation can be found here.

CREATE TYPE TypeName AS varchar(256)[]; This method of creating an Array in PostgreSQL is not working

I can not create a Type of VArray in PostgreSQL.

I have this code in Oracle for the creation of the type:

TYPE FIELDS IS VARRAY(14) OF VARCHAR2(256);

and this is the code that I put in PostgreSQL PgAdmin:

CREATE TYPE FIELDS AS varchar(256)[];

Is there any other ways to create this type?

Tags:

2

Answers


  1. 0

    Theoretically you don’t need to do anything to use a type as an array.
    Every type in postgres has its corresponding array type (see https://www.postgresql.org/docs/current/sql-createtype.html#SQL-CREATETYPE-ARRAY).

    So, to use a varchar(256) array in a table, you could:

    create table test1 (fields varchar(256)[]);

    If you need to add more constraints to a type, you can look at create domain as well.

    As an aside, I’ve read many times that text is a better type than varchar(N) (if it’s just about disk space optimization).
    If you want to guarantee a max text length, you could always use a check (length(value) < 255) on your column definition.

    Login or Signup to reply.
  2. 0

    You cannot declare a new type as an array, but you can create a domain over this type and use that like a data type:

    CREATE DOMAIN fields AS varchar(256)[];

    It is often not a good idea to use arrays as column data type.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search