In postgresql; clarification on CONSTRAINT foo_key PRIMARY KEY (foo)

Question

Sorry if this is a dead simple question but I'm confused from the documentation and I'm not getting any clear answers from searching the web.

If I have the following table schema:

CREATE TABLE footable
(
  foo character varying(10) NOT NULL,
  bar timestamp without time zone,
  CONSTRAINT pk_foo PRIMARY KEY (foo)
);

and then use the query:

SELECT bar FROM footable WHERE foo = '1234567890';

Will the select query find the given row by searching an index or not? In other word: does the table have a primary key (which is foo) or not?

Just to get it clear. I'm used to specifying "PRIMARY KEY" after the column I'm specifying like this:

"...foo character varying(10) PRIMARY KEY, ..."

Does it change anything?

Answer 1

Why not look at the query plan and find out yourself? The query plan will tell you exactly what indexes are being used, so you don't have to guess. Here's how to do it: http://www.postgresql.org/docs/current/static/sql-explain.html

But in general, it should use the index in this case since you specified the primary key in the where clause and you didn't use something that could prevent it from using it (a LIKE, for example).

It's always best to look at the query plan to verify it for sure, then there's no doubt.

Answer 2

In both cases, the primary key can be used but it depends. The optimizer will make a choice depending on the amount of data, the statistics, etc.

Naming the constraint can make debugging and error handling easier, you know what constraint is violated. Without a name, it can be confusing.