Getting temporary table creation statements from PostgreSQL

Question

In PostgreSQL you can create a temporary table very easily with:

CREATE TEMPORARY TABLE tmp_foo AS
SELECT bar, baz FROM some_table WHERE ...;

Is there any easy way to get a create statement for that table? I can make queries to get information about the table structure (eg in psql I can use \d) but that doesn't give me a creation statement that I can use.

I would like this so that I can materialize queries in one PostgreSQL database, get the table structure of the query, create that temporary table in another, and then use COPY commands to copy data from one to the other before proceeding.

Nick Barnes · Accepted Answer · 2015-12-12 00:52:31Z

2

In general, reconstructing the CREATE TABLE statement gets pretty complicated, if you need to account for column defaults, foreign keys, check constraints, etc.

But the product of a CREATE TABLE AS ... statement won't have any of this, so it's relatively straightforward. Plug your table name into the WHERE clause of this query:

SELECT
  format(
    'CREATE TEMP TABLE %s (%s)',
    attrelid::regclass,
    string_agg(
      format(
        '%I %s',
        attname,
        format_type(atttypid, atttypmod)
      ),
      ','
      ORDER BY attnum
    )
  )
FROM pg_attribute
WHERE
  attrelid = 'tmp_foo'::regclass AND
  attnum > 0 AND
  NOT attisdropped
GROUP BY attrelid

As a side-note, instead of doing this:

CREATE TEMP TABLE tmp_foo AS SELECT ... ;
COPY tmp_foo TO STDOUT;

... you can get the structure of the SELECT result without actually running it, which avoids creating an intermediate copy of the data:

CREATE TEMP TABLE tmp_foo AS SELECT ... WITH NO DATA;
COPY (SELECT ...) TO STDOUT;

edited Dec 12, 2015 at 0:52

answered Dec 12, 2015 at 0:31

Nick Barnes

21.8k5 gold badges64 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

btilly Over a year ago

Perfect! It is too bad this isn't a native function.

Daniel Lyons · Accepted Answer · 2015-12-11 21:06:06Z

0

The easiest trick would be to use pg_dump:

$ pg_dump -s -t tmp_foo _dbname_

Of course, this will only work for a non-temporary table. But you could also just create a regular table and do a pipe like pg_dump ... | psql ... or pg_dump ... | pg_restore ... to do your task.

answered Dec 11, 2015 at 21:06

Daniel Lyons

22.9k3 gold badges54 silver badges82 bronze badges

6 Comments

btilly Over a year ago

I have done things like that before, but there is a problem if I do not have permissions to create tables. Also it is a code maintenance problem where every time the query changes, the table creation also has to change. (I've seen that screw up too many times.)

Daniel Lyons Over a year ago

Using pg_dump can get you past the query change problem, but not the permission one.

btilly Over a year ago

Using pg_dump can't automate the query change problem.

Daniel Lyons Over a year ago

I don't see how it doesn't. It doesn't know anything about the query, it's just fetching a table from database A and writing it to database B.

Daniel Lyons Over a year ago

This suggests to me that you have per-user tables, which is a normalization smell. Probably you should have one big table that contains every user's stuff and have a system user do the moving around, then use views or procedures to confine access to that table to matching users.

|

Collectives™ on Stack Overflow

Getting temporary table creation statements from PostgreSQL

2 Answers 2

1 Comment

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related