Jan 19, 2014

JSON Lint - validation tool for JSON

There are several validation tools for JSON. These tools help you to write and test JSON data or schema.

  • JSONLint
    • This tool validates that JSON data is valid or not.
    • It does not deal with JSON schema.
  • JSON Schema Lint
    • This tool validates
      • JSON schema is valid
      • JSON data is valid
      • JSON data is valid against JSON schema

Jan 12, 2014

INSERT VS COPY: The fastest way to do a bulk insert into PostgreSQL

It takes time to populate lots of data in database by many "INSERT" statements. The official PostgreSQL documentation mentions the topic.

PostgreSQL 9.3.2 Documentation Chapter 14. Performance Tips 14.4. Populating a Database

The chapter shows many options, but I am paying attention to first two.

Disable autocommit

PostgreSQL commits each statement automatically. That means if you run an INSERT statement, it runs like this for each statement:

  1. Open a transaction
  2. Insert data
  3. Close the transaction

It is redundant. In this case, it becomes faster if you use autocommit like this:

BEGIN; -- the beginning of the transaction
END;    -- the end of the transaction

PostgreSQL does not commit the statements between BEGIN and END.


COPY is also to use populate data, but the different point is that COPY does not offer autocommit. It means that PostgreSQL commit after all data is populated on a database.
Please refer to the document about how to use COPY statement.

Auto increment primary key in PostgreSQL

If you want to increment primary key automatically in Postgres, "serial" is a good option.

Please that you do not need to specify a primary key to insert data to your database.