CODEWITHSUNDEEP
sqldatabasepostgresql
Aan
Aan

Reputation: 12900

Date field to be filled automatically

I am using PostgreSQL and created the below table:

CREATE TABLE "TrainingMatrix"
(
  payroll text NOT NULL,
  "TrainingName" text NOT NULL,
  "Institute" text,
  "TrainingDate" date NOT NULL,
  "ExpiryDate" date,
  "RecorderName" text,
  "EnteringDate" date,
  CONSTRAINT "TrainingMatrix_pkey" PRIMARY KEY (payroll, "TrainingName", "TrainingDate")
)

I want to let EnteringDate to be filled automatically by the current date in the machine for each entered record.

Upvotes: 1

Views: 1622

Answers (4)

krish
krish

Reputation: 1

By using the default sysdate you can achive the desired output.

CREATE TABLE "TrainingMatrix"
(
  payroll text NOT NULL,
  "TrainingName" text NOT NULL,
  "Institute" text,
  "TrainingDate" date NOT NULL,
  "ExpiryDate" date,
  "RecorderName" text,
  "EnteringDate" date default sysdate,
  CONSTRAINT "TrainingMatrix_pkey" PRIMARY KEY (payroll, "TrainingName", "TrainingDate")
)

Upvotes: 0

sunysen
sunysen

Reputation: 2351

CREATE TABLE "TrainingMatrix"
(
  payroll text NOT NULL,
  "TrainingName" text NOT NULL,
  "Institute" text,
  "TrainingDate" date NOT NULL,
  "ExpiryDate" date,
  "RecorderName" text,
  "EnteringDate" date NOT NULL default current_date,--"EnteringDate" date,
  CONSTRAINT "TrainingMatrix_pkey" PRIMARY KEY (payroll, "TrainingName", "TrainingDate")
)

Upvotes: 1

cbeckner
cbeckner

Reputation: 1818

CREATE TABLE "TrainingMatrix"
(
  payroll text NOT NULL,
  "TrainingName" text NOT NULL,
  "Institute" text,
  "TrainingDate" date NOT NULL,
  "ExpiryDate" date,
  "RecorderName" text,
  "EnteringDate" date not null default current_timestamp,
  CONSTRAINT "TrainingMatrix_pkey" PRIMARY KEY (payroll, "TrainingName", "TrainingDate")
)

current_timestamp is equivelent to Now() will give the current date & time (including timezone) http://www.postgresql.org/docs/8.1/static/functions-datetime.html

Defining the column as not null will also help to ensure that no records are explicitely entered that do not have a timestamp.

Upvotes: 1

Abhijith Nagarajan
Abhijith Nagarajan

Reputation: 4030

use default keyword

column datatype default sysdate

Check this as well

How to set default value for column of new created table from select statement in 11g

Upvotes: 0

Related Questions