small medium large xlarge

26 Nov 2016, 03:04
Mohammad Esmaeili (4 posts)

Hi David,

I would like to thank you for your very useful well-written book.

1) I am wondering if the 1st version of book/codes also work with PostgreSQL 9.6?

2) I am using postgreSQL 9.6 and in Ch. 8, when I am creating the Materialized View by the rake db:migrate, it stops with the error:

ERROR: could not create unique index “customer_details_customer_id”
DETAIL: Key (customer_id)=(1) is duplicated.
CREATE UNIQUE INDEX customer_details_customer_id ON customer_details(customer_id)

I removed the code that creates the Unique Index, so It does create the View, and indeed the customer_id=1 is duplicated , that is why the initial rake command stops.

Please advise how to fix. thanks a lot.

26 Nov 2016, 15:49
David Copeland (499 posts)

The code in the book should work with Postgres 9.6. Postgres doesn’t usually make breaking changes.

The error you are getting would happen if your view doesn’t produce unique rows for each customer ID. Is there any chance there’s a typo in it?

You can check by doing something like select count(*),customer_id from customer_details group by customer_id having count(*) > 1

That should show you all customer ids that have more than one row in thre view.

26 Nov 2016, 19:00
Mohammad Esmaeili (4 posts)

thanks a lot David.

I dropped the address-related tables and recreated them. and the Materialized View works now with the same code. not sure what went wrong earlier.

One observation I had was that creating the Materialized VIEW was very fast (rake db:migrate takes <few seconds). I was expecting to take longer as it joins several big tables.

is this correct?

FYI, the complex query to the Materialized View runs correctly and returns the data in less than 2 msec.

thanks for your time !.

26 Nov 2016, 20:36
David Copeland (499 posts)

Glad things are working.

Creating the view will take time proportional to the amount of data in your database. If you inserted all 350K records, it should take more than a few seconds, but I guess you can sanity-check by querying various customers in your DB and making sure they all come back.

You must be logged in to comment