INSERT INTO
Warning
At the moment, YDB's compatibility with PostgreSQL is under development, so not all PostgreSQL constructs and functions are supported yet. PostgreSQL compatibility is available for testing in the form of a Docker container, which can be deployed by following these instructions.
Syntax of the INSERT INTO statement:
INSERT INTO <table name> (<column name>, ...)
VALUES (<value>);
The INSERT INTO statement is adds rows to a table. It can insert one or several rows in a single execution. Example of inserting a single row into the "people" table:
INSERT INTO people (name, lastname, age, country, state, city, birthday, sex)
VALUES ('John', 'Doe', 30, 'USA', 'California', 'Los Angeles', CAST('1992-01-15' AS Date), 'Male');
In this query, we did not specify the id column and did not assign a value to it. This is intentional, as the "id" column in the "people" table is set to the Serial data type. When executing the INSERT INTO statement, the value of the "id" column will be assigned automatically, taking into account previous values, the current "id" value will be incremented.
For inserting multiple rows into a table, the same construction is used with the enumeration of groups of data to be inserted, separated by commas:
INSERT INTO people (name, lastname, age, country, state, city, birthday, sex)
VALUES
('Jane', 'Smith', 25, 'Canada', 'Ontario', 'Toronto', CAST('1997-08-23' AS Date), 'Female'),
('Alice', 'Johnson', 28, 'UK', 'England', 'London', CAST('1994-05-05' AS Date), 'Female'),
('Bob', 'Brown', 40, 'USA', 'Texas', 'Dallas', CAST('1982-12-10' AS Date), 'Male'),
('Charlie', 'Davis', 35, 'Canada', 'Quebec', 'Montreal', CAST('1987-02-17' AS Date), 'Male'),
('Eve', 'Martin', 29, 'UK', 'Scotland', 'Edinburgh', CAST('1993-11-21' AS Date), 'Female'),
('Frank', 'White', 45, 'USA', 'Florida', 'Miami', CAST('1977-03-14' AS Date), 'Male'),
('Grace', 'Clark', 50, 'Canada', 'British Columbia', 'Vancouver', CAST('1972-04-26' AS Date), 'Female'),
('Hank', 'Miller', 33, 'UK', 'Wales', 'Cardiff', CAST('1989-07-30' AS Date), 'Male'),
('Ivy', 'Garcia', 31, 'USA', 'Arizona', 'Phoenix', CAST('1991-09-05' AS Date), 'Female'),
('Jack', 'Anderson', 22, 'Canada', 'Manitoba', 'Winnipeg', CAST('2000-06-13' AS Date), 'Male'),
('Kara', 'Thompson', 19, 'UK', 'Northern Ireland', 'Belfast', CAST('2003-10-18' AS Date), 'Female'),
('Liam', 'Martinez', 55, 'USA', 'New York', 'New York City', CAST('1967-01-29' AS Date), 'Male'),
('Molly', 'Robinson', 40, 'Canada', 'Alberta', 'Calgary', CAST('1982-12-01' AS Date), 'Female'),
('Noah', 'Lee', 47, 'UK', 'England', 'Liverpool', CAST('1975-05-20' AS Date), 'Male'),
('Olivia', 'Gonzalez', 38, 'USA', 'Illinois', 'Chicago', CAST('1984-03-22' AS Date), 'Female'),
('Paul', 'Harris', 23, 'Canada', 'Saskatchewan', 'Saskatoon', CAST('1999-08-19' AS Date), 'Male'),
('Quinn', 'Lewis', 34, 'UK', 'England', 'Manchester', CAST('1988-07-25' AS DATE), 'Female'),
('Rachel', 'Young', 42, 'USA', 'Ohio', 'Cleveland', CAST('1980-02-03' AS Date), 'Female');
In both examples, to specify the release date of the movie, we used the CAST() function, which is used to convert one data type to another. In this case, using the keyword AS and the data type Date, we explicitly indicated that we want to convert the string representation of the date in ISO8601 format.
You can specify the required data type, for example, DATE, by using the type cast operator ::, which is a PostgreSQL-specific syntax for explicit conversion of a value from one data type to another. This is in contrast to the CAST function, which is used more broadly across different SQL databases for the same purpose. An example of using the :: operator might look like this: '2023-01-01'::DATE, which explicitly converts the string to a DATE type, ensuring the database treats the value as a date. This explicit casting with :: is particularly useful when you want to override implicit type conversion rules of the database.
An example of using the :: operator might look like this:
INSERT INTO people (name, lastname, age, country, state, city, birthday, sex)
VALUES ('Sam', 'Walker', 60, 'Canada', 'Nova Scotia', 'Halifax', '1962-04-15'::Date, 'Male');
Note
Unlike PostgreSQL, YDB uses optimistic locking. This means that transactions check the conditions for the necessary locks at the end of their operation, not at the beginning. If the lock has been violated during the transaction's execution, such a transaction will end with a Transaction locks invalidated error. In this case, you can try to execute a similar transaction again.