3

I recently switched from MySQL to PostgreSQL and I noticed one additional query after following many "regular" queries.

Immediately after this...

SELECT "documents".* FROM "documents" WHERE ("documents"."id" = 1) LIMIT 1

this is executed:

SELECT a.attname, format_type(a.atttypid, a.atttypmod), d.adsrc, a.attnotnull
 FROM pg_attribute a LEFT JOIN pg_attrdef d
 ON a.attrelid = d.adrelid AND a.attnum = d.adnum
 WHERE a.attrelid = '"documents"'::regclass
 AND a.attnum > 0 AND NOT a.attisdropped
 ORDER BY a.attnum

What does this last query accomplish? Which information is returned here?

Improve this question
1
  • What database access layer are you using? Commented Dec 18, 2010 at 21:14

1 Answer 1

Reset to default
3

The query is fetching information about the table documents.

The catalog pg_attribute stores information about table columns.

The catalog pg_attrdef stores column default values.

  • a.attrelid: The table this column belongs to.
  • a.attname: The column name.
  • a.atttypid: The data type of this column.
  • a.atttypmod: Records type-specific data supplied at table creation time (for example, the maximum length of a varchar column). It is passed to type-specific input functions and length coercion functions. The value will generally be -1 for types that do not need atttypmod.
  • d.adsrc: A human-readable representation of the default value.
  • a.attnotnull: This represents a not-null constraint.

This information could for example be used by an ORM to construct a mapping between a class and a table in the database.

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.