Postgres Pro
Downloads
Home   >   mailing lists

Thread: Adding a unique number to each record in a table

Adding a unique number to each record in a table

From
Alexander Farber
Date:
Hello,

I have a table in PostgreSQL 8.4.9 storing
user comments (in the "about" field):

# \d pref_rep
                 Table "public.pref_rep"
   Column   |            Type             |   Modifiers
------------+-----------------------------+---------------
 id         | character varying(32)       |
 author     | character varying(32)       |
 good       | boolean                     |
 fair       | boolean                     |
 nice       | boolean                     |
 about      | character varying(256)      |
 last_rated | timestamp without time zone | default now()
 author_ip  | inet                        |
Check constraints:
    "pref_rep_check" CHECK (id::text <> author::text)
Foreign-key constraints:
    "pref_rep_author_fkey" FOREIGN KEY (author) REFERENCES pref_users(id)
    "pref_rep_id_fkey" FOREIGN KEY (id) REFERENCES pref_users(id)

But now after having 40000 comments I've
realized, that I need a web script for me as
admin - for delete obscene/aggressive ones.

Because doing it thru "psql" is tedious + error prone.

Is there please a way to add a column to my table,
which I could use to uniquely identify each
record in that table (incl. old records)

Regards
Alex

Re: Adding a unique number to each record in a table

From
Andy Colson
Date:
On 10/24/2011 11:19 AM, Alexander Farber wrote:
> Hello,
>
> I have a table in PostgreSQL 8.4.9 storing
> user comments (in the "about" field):
>
> # \d pref_rep
>                   Table "public.pref_rep"
>     Column   |            Type             |   Modifiers
> ------------+-----------------------------+---------------
>   id         | character varying(32)       |
>   author     | character varying(32)       |
>   good       | boolean                     |
>   fair       | boolean                     |
>   nice       | boolean                     |
>   about      | character varying(256)      |
>   last_rated | timestamp without time zone | default now()
>   author_ip  | inet                        |
> Check constraints:
>      "pref_rep_check" CHECK (id::text<>  author::text)
> Foreign-key constraints:
>      "pref_rep_author_fkey" FOREIGN KEY (author) REFERENCES pref_users(id)
>      "pref_rep_id_fkey" FOREIGN KEY (id) REFERENCES pref_users(id)
>
> But now after having 40000 comments I've
> realized, that I need a web script for me as
> admin - for delete obscene/aggressive ones.
>
> Because doing it thru "psql" is tedious + error prone.
>
> Is there please a way to add a column to my table,
> which I could use to uniquely identify each
> record in that table (incl. old records)
>
> Regards
> Alex
>

alter table pref_rep add rowid serial;

Will both add the column and set it for existing rows, as well as for
new rows.

-Andy