PostgreSQL with natural language support
In today's fast development cycles and with the available, rapidly changing technologies there is less time for the rational planning of a new application. Since the database is usually the last in the development chain, programmers tend to avoid learning yet another programming language for relational databases. The PostgreSQL Project is aware of this problem.
After a few more or less successful attempts to simplify the interface in the last years, queries can from now on be written in a natural language. A first proof of concept is now available.
To enable this feature, the new GUC "natural_language" must be changed from "off" to "english":
test=# SET natural_language TO 'english'; SET test=# SET client_min_messages TO 'notice'; SET
From now on queries can be formulated in the English language. If users find the concept handy, other languages are also possible. A error-free English or American spelling is currently a mandatory requirement.
Queries must no longer be formally written in SQL, the ending semicolon can be optionally replaced with a dot.
test=# return all rows from table customer. NOTICE: Translated natural query into: SELECT * FROM "customer";
test=# I need the first three lines from table customer, only columns id and name. NOTICE: Translated natural query into: SELECT id, name FROM "customer" LIMIT 3;
test=# Please retrn all from table purchases, sorted by date. ERROR: syntax error at or near "all" ROW 1: Please retrn all from... ^ test=# Please return all from table purchases, sorted by date. NOTICE: Translated natural query into: SELECT * FROM "purchases" ORDER BY date;
Joins are possible:
test=# Join tables purchases and customer on foreign key. NOTICE: Translated natural query into: SELECT t1.*, t2.* FROM "customer" INNER JOIN "purchases" ON t1."id" = t2."customer_id";
Where conditions too:
test=# Fetch all rows from table purchases with today's date in the date column, ordered by primary key. NOTICE: Translated natural query into: SELECT * FROM "purchases" WHERE "date" = NOW()::DATE ORDER BY "id";
test=# Return "name" from the customer table where the value starts with an 'a'. NOTICE: Translated natural query into: SELECT "name" FROM "customer" WHERE "name" ~ '^a';
Tables can be described with words:
test=# Need a table for user_data, which has a surrogate key, a timestamp, and a username and password column, both text. NOTICE: Choosing "id" for surrogate key column. NOTICE: Choosing "inserted_at" for timestamp column. NOTICE: Translated natural query into: CREATE TABLE "user_data" ("id" SERIAL PRIMARY KEY, "inserted_at" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), "username" TEXT, "password" TEXT);
More complex tables should be created in the traditional way, to not confuse the language parser.
Insert and update rows:
test=# Write into user_data, username is 'ads' and password is 'secret'. NOTICE: Translated natural query into: INSERT INTO "user_data" ("username", "password") VALUES ('ads', 'secret');
test=# Modify user_data table, new value for password column is 'top secret'. NOTICE: Translated natural query into: UPDATE "user_data" SET "password" = 'top secret';
Using functions is easy:
test=# Execute the md5 function on column password on table user_data. NOTICE: Translated natural query into: SELECT md5(password) FROM user_data; test=# Update the fourth column on table user_data with the md5 function. ERROR: "fourth" is ambiguous ROW 1: Update the fourth column on... ^ test=# Update the password column on table user_data with the md5 function. NOTICE: Translated natural query into: UPDATE "user_data" SET "password" = md5("password");
More supported commands:
test=# Start a transaction. NOTICE: Translated natural query into: BEGIN; test=# Rollback everything. NOTICE: Translated natural query into: ROLLBACK; test=# Finish transaction. NOTICE: Translated natural query into: COMMIT; test=# Cleanup table customer. NOTICE: Translated natural query into: VACUUM "customer"; test=# Compress table customer. NOTICE: Translated natural query into: VACUUM FULL "customer"; test=# Read data from file '/tmp/customer.txt' into table customer. NOTICE: Translated natural query into: COPY "customer" FROM '/tmp/customer.txt'; test=# create another database. ERROR: missing database name ROW 1: create another database. ^ test=# create another database test2. NOTICE: Translated natural query into: CREATE DATABASE "test2";
Unsupported commands, example:
Parsing is done in the server, psql commands are not supported.
test=# change database to test2. ERROR: unknown command "change database" ROW 1: change database to test2. ^
The language parser is currently somewhat restricted in it's functionality, but will be expanded in future versions.
Since PostgreSQL is an open source product, patches are always welcome.
We strongly expect that this new feature will help the less experienced developers to achieve faster results.
To enable this feature, the new GUC "natural_language" must be changed from "off" to "english":
test=# SET natural_language TO 'english'; SET test=# SET client_min_messages TO 'notice'; SET
From now on queries can be formulated in the English language. If users find the concept handy, other languages are also possible. A error-free English or American spelling is currently a mandatory requirement.
Queries must no longer be formally written in SQL, the ending semicolon can be optionally replaced with a dot.
test=# return all rows from table customer. NOTICE: Translated natural query into: SELECT * FROM "customer";
test=# I need the first three lines from table customer, only columns id and name. NOTICE: Translated natural query into: SELECT id, name FROM "customer" LIMIT 3;
test=# Please retrn all from table purchases, sorted by date. ERROR: syntax error at or near "all" ROW 1: Please retrn all from... ^ test=# Please return all from table purchases, sorted by date. NOTICE: Translated natural query into: SELECT * FROM "purchases" ORDER BY date;
Joins are possible:
test=# Join tables purchases and customer on foreign key. NOTICE: Translated natural query into: SELECT t1.*, t2.* FROM "customer" INNER JOIN "purchases" ON t1."id" = t2."customer_id";
Where conditions too:
test=# Fetch all rows from table purchases with today's date in the date column, ordered by primary key. NOTICE: Translated natural query into: SELECT * FROM "purchases" WHERE "date" = NOW()::DATE ORDER BY "id";
test=# Return "name" from the customer table where the value starts with an 'a'. NOTICE: Translated natural query into: SELECT "name" FROM "customer" WHERE "name" ~ '^a';
Tables can be described with words:
test=# Need a table for user_data, which has a surrogate key, a timestamp, and a username and password column, both text. NOTICE: Choosing "id" for surrogate key column. NOTICE: Choosing "inserted_at" for timestamp column. NOTICE: Translated natural query into: CREATE TABLE "user_data" ("id" SERIAL PRIMARY KEY, "inserted_at" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), "username" TEXT, "password" TEXT);
More complex tables should be created in the traditional way, to not confuse the language parser.
Insert and update rows:
test=# Write into user_data, username is 'ads' and password is 'secret'. NOTICE: Translated natural query into: INSERT INTO "user_data" ("username", "password") VALUES ('ads', 'secret');
test=# Modify user_data table, new value for password column is 'top secret'. NOTICE: Translated natural query into: UPDATE "user_data" SET "password" = 'top secret';
Using functions is easy:
test=# Execute the md5 function on column password on table user_data. NOTICE: Translated natural query into: SELECT md5(password) FROM user_data; test=# Update the fourth column on table user_data with the md5 function. ERROR: "fourth" is ambiguous ROW 1: Update the fourth column on... ^ test=# Update the password column on table user_data with the md5 function. NOTICE: Translated natural query into: UPDATE "user_data" SET "password" = md5("password");
More supported commands:
test=# Start a transaction. NOTICE: Translated natural query into: BEGIN; test=# Rollback everything. NOTICE: Translated natural query into: ROLLBACK; test=# Finish transaction. NOTICE: Translated natural query into: COMMIT; test=# Cleanup table customer. NOTICE: Translated natural query into: VACUUM "customer"; test=# Compress table customer. NOTICE: Translated natural query into: VACUUM FULL "customer"; test=# Read data from file '/tmp/customer.txt' into table customer. NOTICE: Translated natural query into: COPY "customer" FROM '/tmp/customer.txt'; test=# create another database. ERROR: missing database name ROW 1: create another database. ^ test=# create another database test2. NOTICE: Translated natural query into: CREATE DATABASE "test2";
Unsupported commands, example:
Parsing is done in the server, psql commands are not supported.
test=# change database to test2. ERROR: unknown command "change database" ROW 1: change database to test2. ^
The language parser is currently somewhat restricted in it's functionality, but will be expanded in future versions.
Since PostgreSQL is an open source product, patches are always welcome.
We strongly expect that this new feature will help the less experienced developers to achieve faster results.
Trackbacks
No Trackbacks
Comments
Display comments as Linear | Threaded
Santiago Zarate on :
Andreas 'ads' Scherbaum on :
Pavel Golub on :
Chaitany Kulkarni on :
russell on :