2

My end goal is complete a Postgresql function that will need to use a temporary table. Trying to create the temporary table itself isn't working because of a syntax error. I'm pretty sure I have the syntax right so can someone tell me what I'm doing wrong here?

In PGAdmin III, I open a query window and enter the following:

create temporary table tmp_parts
(
  id serial not null,
  part_number character varying(255) not null,
  max_price numeric(19,2) not null
);

When I run the Explain Query feature to test the syntax, I get the following error:

ERROR:  syntax error at or near "serial"
LINE 3:   id serial not null,
             ^
********** Error **********

ERROR: syntax error at or near "serial"
SQL state: 42601
Character: 98

What am I doing wrong?

aarona
  • 35,986
  • 41
  • 138
  • 186
  • 1
    Your query runs fine when I run it in Postgres 9.3. – Gordon Linoff Nov 20 '15 at 12:59
  • 1
    @DJTripleThreat If you want to "test the syntax" in other words execute the query without acctually modification of database, but in the same time be alerted if syntax is good or wrong, you could do within transaction and rollback it. ie.: `START TRANSACTION; /*then your query*/ ROLLBACK;` – Gabriel's Messanger Nov 20 '15 at 13:44
  • @Gabriel'sMessanger thanks! I figured that out a couple minutes ago. That is very useful. – aarona Nov 20 '15 at 13:47

2 Answers2

4

You cannot use create table in explain. Only the following is allowed:

Any SELECT, INSERT, UPDATE, DELETE, VALUES, EXECUTE, DECLARE, or CREATE TABLE AS statement, whose execution plan you wish to see.

stas.yaranov
  • 1,797
  • 10
  • 17
  • wow what a bummer! I'd like to check that my code isn't going to do something wonky before I commit it as a function. Thats a little disheartening. Thank you for your answer. – aarona Nov 20 '15 at 13:13
  • The reason is that there is no execution plan associated with creating a table, unless there is a DML query component to it, as there is for `create table ... AS ...`. So even if it was valid syntax, there would be no plan to explain. – David Aldridge Nov 20 '15 at 13:53
0

serial is not actually a type in postgres, it is a shorthand for

id integer NOT NULL DEFAULT nextval('tmp_parts_seq')

My 'guess' is that either it does not like the duplication of NOT NULL or (more likely?) that you can't have a sequence of temporary numbers because they are temporary.

It is simpler than we imagined:

This previous question shows the syntax, you need to remove the brackets and finish the statement with a semi-colon.

Community
  • 1
  • 1
fatherdamo
  • 165
  • 1
  • 2
  • 13
  • That was what I thought also but simply changing `serial` to `integer` gave me similar results. – aarona Nov 20 '15 at 13:09