How to create a composite type in PostgreSQL using pgAdmin?

Question

I'm pretty new to postgres. Essentially what I'm looking to do is have a table, where each entry contains basically a struct (in the C-parlance) or an object.

I believe this is called a composite type? Anyway it would essentially be like this (pseudocode):

user_name = {
    my_int_value    : 100,
    my_string_value : "some string",
    my_array        : (1, 2, 3, 4, 5, 6)
}

I'm using pgAdmin to muck around.

Problem is, when I add a table and then go to add a column, I don't see any data type that would seem to represent such a data structure. I thought there was a composite type that would essentially let me do this.

My primary goal is to be able to do this via pgAdmin, but if that's not possible at all, I'm open to other ideas for the easiest way to go about doing it.

user330315 · Accepted Answer · 2013-12-11 07:23:03Z

2

The design seems highly strange - you probably should read up on normalization.

You first need to create such a type in order to be able to use it. I don't use pgAdmin, so I have no idea what the UI looks like but the SQL to do so, would be something like this:

create type user_name as
(
  my_int_value integer,
  my_string_value text,
  my_array int[]
);

create table foobar
(
  id integer not null primary key,
  some_column text,
  the_name user_name
);

insert into foobar (id, some_column, the_name)
values
(1, 'foo', (1, 'bar', array[1,2,3]));

answered Dec 11, 2013 at 7:23

user330315

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

Collectives™ on Stack Overflow

How to create a composite type in PostgreSQL using pgAdmin?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related