Multiple data types array in PostgreSQL

Question

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?

ig0774 · Accepted Answer · 2011-11-17 13:39:18Z

20

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.

answered Nov 17, 2011 at 13:39

ig0774

41.6k3 gold badges61 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

EliyaMelamed Over a year ago

Should I write this code in python file? or inside postgres? (if yes where? )

Alexi Theodore Over a year ago

You CAN use JSON(B) to accomplish this task. Ex: SELECT '[1, "text", true]'::JSONB; but there are limitations with that, namely that JSON only supports a limited number of data types which don't match 1:1 with postgres' native data types. But it is a quick basic solution that isn't necessarily hacky.

Collectives™ on Stack Overflow

Multiple data types array in PostgreSQL

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related