CODEWITHSUNDEEP
postgresqltemp-tables
btilly
btilly

Reputation: 46445

Getting temporary table creation statements from PostgreSQL

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.

Upvotes: 2

Views: 816

Answers (2)

Nick Barnes
Nick Barnes

Reputation: 21356

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;

Upvotes: 2

Daniel Lyons
Daniel Lyons

Reputation: 22803

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.

Upvotes: 0

Related Questions