CODEWITHSUNDEEP
sqlpostgresql
user2923767
user2923767

Reputation: 637

Postgres - CREATE TABLE FROM SELECT

I have two tables, one contains a large list of IDs and Info regarding those ids.

I have a second table Graph which just has two columns, each column contains the aforementioned id numbers, multiple times. I want to trim the size of my Info table by selecting only those ids that appear in my graph and creating a new smaller Info table. Is there a simple way of doing this? 

CREATE TABLE FROM SELECT?

Thanks!

Upvotes: 48

Views: 85426

Answers (2)

Ilesh Patel
Ilesh Patel

Reputation: 2155

You can create TEMP table if you need those small table only for that session. you can use below query to do that.

  DROP TABLE IF EXISTS temp_table;
    CREATE TEMP TABLE temp_table AS
     SELECT 
       i.id as info_id, i.information as information
     FROM
      info i
      INNER JOIN graph g ON i.id = g.id;

Now you can use this temp_table for your next table in the function.

                    OR

you can also create table like below (if you not want to create it as TEMP):

CREATE TABLE temp_table AS
     SELECT 
       i.id as info_id, i.information as information
     FROM
      info i
      INNER JOIN graph g ON i.id = g.id;

Upvotes: 22

user330315
user330315

Reputation:

It's as easy as:

create table new_table
as 
select t1.col1, t2.col2
from some_table t1
   join t2 on t1.id = t2.some_id;

You can use any select statement for that. The column names of the new table are defined by the column aliases used in th query.

More details in the manual: http://www.postgresql.org/docs/current/static/sql-createtableas.html

Upvotes: 70

Related Questions