PostgreSQL Alter Table: Alter a table to add an index

12. Write a SQL statement to add an index named index_job_id on job_id column in the table job_history.

Here is the structure of the table job_history.

postgres=# \d job_history

    Column     |         Type          | Modifiers
---------------+-----------------------+-----------
 employee_id   | numeric(6,0)          |
 start_date    | date                  |
 end_date      | date                  |
 job_id        | character varying(10) |
 department_id | numeric(4,0)          |

Now execute the following statement.

Sample Solution:

Code:

CREATE UNIQUE INDEX CONCURRENTLY index_job_id ON job_history(job_id);
ALTER TABLE job_history ADD CONSTRAINT index_job_id PRIMARY KEY USING INDEX index_job_id;

Output:

Now see the structure of the table job_history after being altered.

postgres=# \d job_history

    Column     |         Type          | Modifiers
---------------+-----------------------+-----------
 employee_id   | numeric(6,0)          |
 start_date    | date                  |
 end_date      | date                  |
 job_id        | character varying(10) | not null
 department_id | numeric(4,0)          |
Indexes:
    "index_job_id" PRIMARY KEY, btree (job_id)

Go to:

• PostgreSQL Alter Table Exercises Home. ↩
• PostgreSQL Exercises Home ↩

PREV : Write a SQL statement to drop the existing foreign key fk_job_id from job_history table on job_id column, which is referenced to the job_id of jobs table
NEXT : Write a SQL statement to drop the index indx_job_id from job_history table.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.