Composite types in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a composite type in PostgreSQL?

A composite type is a user-defined data type that groups multiple fields together, like a mini table row, allowing you to store related data as a single unit.

beginner

How do you create a composite type in PostgreSQL?

Use the CREATE TYPE statement with AS and list the fields with their data types inside parentheses. For example: <br>CREATE TYPE address AS (street text, city text, zip int);

intermediate

How can you use a composite type in a table?

You can define a column in a table to have the composite type, so each row stores a structured value. For example: <br>CREATE TABLE person (name text, home address); where address is a composite type.

intermediate

How do you access individual fields of a composite type column in a query?

Use the dot notation to get a field. For example, if home is a composite type column, SELECT home.city FROM person; returns the city field.

advanced

Can composite types be nested inside other composite types?

Yes, composite types can include fields that are themselves composite types, allowing complex structured data within a single column.

Which SQL command creates a composite type in PostgreSQL?

A CREATE TYPE ... AS (...)

B CREATE TABLE ... AS (...)

C CREATE DOMAIN ... AS (...)

D CREATE FUNCTION ... AS (...)

The correct syntax to create a composite type is using CREATE TYPE ... AS (...).

How do you select the 'zip' field from a composite type column named 'home'?

A SELECT home->zip FROM table;

B SELECT zip(home) FROM table;

C SELECT home.zip FROM table;

D SELECT zip FROM home;

Use dot notation: SELECT home.zip FROM table; to access the 'zip' field.

Can a composite type be used as a column type in a PostgreSQL table?

A Yes, it can store multiple related fields in one column.

B No, composite types are only for functions.

C No, composite types cannot be stored in tables.

D Yes, but only for temporary tables.

Composite types can be used as column types to store grouped fields.

What happens if you try to insert a row with missing fields for a composite type column?

A The insert fails due to missing data.

B PostgreSQL fills missing fields with NULL.

C PostgreSQL ignores the composite column.

D The row is inserted but fields have default values.

Missing fields in composite types default to NULL if not provided.

Is it possible to nest composite types inside other composite types?

A Composite types cannot be nested at all.

B No, composite types must only have simple data types.

C Only arrays can be nested, not composite types.

D Yes, composite types can contain other composite types.

Composite types can be nested to create complex structures.

Explain what a composite type is in PostgreSQL and how you create one.

Think of it like a small table structure inside a type.

Describe how to use a composite type as a column in a table and how to query its fields.

Remember you can treat the composite column like an object with properties.