YouTip

## PostgreSQL INSERT INTO Statement The PostgreSQL `INSERT INTO` statement is used to insert new records (rows) into a table. You can insert a single row or multiple rows at the same time. --- ## Syntax There are two primary ways to write an `INSERT INTO` statement, depending on whether you want to specify the column names. ### 1. Specifying Column Names (Recommended) This method specifies both the column names and the values to be inserted. It is highly recommended because it keeps your queries robust even if the table schema changes (e.g., if new columns are added). ```sql INSERT INTO table_name (column1, column2, column3, ... columnN) VALUES (value1, value2, value3, ... valueN); ``` * **`column1, column2, ... columnN`**: The names of the columns in the table where you want to insert data. * **`value1, value2, ... valueN`**: The corresponding values for these columns. > **Note:** The number of values must match the number of specified columns, and the data types must be compatible with the respective columns in the exact order they are listed. ### 2. Without Specifying Column Names If you are inserting values for **all** columns in the table, you can omit the column names. However, you must ensure that the values are provided in the exact order of the columns as they were defined when the table was created. ```sql INSERT INTO table_name VALUES (value1, value2, value3, ... valueN); ``` --- ## Understanding the Command Output When you execute an `INSERT` statement in PostgreSQL, the database returns a confirmation message. Here is what the output messages mean: | Output Message | Description | | :--- | :--- | | **`INSERT oid 1`** | Returned when exactly one row is inserted into a table configured with OIDs (Object Identifiers). The `oid` is the unique numeric identifier assigned to the inserted row. *(Note: OIDs are deprecated in modern PostgreSQL versions).* | | **`INSERT 0 #`** | Returned when one or more rows are successfully inserted. The `#` represents the total number of rows inserted (e.g., `INSERT 0 1` for one row, `INSERT 0 5` for five rows). | --- ## Practical Examples To demonstrate how to use the `INSERT INTO` statement, let's first create a sample table named `COMPANY` in our database: ```sql CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL, JOIN_DATE DATE ); ``` ### Example 1: Inserting a Complete Row The following statement inserts a complete record into the `COMPANY` table, specifying all columns and their values: ```sql INSERT INTO COMPANY (ID, NAME, AGE, ADDRESS, SALARY, JOIN_DATE) VALUES (1, 'Paul', 32, 'California', 20000.00, '2001-07-13'); ``` **Output:** ```text INSERT 0 1 ``` ### Example 2: Omitting Columns (Inserting Partial Data) You can omit columns that allow `NULL` values or have default values. In this example, we omit the `SALARY` column: ```sql INSERT INTO COMPANY (ID, NAME, AGE, ADDRESS, JOIN_DATE) VALUES (2, 'Allen', 25, 'Texas', '2007-12-13'); ``` **Output:** ```text INSERT 0 1 ``` ### Example 3: Using the `DEFAULT` Keyword If a column has a default value (or is nullable), you can explicitly use the `DEFAULT` keyword to instruct PostgreSQL to populate it with its default value: ```sql INSERT INTO COMPANY (ID, NAME, AGE, ADDRESS, SALARY, JOIN_DATE) VALUES (3, 'Teddy', 23, 'Norway', 20000.00, DEFAULT); ``` **Output:** ```text INSERT 0 1 ``` ### Example 4: Inserting Multiple Rows in a Single Statement You can insert multiple rows efficiently using a single `INSERT INTO` statement by separating the value sets with commas: ```sql INSERT INTO COMPANY (ID, NAME, AGE, ADDRESS, SALARY, JOIN_DATE) VALUES (4, 'Mark', 25, 'Rich-Mond', 65000.00, '2007-12-13'), (5, 'David', 27, 'Texas', 85000.00, '2007-12-13'); ``` **Output:** ```text INSERT 0 2 ``` --- ## Verifying the Inserted Data To verify that the records have been successfully written to the database, run a `SELECT` query: ```sql SELECT * FROM COMPANY; ``` **Result:** ```text ID NAME AGE ADDRESS SALARY JOIN_DATE ---- ---------- ----- ---------- ------- -------- 1 Paul 32 California 20000.0 2001-07-13 2 Allen 25 Texas 2007-12-13 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 2007-12-13 5 David 27 Texas 85000.0 2007-12-13 ``` --- ## Key Considerations 1. **Data Type Constraints**: Ensure that the values you insert match the data types of the columns. For example, trying to insert a string into an `INT` column will result in an error. 2. **Not Null Constraints**: Any column defined with `NOT NULL` must be provided with a value during insertion unless it has a defined `DEFAULT` value. 3. **String and Date Formatting**: In PostgreSQL, string literals and date values must be enclosed in single quotes (e.g., `'Paul'`, `'2023-10-25'`). 4. **Performance Tip**: When inserting large volumes of data, inserting multiple rows in a single `INSERT` statement (as shown in Example 4) is significantly faster than executing individual `INSERT` statements for each row. For extremely large datasets, consider using the PostgreSQL `COPY` command.