CODEWITHSUNDEEP
postgresqlrdbmspostgresql-9.1postgresql-9.3postgresql-9.2
RMu
RMu

Reputation: 869

PostgreSQL - CREATE TABLE AS vs INSERT INTO performance comparision

I'm trying to insert couple of million rows into a PostgreSQL database. I am wondering what is the best way to do it.

  1. CREATE TABLE AS
  2. INSERT INTO

I'm looking to see which one is better and why? I have read through some blogs but still couldn't come to a conclusion.

I think INSERT INTO is a bulk insert operation. Please correct me if I'm wrong. Whether CREATE TABLE AS SELECT is a bulk insert operation?

Please advise.

Upvotes: 0

Views: 2081

Answers (1)

Florian Weimer
Florian Weimer

Reputation: 33704

CREATE TABLE AS is a bulk insert operation as well. The main difference is that CREATE TABLE AS is easier to optimize for PostgreSQL; it is clear that no WAL information has to be written (unless WAL-based replication is active, of course). See the wal_level documentation and Disable WAL Archival and Streaming Replication for some other cases where this optimization applies.

Upvotes: 4

Related Questions