CODEWITHSUNDEEP
sqlpostgresqltemp-tables
cLupus
cLupus

Reputation: 563

Creation of a temporary table in postgres

I'm trying to create a temporary table in Postgres (to speed up joining, as there will be a lot of similar queries throughout a session). The SQL that will be called at the beginning of a session is the following:

CREATE TEMPORARY TABLE extended_point AS
SELECT (
  point.id,
  local_location,
  relative_location,
  long_lat,
  region,
  dataset,
  region.name,
  region.sub_name,
  color,
  type)
FROM point, region, dataset
WHERE point.region = region.id AND region.dataset = dataset.id;

The tables point has the columns id::int, region::int, local_location::point, relative_location::point, long_lat:point (longitude, latitude).

Region has the columns id::int, color::int, dataset::int, name::varchar, sub_name::varchar.

Dataset has the columns id::int, name::varchar, type:varchar.

When this is run, I get the error message: [25P02] ERROR: current transaction is aborted, commands ignored until end of transaction block.

As a side, the commands are executed in PyCharm, and is part of a Python project.

Any suggestions?

Thanks in advance :)

Upvotes: 2

Views: 2490

Answers (1)

klin
klin

Reputation: 121474

There is an important difference between these two queries:

select 1, 'abc';
select (1, 'abc');

The first query returns one row with two columns with values 1 and 'abc'. The second one returns a row with one column of pseudo-type record with value (1, 'abc').

Your query tries to create a table with one column of pseudo-type record. This is impossible and should end with

ERROR: column "row" has pseudo-type record
SQL state: 42P16

Just remove brackets from your query. As a_horse stated, [25P02] ERROR does not apply to the query in question.

Btw, my advice: never use keywords as table/column names.

Upvotes: 4

Related Questions