1

I'm trying to create a table in PostgreSQL, but it returns an error. This is my statement:

CREATE TABLE sportschema.zaal(
    zaalnr serial PRIMARY KEY,
    naam varchar(50),
    adres varchar(50),
    telefoonnummer varchar(50)
);

If I execute this query I get no response:

sportdb(# CREATE TABLE sportschema.zaal(zaalnr serial PRIMARY KEY, naam varchar(50), adres varchar(50), telefoonnummer varchar(50));

Anyone knows what is wrong about this query?

after trying a several times after reconnecting to the db it was suddenly working:

sportdb=# CREATE TABLE sportschema.zaal(zaalnr serial PRIMARY KEY, naam varchar(50), adres varchar(50), telefoonnummer varchar(50));
CREATE TABLE
sportdb=#

Sample from the terminal here:

portdb=# DROP TABLE sportschema.zaal;
    DROP TABLE
    sportdb=# CREATE TABLE sportschema.zaal(zaalnr serial PRIMARY KEY, naam varchar(50), adres varchar(50), telefoonnummer varchar(50);
    sportdb(# 
    sportdb(# 
//Inserted wrong query
    sportdb(# CREATE TABLE sportschema.zaal(zaalnr serial PRIMARY KEY, naam varchar(50), adres varchar(50), telefoonnummer varchar(50);
//doing buggy after a wrong query
    sportdb(# CREATE TABLE sportschema.zaal(zaalnr serial PRIMARY KEY, naam varchar(50), adres varchar(50), telefoonnummer varchar(50));
    sportdb(# CREATE TABLE sportschema.zaal(zaalnr serial PRIMARY KEY, naam varchar(50), adres varchar(50), telefoonnummer varchar(50));
    sportdb(# SELECT * FORM sportschema.zaal;
    sportdb(# SELECT * FROM sportschema.zaal;
    sportdb(# SELECT * FROM sportschema.zaal;
    sportdb-# \q
    postgres@bro:/home/foo$ psql -V
    psql (PostgreSQL) 9.3.10
    postgres=# \c sportdb
    sportdb=# SELECT * FROM sportschema.zaal;
    ERROR:  relation "sportschema.zaal" does not exist
    LINE 1: SELECT * FROM sportschema.zaal;
                          ^
    sportdb=# SELECT * FROM sportschema.zaal;
    ERROR:  relation "sportschema.zaal" does not exist
    LINE 1: SELECT * FROM sportschema.zaal;
                          ^
    sportdb=# CREATE TABLE sportschema.zaal(zaalnr serial PRIMARY KEY, naam varchar(50), adres varchar(50), telefoonnummer varchar(50));
    CREATE TABLE
    sportdb=# SELECT * FROM sportschema.zaal;
     zaalnr | naam | adres | telefoonnummer 
    --------+------+-------+----------------
    (0 rows)

When I execute a wrong query, my sql client is acting strange when I'm trying to execute the next query. Often it gives me the response of the query before, and now and then it just returns nothing. Anyone knows how to solve postgresql from acting strange after inserting a wrong query?

Gabriel's Messanger
  • 3,213
  • 17
  • 31
Melvin
  • 77
  • 1
  • 10
  • Check this `select * from sportschema.zaal`, BTW _If I execute this query I get no response:_ what you're expecting ? – Vivek S. Oct 21 '15 at 11:46
  • Already did, after trying again, this is the response: sportdb(# SELECT * FROM sportschema.zaal; sportdb(# – Melvin Oct 21 '15 at 11:47
  • The syntax for the `create table` is correct. Are you sure the schema exists? Or that the table does not already exist? – Gordon Linoff Oct 21 '15 at 11:47
  • psql (PostgreSQL) 9.3.10 on Ubuntu 14.04 – Melvin Oct 21 '15 at 11:50
  • *"but it returns an error."* What error do you get? – ypercubeᵀᴹ Oct 21 '15 at 11:50
  • Are you **sure** you have a `;` at the end? Sounds as if you are not terminating the statement properly and `psql` is simply waiting for you to complete the statement. –  Oct 21 '15 at 11:52
  • The point was, it didn't return an error, but when I did a SELECT statement for checking if the table exists it also returned nothing – Melvin Oct 21 '15 at 11:55
  • After reconnecting the database a few times it was suddenly working, I guess it was a strange postgresql bug – Melvin Oct 21 '15 at 11:55
  • 2
    I'm pretty sure it was not a "postgres bug" for such a basic functionality. I guess that you had some unclosed string literals or similar things. The prompt `sportdb(#` strongly indicates this. (as opposed to the `sportdb=#` prompt) –  Oct 21 '15 at 11:57
  • Sometimes after executing a wrong sql statement, my sql client is acting strange. I guess that was the case here – Melvin Oct 21 '15 at 12:02
  • 2
    It is obvious, the first create statement misses a `)` at rhe end. (in vi[m] hit `%` to find the matching parenthesis / brace/ bracket) – wildplasser Oct 21 '15 at 12:16
  • Nice name wildplasser :) I saw this, so I edited my query. When I executed the new query, my sql client was still acting strange, so I edited this post and the new question is: how do I solve this from happening again? – Melvin Oct 21 '15 at 12:18
  • 1
    @epicbro97 A lott of SQL-programmers adapt some style of indentation/ whitespace. That doesn't solve anything but it will cause syntax (errors) to be more visible to the eye. Aligning braces and commas (like in my answer) is one of them. Using an editor (and being comfortable with it) is another one. – wildplasser Oct 21 '15 at 12:25
  • I use gedit, with some plugins. Could paste this in the terminal with a \, but to lazy for that :P – Melvin Oct 21 '15 at 12:28
  • 1
    Note: if you save you .SQL file (and leave the editor) you can submit it via `psql -U my_username my_databasename < myfile.sql` – wildplasser Oct 21 '15 at 12:35

0 Answers0