2

For my Project Purpose I want to create a table from the data fetched from a view. I am using the basic statement:

CREATE TABLE TABLE_NAME AS (SELECT * FROM VIEW_NAME) ;

The problem is that Around 3 cores of data will be fetched from that view and as the view has joins on many tables and many conditions are applied the performance of the view is bit slow. When I am trying the basic syntax (as mentioned above) after sometime the session is getting timed out and hence it fails. Any alternative way to do this?

wogsland
  • 9,106
  • 19
  • 57
  • 93
Abinash Satapathy
  • 61
  • 1
  • 6
  • Your doing this task directly on the database or via front end code ?? if you are using it with a command object the use `command.timeout=0` – Vivek S. Dec 19 '15 at 06:44
  • just create the table using `CREATE TABLE table_name AS (SELECT * FROM view_name LIMIT 0);` and export view to a csv using [copy](http://www.postgresql.org/docs/9.4/static/sql-copy.html) and import it to the created table, take a look at https://wiki.postgresql.org/wiki/COPY and http://stackoverflow.com/questions/17662631/how-to-copy-from-csv-file-to-postgresql-table-with-headers-in-csv-file – Vivek S. Dec 19 '15 at 06:47

1 Answers1

1

an alternative way will be using Postgres Copy option.but you will have to create table schema prior to copy. so actual query will be

CREATE TABLE yourtable AS (SELECT * FROM view With no Data);Copy select * from view to yourtable;

you can follow the provided link to know advanced options to increase performance of copy command.hope this helps.

akhil kumar
  • 1,598
  • 1
  • 13
  • 26
  • Side Note : [Use `Postgres/PostgreSQL`never `Postgre`](http://stackoverflow.com/tags/postgresql/info) – Vivek S. Dec 21 '15 at 09:55