is primary key automatically indexed in postgresql?

Question

enter image description here

I have created table name as d with ID column as primary key and then just inserted records as shown in output, but after fetching all records this output still displayed same as order in which records are inserted. but output as a see now not in ordered form.

Your table is so small that Postgres will do a sequential load of the whole table rather than bother with index. It probably all fits on a single page. That is why you're seeing the data in the order you loaded it. — Gary - Stand with Ukraine, Mar 20 '17 at 09:49
Tables are not ordered. Your expectation to get any order without order by is unjustified. — philipxy, Sep 15 '18 at 04:25
Possible duplicate of [What is the order of records in a table with a composite primary key](https://stackoverflow.com/questions/13190720/what-is-the-order-of-records-in-a-table-with-a-composite-primary-key) — philipxy, Sep 15 '18 at 04:28
Possibly needs an edit, but weird this was closed. The title was exactly my question, and the answer was exactly what I wanted to know :/ — Josh Mc, Jun 13 '23 at 10:15

score 144 · Answer 1 · answered Mar 20 '17 at 08:40

144

PostgreSQL automatically creates an index for each unique constraint and primary key constraint to enforce uniqueness. Thus, it is not necessary to create an index explicitly for primary key columns. (See CREATE INDEX for more information.)

Source: Docs

answered Mar 20 '17 at 08:40

maxhuang

2,681
1
21
26

3

This answers the question as posed in the title but does not disabuse the asker of their wrong expectation, founding the body of the question, that indexes are relevant to the (undefined) order of result rows for a query without `order by`. What the PK is or whether it is indexed is irrelevant. – philipxy Sep 15 '18 at 23:08
7

From their documentation, to be more exact: Adding a PRIMARY KEY constraint will automatically create a unique btree index on the column or group of columns used in the constraint. – Sargsyan Grigor Sep 26 '19 at 13:46

score 23 · Answer 2 · answered Mar 20 '17 at 08:46

but after fetching all records this output still displayed same as order in which records are inserted

There is NO default "sort" order - even if there is an index on that column (which indeed is the case in Postgres: the primary key is supported by a unique index in the background)

Rows in a relational table are not sorted.

The only (really: the only) way to get a specific order is to use an ORDER BY

If you do not specify an ORDER BY the database is free to return the rows in any order it wants - and that order can change at any time.

The order can change because of various reasons:

other sessions are running the same statement
the table was updated
the execution plan changes
...

score 12 · Answer 3 · edited Sep 20 '19 at 14:07

In addition to what the others have said, Postgres does not have a concept of a 'Clustered Index' like Microsoft SQL Server and other databases have. You can cluster an index, but it is a one-time operation (until you call it again) and will not maintain the clustering of rows upon edits, etc. See the docs

I was running into the same thing you were, where I half expected the rows to be returned in order of primary key (I didn't insert them out of order like you did, though). They did come back upon initial insert, but editing a record in Postgres seems to move the record to the end of the page, and the records quickly became out of order (I updated fields other than the primary key).

is primary key automatically indexed in postgresql?

3 Answers3

Rows in a relational table are not sorted.

Linked