PgDAC

Inserting Data Into Tables

This tutorial describes how to insert data into tables using the TPgQuery and TPgTable components.

  1. Requirements
  2. General Information
  3. Design-Time
  4. Runtime
  5. Additional Information

Requirements

This tutorial assumes that you already know how to connect to the server (see Connecting to PostgreSQL) and that the necessary objects have already been created on the server (see Creating Database Objects). To insert data at runtime, add the PgAccess unit to the uses clause for Delphi or include the PgAccess.hpp header file for C++ Builder.

General Information

You can insert data into a table using the Data Manipulation Language (DML), which is part of SQL. The user must have the appropriate privileges to execute DML statements on the server. There are two ways to manipulate data in a table: you can build DML statements manually and run them with a component like TPgQuery, or you can use the dataset functionality (the Insert, Append, and Post methods) of the TPgQuery and TPgTable components. This tutorial shows you how to insert data into the dept and emp tables.

The dept table definition:

deptno dname loc
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

The emp table definition:

empno ename job mgr hiredate sal comm deptno
7369 SMITH CLERK 7902 17-12-1980 800 NULL 20
7499 ALLEN SALESMAN 7698 20-02-1981 1600 300 30
7521 WARD SALESMAN 7698 22-02-1981 1250 500 30
7566 JONES MANAGER 7839 02-04-1981 2975 NULL 20
7654 MARTIN SALESMAN 7698 28-09-1981 1250 1400 30
7698 BLAKE MANAGER 7839 01-05-1981 2850 NULL 30
7782 CLARK MANAGER 7839 09-06-1981 2450 NULL 10
7788 SCOTT ANALYST 7566 13-07-1987 3000 NULL 20
7839 KING PRESIDENT NULL 17-11-1981 5000 NULL 10
7844 TURNER SALESMAN 7698 08-09-1981 1500 0 30
7876 ADAMS CLERK 7788 13-07-1987 1100 NULL 20
7900 JAMES CLERK 7698 03-12-1981 950 NULL 30
7902 FORD ANALYST 7566 03-12-1981 3000 NULL 20
7934 MILLER CLERK 7782 23-01-1982 1300 NULL 10

Inserting at Design-Time

Inserting at Runtime

Using the DataSet Functionality

The Insert, Append, and Post methods of the TPgQuery and TPgTable components allow you to insert data without having to manually construct a DML statement — it is generated by PgDAC components internally. Insert adds a new empty record in the current cursor position, while Append adds a new empty record at the end of the dataset. The code below demonstrates the use of these methods:

Delphi

var
  PgQuery1: TPgQuery;
begin
  PgQuery1 := TPgQuery.Create(nil);
  try
    // PgConnection1 was set up earlier
    PgQuery1.Connection := PgConnection1; 
    // adds a statement to retrieve data
    PgQuery1.SQL.Text := 'SELECT * FROM dept';
    // opens the dataset
    PgQuery1.Open;
    // adds a new empty record at the end of the dataset
    PgQuery1.Append;
    // searches fields by their names and assigns new values
    PgQuery1.FieldByName('deptno').AsInteger := 10;
    PgQuery1.FieldByName('dname').AsString := 'ACCOUNTING';
    PgQuery1.FieldByName('loc').AsString := 'NEW YORK';
    // writes the modified record
    PgQuery1.Post;
  finally
    PgQuery1.Free;
  end;
end;

C++Builder

TPgQuery* PgQuery1 = new TPgQuery(NULL);
try {
    // PgConnection1 was set up earlier
    PgQuery1->Connection = PgConnection1;
    // adds a statement to retrieve data
    PgQuery1->SQL->Text = "SELECT * FROM dept";
    // opens the dataset
    PgQuery1->Open();
    // adds a new empty record at the end of the dataset
    PgQuery1->Append();
    // searches fields by their names and assigns new values
    PgQuery1->FieldByName("deptno")->AsInteger = 10;
    PgQuery1->FieldByName("dname")->AsString = "ACCOUNTING";
    PgQuery1->FieldByName("loc")->AsString = "NEW YORK";
    // writes the modified record
    PgQuery1->Post();
}
__finally {
    PgQuery1->Free();
}

Building DML Statements Manually

DML statements can be constucted with or without parameters. The code below demonstrates both ways.

Parameterized DML Statements

Delphi

var
  PgQuery1: TPgQuery;
begin
  PgQuery1 := TPgQuery.Create(nil);
  try
    // PgConnection1 was set up earlier
    PgQuery1.Connection := PgConnection1;
    // adds a parameterized statement to insert data
    PgQuery1.SQL.Add('INSERT INTO dept(deptno, dname, loc) VALUES (:deptno, :dname, :loc);');
    // searches parameters by their names and assigns new values
    PgQuery1.ParamByName('deptno').AsInteger := 10;
    PgQuery1.ParamByName('dname').AsString := 'ACCOUNTING';
    PgQuery1.ParamByName('loc').AsString := 'NEW YORK';
    // executes the statement
    PgQuery1.Execute;
  finally
    PgQuery1.Free;
  end;
end;

C++Builder

TPgQuery* PgQuery1 = new TPgQuery(NULL);
try {
    // PgConnection1 was set up earlier
    PgQuery1->Connection = PgConnection1;
    // adds a parameterized statement to insert data 
    PgQuery1->SQL->Add("INSERT INTO dept(deptno, dname, loc) VALUES (:deptno, :dname, :loc);");
    // searches parameters by their names and assigns new values
    PgQuery1->ParamByName("deptno")->AsInteger = 10;
    PgQuery1->ParamByName("dname")->AsString = "ACCOUNTING";
    PgQuery1->ParamByName("loc")->AsString = "NEW YORK";
    // executes the statement
    PgQuery1->Execute();
}
__finally {
    PgQuery1->Free();
}

Non-Parameterized DML Statements

Delphi

var
  PgQuery1: TPgQuery;
begin
  PgQuery1 := TPgQuery.Create(nil);
  try
    // PgConnection1 was set up earlier
    PgQuery1.Connection := PgConnection1; 
    // adds a statement to insert a record
    PgQuery1.SQL.Add('INSERT INTO dept(deptno, dname, loc) VALUES (10,''ACCOUNTING'',''NEW YORK'');');
    // executes the statement
    PgQuery1.Execute;
  finally
    PgQuery1.Free;
  end;
end;

C++Builder

TPgQuery* PgQuery1 = new TPgQuery(NULL);
try {
    // PgConnection1 was set up earlier
    PgQuery1->Connection = PgConnection1;
    //adds the statement to insert a record 
    PgQuery1->SQL->Add("INSERT INTO dept(deptno, dname, loc) VALUES (10,'ACCOUNTING','NEW YORK');");
    // executes the statement
    PgQuery1->Execute();
}
__finally {
    PgQuery1->Free();
}

Additional Information

There are many ways to insert data into tables. Any tool or component that is capable of running an SQL query can be used to manage data. For example, TPgSQL can be used to insert records one by one, while TPgScript is designed to execute multiple DDL/DML statements as a single SQL script. TPgLoader is the fastest way to insert data into PostgreSQL tables.

It is also possible to use stored procedures to insert data, in which case all data manipulation logic is defined on the server. See Using Stored Procedures for more information.

© 1997-2021 Devart. All Rights Reserved. Request Support DAC Forum Provide Feedback