CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.3
Peter Penzov
Peter Penzov

Reputation: 1754

Unique ID in PostgreSQL 9.4 table

I want to insert unique ID record into PostgreSQL 9.4 table.

INSERT INTO ACCOUNT_HISTORY(ID, ACCOUNT_ID, USERNAME, COMMENT, CREATED) VALUES (?, (select ID from ACCOUNT where USER_NAME = ?), ?, ?, CURRENT_DATE)

Table

CREATE TABLE ACCOUNT_HISTORY(
 ID INTEGER NOT NULL,
 ACCOUNT_ID INTEGER,
 USERNAME TEXT,
 COMMENT TEXT,
 CREATED DATE
)
;

CREATE INDEX IX_RELATIONSHIP8 ON ACCOUNT_HISTORY (ACCOUNT_ID)
;

How I can generate unique ID for every insert query?

Upvotes: 0

Views: 83

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

Use serial:

CREATE TABLE ACCOUNT_HISTORY (
   ID SERIAL,
   ACCOUNT_ID INTEGER,
   USERNAME TEXT,
   COMMENT TEXT,
   CREATED DATE
);

Then, do not include it in the insert statement:

INSERT INTO ACCOUNT_HISTORY(ACCOUNT_ID, USERNAME, COMMENT, CREATED)
    SELECT ID, ?, ?, ?, CURRENT_DATE
    FROM ACCOUNT 
    WHERE USER_NAME = ?;

EDIT:

If you have data in the table (so you don't want to just drop it):

create temporary table temp_account_history as
    select *
    from account_history;

drop account_history;

CREATE TABLE ACCOUNT_HISTORY (
   ID SERIAL,
   ACCOUNT_ID INTEGER,
   USERNAME TEXT,
   COMMENT TEXT,
   CREATED NOT NULL DEFAULT CURRENT_DATE
);

INSERT INTO ACCOUNT_HISTORY(ACCOUNT_ID, USERNAME, COMMENT, CREATED)
    SELECT ACCOUNT_ID, USERNAME, COMMENT, CREATED
    FROM TEMP_ACCOUNT_HISTORY;

Note that this also gives CREATED a default value, so you don't have to include that in subsequent inserts either.

Upvotes: 2

Related Questions