0

Is it possible to make a table that has like auto-incrementing id's for every unique name that I make in the table?

For example:

ID   NAME_ID    NAME  
----------------------
1       1       John
2       1       John
3       1       John
4       2       Mary
5       2       Mary
6       3       Sarah
7       4       Lucas

and so on.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Ovelion
  • 61
  • 4
  • What you show is *not* an *"auto-incrementing id for every unique name"*. It's the same id for the same name, and a classical normalization issue. Is database normalization a viable alternative for you? Either way, please start any such question with your Postgres version (`SELECT version ();`) and the current table definition (`CREATE TABLE` script). – Erwin Brandstetter Jun 06 '22 at 21:41

2 Answers2

1

Use the window function rank() to get a unique id per name. Or dense_rank() to get the same without gaps:

SELECT id, dense_rank() OVER (ORDER BY name) AS name_id, name
FROM   tbl;

I would advise not to write that redundant information to your table. You can generate that number on the fly. Or you shouldn't store name redundantly in that table, name would typically live in another table, with name_id as PRIMARY KEY.

Then you have a "names" table and run "SELECT or INSERT" there to get a unique name_id for every new entry in the main table. See:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
-2

First add the column to the table.

ALTER TABLE yourtable
ADD [UID] INT NULL;

``
ALTER TABLE yourtable
ADD constraint fk_yourtable_uid_id foreign key ([UID]) references yourtable([Serial]);

Then you can update the UID with the minimum Serial ID per Name.

UPDATE t
SET [UID] = q.[UID]
FROM yourtable t
JOIN
(
  SELECT Name, MIN([Serial]) AS [UID]
  FROM yourtable
  GROUP BY Name
) q ON q.Name = t.Name
WHERE (t.[UID] IS NULL OR t.[UID] != q.[UID]) -- Repeatability
Leofc97
  • 1
  • 1