Menu
I'm sorry if this is a duplicate, although I couldn't find an exact answer for this anywhere:
Is there a way to create an array in postgreSQL which contains multiple data types?
I have an column of type text[] (array of type text); although I'd like to insert into this array three text entries and then a fourth entry, from type integer.
Is there a way to do so? If so, how?
661
PostgreSQL allows columns of a table to be defined as variable-length multidimensional arrays. Arrays of any built-in or user-defined base type, enum type, composite type, range type, or domain can be created.
To create a column of an array type, the [] symbol is used. The following examples illustrate this: create table contacts ( first_name varchar, last_name varchar, phone_numbers varchar[] ); create table player_scores ( player_number integer, round_scores integer[] );
When you are considering portability (e.g. rewriting your system to work with other databses) then you must not use arrays. If you are sure you'll stick with Postgres, then you can safely use arrays where you find appropriate. They exist for a reason and are neither bad design nor non-compliant.
PostgreSQL supports a character data type called TEXT. This data type is used to store character of unlimited length. It is represented as text in PostgreSQL. The performance of the varchar (without n) and text are the same.
I don't believe there's a way to declare an array with multiple types; however, I think you can accomplish what you are trying to do with a composite type, e.g.,
create type my_item as (
field_1 text,
field_2 text,
field_3 text,
field_4 number
);
You could then use this as the column type for your table or even declare a column of arrays of my_item[] if that fits your need.
75
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
