Running queries without having to manually delete created tables

Question

New to SQL, using PostgreSQL.

I'm trying a few beginner problems, but I have a problem. To make sure I don't run into any unknown errors while testing things, I'm writing the queries in steps i.e

CREATE TABLE x(...);

...

run query..

CREATE TABLE x(...);

CREATE TABLE y(...);

run query

--- and so on.

Problem is, after I created a table, I have to delete it if I want to try and run the query again, otherwise I get the

'ERROR: relation "x" already exists.'

Seems like a trivial problem, but after looking around in stackoverflow, google, postgre's website, I haven't found anything.

score 1 · Answer 1 · answered Aug 05 '17 at 14:00

1

You can either drop the table if it exists, or create the table if it doesn't exist, depending on your needs.

DROP TABLE IF EXISTS tableName ...

CREATE TABLE IF NOT EXISTS tableName ...

Check out the documenation for creating a table or dropping a table.

answered Aug 05 '17 at 14:00

Wolfie

1,369
8
16

but let's say I changed something in one of the tables, will it still delete it? or will it read it as a 'new' table? – Immanuel Aug 05 '17 at 14:10
@Immanuel If you drop the table, it will delete everything. But if you create a new table that already exists, it will just append rows to the table, leaving all existing data in place. – Wolfie Aug 05 '17 at 14:12
so I would have to use the 'IF NOT EXISTS' for each table I create correct? is there anything 'all inclusive'? – Immanuel Aug 05 '17 at 14:16
You can also [drop all the tables at once](https://stackoverflow.com/questions/3327312/drop-all-tables-in-postgresql) – Wolfie Aug 05 '17 at 14:24

score 1 · Answer 2 · answered Aug 05 '17 at 14:13

You shouldn't use temporary tables -- unless you have specific performance (or other) reasons for doing so.

Instead use common table expressions (CTEs):

with x as (
      <code here>
     ),
     y as (
      <code here>
     ),
     . . .
select . . .

CTEs are part of the query, so they do not persist between query runs. Voila! No problem with naming or temporary tables.

score 0 · Answer 3 · answered Aug 05 '17 at 14:03

0

If you're really just practicing and the data isn't important you can use TEMPORARY tables. Then a simple disconnect and reconnect throws them away and gives you a clean slate.

CREATE TEMPORARY TABLE x(...);

Or you can even use transaction-scoped temp tables, which are dropped on commit or rollback:

BEGIN;

CREATE TEMPORARY TABLE x(...) ON COMMIT DROP;

INSERT ...

COMMIT;

Or, of course, you can just do your iterative development in a transaction that you ROLLBACK at the end.

BEGIN;

CREATE TABLE x(...);

INSERT ...

ROLLBACK;

answered Aug 05 '17 at 14:03

Craig Ringer

307,061
76
688
778

I get a different 'ERROR: constraints on permanent tables may reference only permanent tables' – Immanuel Aug 05 '17 at 14:49
@Immanuel You'd have to use all temp, or all permanent, tables in your tests. You can't have a foreign key constraint from a permanent table to a temp one or vice versa. – Craig Ringer Aug 06 '17 at 01:16

Running queries without having to manually delete created tables

3 Answers3