Skip to content

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.
  • Twitter
  • Bookmark PostgreSQL with natural language support at del.icio.us
  • Facebook
  • Google Bookmarks
  • FriendFeed
  • Digg PostgreSQL with natural language support
  • Bloglines PostgreSQL with natural language support
  • Technorati PostgreSQL with natural language support
  • Fark this: PostgreSQL with natural language support
  • Bookmark PostgreSQL with natural language support at YahooMyWeb
  • Bookmark PostgreSQL with natural language support at Furl.net
  • Bookmark PostgreSQL with natural language support at reddit.com
  • Bookmark PostgreSQL with natural language support at blinklist.com
  • Bookmark PostgreSQL with natural language support at Spurl.net
  • Bookmark PostgreSQL with natural language support at Simpy.com
  • Bookmark PostgreSQL with natural language support at blogmarks
  • Bookmark PostgreSQL with natural language support with wists
  • wong it!
  • Bookmark using any bookmark manager!
  • Stumble It!
  • Identi.ca

Trackbacks

No Trackbacks

Comments

Display comments as Linear | Threaded

Santiago Zarate on :

Wow! Amazing feature! will there be a "slang" or "h4x0r" locale support?
Comments ()

Pavel Golub on :

What about right-to-left languages support, e.g. arabic, hebrew etc.?
Comments ()

Chaitany Kulkarni on :

I think all natural languages are supported. May be I can mix up words in Hindi and English too like तैयार करो database test
Comments ()

russell on :

awesome work, natural language tends to be difficult to parse. I myself would love something a little more symbolic. my example is flawed as I do not understand query internals well but... (select (returning (count (alias name) ) (alias name) ) (from (join tape_log alias (eq (tape_log who) (alias alias)) ) (order (alias name) desc) ) ) however all the more reguler forms tend to come in some sort of orm that serializes to sql, one more level of abstraction that I instinctively avoid.
Comments ()

Add Comment

Enclosing asterisks marks text as bold (*word*), underscore are made via _word_.
E-Mail addresses will not be displayed and will only be used for E-Mail notifications.
To leave a comment you must approve it via e-mail, which will be sent to your address after submission.
Form options