What are the names of these Postgres concepts?

Question

What are the names for the concepts highlighted in the following SQL table? If Postgres has particular names for them then I'd prefer those, otherwise the general SQL names would be helpful.

I'm especially interested in the name for 5 because I don't really have a clue.

My guesses/intuition are

1. Table specification

2. Row

3. Column name

4. Column

5. Not sure. "Field"?

The background to this question is that I maintain a PostgreSQL API for Haskell and I would like to give these concepts their correct names in my API.

Answer 1

If you want relational theory names, then:

1. Heading (but nobody calls it that, usually "field names" or "attribute names" or "column names" is more typical)
2. Tuple
3. Attribute name
4. Attribute (but that'll confuse people)
5. Attribute value

... and a data type is a domain. The whole table toether is a relation.

Nobody really uses that language though. More typical would be:

1. Table definition (if it includes data types, constraints, etc), or column names if it doesn't.
2. Tuple or row
3. Field name or column name
4. Column (if it includes name) or column values (if it doesn't); but the latter is uncommon in relational DBs since you rarely refer to a column except by selecting fields out of tuples.
5. Field or attribute value

The whole thing together is usually called a table, but relation is common enough too.

---

Whatever names you choose to use, please do not assume that tuples have any natural ordering in a relation. They don't.

And don't bake column ordering into your API. Column ordering is stable within a query result, of course, but apps should be discouraged from hard coding column ordinal positions. This is OK (pseudocode):

 zip(['col1', 'col2', 'col3'], do_query('SELECT col1, col2, col3 FROM my_table WHERE ...'))

but this is a bad, bad idea:

 zip(['col1', 'col2', 'col3'], do_query('SELECT * FROM my_table WHERE ...)]

as any addition of a column will break the code. So will less obvious changes like altering the data type of a column.

Apps should be encouraged to use query interfaces that produce a field-name to value map as a result, where field names are discovered from the result set metadata.

For efficiency you still want to offer ordinal access. But field name access is usually preferable.

Python for example does this with a result object that can behave as both an array (ordinal access) and dictionary (key access).

---

You've omitted data typing though. Relational DBs are strongly typed.

Here's a more typical table definition:

demo-> \d address
                                Table "public.address"
   Column    |  Type   |                          Modifiers                           
-------------+---------+--------------------------------------------------------------
 address_id  | integer | not null default nextval('address_address_id_seq'::regclass)
 customer_id | integer | not null
 address     | text    | 
Indexes:
    "address_pkey" PRIMARY KEY, btree (address_id)
Foreign-key constraints:
    "address_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customer(customer_id)

Critically, note that every column has a name, a type, and zero or more constraints like not null or primary key. There are also various other details like foreign key constraints.

Answer 2

1. Header
2. Row
3. Column name
4. Column
5. Value (though the thing that holds the value is a Cell)