7

In ORDBMS databases I have seen that complex types can be used:

create type name as( ...)

And I can references those types when creating a new table, like:

create table example (row_name ref(name))

How can I achieve the same in PostgreSQL?

9

You can use a typed table:

CREATE TYPE mytype AS (some_id int, some_col text);

CREATE TABLE example OF mytype (PRIMARY KEY (some_id));

I added a PK constraint (which you did not ask for).

The syntax is documented as second variant in the manual for CREATE TABLE, which also explains further down:

OF type_name

Creates a typed table, which takes its structure from the specified composite type (name optionally schema-qualified). A typed table is tied to its type; for example the table will be dropped if the type is dropped (with DROP TYPE ... CASCADE).

When a typed table is created, then the data types of the columns are determined by the underlying composite type and are not specified by the CREATE TABLE command. But the CREATE TABLE command can add defaults and constraints to the table and can specify storage parameters.

There is also a code example at the end of the examples section.

3

I'm not sure if you want to create a table with a given composite type (as Erwin describes) or one containing a composite type.

If the latter, it's almost the same.

create type typename as( ...)

then

create table example (
   row_name typename
)

Typically you'd have more columns, like:

create table example (
   id serial primary key,
   some_composite typename,
   parent_id integer not null references parenttable(id),
   .... more columns ...
)
  • That's another way to read the question. row_name was my clue to assume a typed table ... – Erwin Brandstetter Sep 25 '15 at 2:27
  • @ErwinBrandstetter I think you're quite likely right. I don't know what ref(x) means and didn't find it in a 30s search. I just thought it was worth explaining in case the user did mean use of a composite type as a field. – Craig Ringer Sep 25 '15 at 2:30
  • It's worth explaining in any case. If this user didn't mean it the next one finding this question might. – Erwin Brandstetter Sep 25 '15 at 2:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.