DML - Inserting Special Values

Inserting Special Values :

• The CURRENT_DATE function records the current date and time.
• INSERT INTO emp (empno, ename,hiredate, job, sal, comm, mgr,deptno) VALUES (1132, 'Louis',CURRENT_DATE, 'AC_ACCOUNT', 7000, NULL, 7768, 70);

Inserting Specific Date Values :

Add a new employee :

• INSERT INTO emp (empno, ename,hiredate, job, sal, comm, mgr,deptno) VALUES ( 1114,'Den', TO_DATE('FEB 3, 1999', 'MON DD, YYYY'), 'AC_ACCOUNT', 3600, NULL, 7900, 40);

Verify your addition :

• Select*from emp;

Creating a Script :

• Open vi editor using vi command
• Type in your insert command
• Save your file using :wq filename
• Use psql –f option to execute the stored file in training database.
• E.g
  • ./psql –f in.sql –d training –U postgres –p 5432

Copying Rows from Another Table :

Write your INSERT statement with a subquery :

• INSERT INTO sales_reps(id, name, sal, comm)
  SELECT empno, ename, sal, comm
  FROM emp WHERE job=‘SALESMAN’;
• Do not use the VALUES clause.
• Match the number of columns in the INSERT clause to those in the subquery.