Creating a PostgreSQL sequence to a field (which is not the ID of the record)

Question

Accepted Answer

Use `CREATE SEQUENCE`:

```
CREATE SEQUENCE scores_job_id_seq;  -- = default name for plain a serial
```

Then add a column default to `scores.job_id`:

```
ALTER TABLE scores ALTER COLUMN job_id
SET DEFAULT nextval('scores_job_id_seq');
```

If you want to "bind" the sequence to the column (so it is deleted when the column is deleted), also run:

```
ALTER SEQUENCE scores_job_id_seq OWNED BY scores.job_id;
```

All of this can be replaced with using the pseudo data type `serial` for the column `job_id` to begin with. See:

- Safely rename tables using serial primary key columns

If your table already has rows, you may want to set the `SEQUENCE` to the next highest value:

```
SELECT setval('scores_job_id_seq', COALESCE(max(job_id), 1)) FROM scores;
```

And, fill in missing (null) serial values in the table (if any):

```
UPDATE scores
SET    job_id = nextval('scores_job_id_seq')
WHERE  job_id IS NULL;
```

- How to check a sequence efficiently for used and unused values in PostgreSQL

- Postgres manually alter sequence

- How to reset Postgres' primary key sequence when it falls out of sync?

The only remaining difference: a `serial` column is also set to `NOT NULL`. You may or may not want that, too:

```
ALTER TABLE scores ALTER COLUMN job_id SET NOT NULL;
```

But you cannot just alter the type of an existing `integer`:

```
ALTER TABLE scores ALTER job_id TYPE serial;
```

`serial` is not an actual data type. It's just a notational convenience feature for `CREATE TABLE`. In Postgres 10 or later consider an `IDENTITY` column instead:

- Auto increment table column

Creating a PostgreSQL sequence to a field (which is not the ID of the record)

Solution

Problem

Related problems