0

I have a complicated query I'm working on. It involves several tables.

It would be very helpful for me to create a new table and then simply query from that. However, this is a shared database and I don't want to make a new table, especially when i don't plan on using that table specifically. (I just want it as a stepping stone in my query)

Is it possible to create a table just for 1 query that deletes right when the query is done? (i.e a temporary table)

Daniel64
  • 29
  • 2
  • 8

4 Answers4

3

Sure. Use CREATE TEMPORARY TABLE:

=> CREATE TEMPORARY TABLE secret_table(id BIGSERIAL, name text);

=> INSERT INTO secret_table(name) VALUES ('Good day');
INSERT 0 1

=> INSERT INTO secret_table(name) VALUES ('Good night');
INSERT 0 1

=> SELECT * FROM secret_table;
 id |    name
----+------------
  1 | Good day
  2 | Good night
(2 rows)

But upon reconnection:

psql (9.5.4)
Type "help" for help.

=> SELECT * FROM secret_table;
ERROR:  relation "secret_table" does not exist
LINE 1: SELECT * FROM secret_table;
Adam Matan
  • 128,757
  • 147
  • 397
  • 562
  • 1
    The `ON COMMIT DROP` option might also be useful when running in a transaction. – Marth Nov 16 '16 at 20:38
  • Sure, that would be useful within a single transaction scope; I think the OP was aiming at a session scope. I think it's good to have the temporary table stable between transactions, and not re-create it whenever the transaction ends due to silly typo. – Adam Matan Nov 16 '16 at 20:54
  • 1
    Yeah I agree it's a rather rarely useful option, tailored to specific workflows. Just though that, as the question is rather unclear on the specifics (actually a CTE might be what is needed), it might be worth mentioning. – Marth Nov 16 '16 at 21:05
2

You could use temporary tables which drops itself at the end of session in which they were created (not after the query finishes, as you've said). Though, you could always drop it manually at the end of your operation.

If you'd like to create such table as a result from a query then this is the sample to be expanded to your needs:

CREATE TEMP TABLE tmp_table_name AS ( SELECT 1 AS col1 );

But I'm thinking you may be looking for a CTE instead of a table since you're saying that you're planning to use it only once. Consider this:

WITH tmp_table AS ( SELECT 1 AS col1 )
SELECT *
FROM tmp_table
...
Kamil Gosciminski
  • 16,547
  • 8
  • 49
  • 72
0

You can also do dinamically The result of a query is also a Table 

 select *  from (select col1, col2, col3  
                from my_complex_table 
                ... ) t1
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
0

use keyword temporary, the temporary table is only visible in your current connection and drop after you disconnect your connection.

The other way would create a table and drop the table by yourself when you don't need it

buqing
  • 925
  • 8
  • 25