Creating an Identifier that Combines Multiple Other Columns

Question

I'm working on a DB and would like to implement a system where a tables unique ID is generated by combining several other IDs/factors. Basically, I'd want an ID that looks like this:

1234 (A reference to a standard incrementing serial ID from another table)
10 (A reference to a standard incrementing serial ID from another table)
1234 (A number that increments from 1000-9999)

So the ID would look like:

1234101234

Additionally, each of those "entries" will have multiple time sensitive instances that are stored in another table. For these IDs I want to take the above ID and append a time stamp, so it'll look like:

12341012341234567890123

I've looked a little bit at PSQL sequences, but they seem like they're mostly used for simply incrementing up or down at certain levels, I'm not sure how to do this sort of concatenation in creating an ID string or whether it's even possible.

Do **not** do that. A single column should not store more than a single atomic value. This violates the first rule of database normalization. — , Jun 04 '18 at 16:05
If you absolutely have to have this sort of value in the database, fake a computed/virtual column with a view https://stackoverflow.com/questions/8250389/computed-calculated-columns-in-postgresql — David Faber, Jun 04 '18 at 16:07

score 3 · Accepted Answer · answered Jun 04 '18 at 15:30

Don't do it! Just use a serial primary key id and then have three different columns:

otherTableID
otherTable2ID
timestamp

You can uniquely identify each row using your serial id. You can look up the other information. And -- even better -- you can create foreign key constraints to represent the relationships among the tables.

score 0 · Answer 2 · answered Jun 04 '18 at 15:50

I'm not sure what do you want to achive, but

SELECT col_1::text || col_2::text || col_3::text || now()::text

should work. You should also add UNIQUE constraint on the column, i.e.

 ALTER TABLE this_table ADD UNIQUE INDEX (this_new_column);

But the real question is: why do you want to do this? If you just want a unique meaningless ID, you need just to create column of type serial.

score 0 · Answer 3 · answered Jun 04 '18 at 16:40

create procedure f_return_unq_id(
    CONDITIONAL_PARAMS IN INTEGER,
    v_seq in out integer
)
is 
    QUERY_1 VARCHAR2(200);
    RESP INTEGER;
BEGIN
    QUERY_1:='SELECT TAB1.SL_ID||TAB2.SL_ID||:v_seq||SYSTIMESTAMP FROM TABLE1 TAB1,TABLE2 TAB2 WHERE TAB1.CONDITION=:V_PARAMS';
    BEGIN
        EXECUTE IMMEDIATE QUERY_1 INTO RESP USING v_seq,CONDITIONAL_PARAMS;
    EXCEPTION
        when others then
            DBMS_OUTPUT.PUT_LINE(SQLCODE);
    END;
    v_seq:=RESP;
EXCEPTION
    when others then
        DBMS_OUTPUT.PUT_LINE(SQLCODE);
END;

pass the v_seq to this procedure as your sequence number 1000-9999 and conditional parameters if any are there.

I Agree, This is valid for Oracle DB – Atul Kr Dey Jun 05 '18 at 03:07 — Atul Kr Dey, Jun 05 '18 at 03:07

Creating an Identifier that Combines Multiple Other Columns

3 Answers3