0

I have very little experience in PostgreSQL. I have been reading tutorials and documentation, and in order to create or drop a schema all of them say that I just need to execute:

CREATE SCHEMA myschema; 
DROP SCHEMA myschema;

but it doesn't work. I finally stumble upon that I have to use:

CREATE SCHEMA myschema AUTHORIZATION pgsql;
DROP SCHEMA myschema RESTRICT;

Do I have a corrupted installation or maybe I added some feature that I shouldn't?

UPDATE: If I use pgAdmin from Mac OS X it works. I don't receive any error or alert of any kind. Basically I just log into pgsql account like this:

su pgsql

then

psql mydatabase
Kara
  • 6,115
  • 16
  • 50
  • 57
jigzat
  • 3,498
  • 1
  • 21
  • 23
  • 4
    Weird. How did you create `myuser` and what rights did you grant it? Is this really PostgreSQL, or is it some PostgreSQL variant like Redshift/Greenplum/etc? I think you might just be running into issues with ownership, but since you've not shown the real commands you ran (given the typos), the exact error messages, or any info about the user and its rights it's hard to say. – Craig Ringer May 09 '13 at 02:26
  • Thank you for the comment, my user is the pgsql user that, I guess, was created during installation, and yes it is the real postgresql port from freebsd. And here comes another weird thing, if I use pgAdmin3 from Mac OS X it works flawlessly but no in the psql console. – jigzat May 09 '13 at 16:54
  • 1
    You should probably turn on `log_statement = 'all'` and see what PgAdmin3 is doing. Still very, very weird. By "the pgsql user" do you mean the user `postgres`? There isn't usually a `pgsql` user, unless the FreeBSD port is particularly different to the usual installs. – Craig Ringer May 10 '13 at 00:49
  • This is so weird, yes I was surprised by it but it seems freeBSD postgres user is called pgsql, I enabled the log and it says CREATE SCHEMA myschema; nothing different but after enabling the log on pgAdmin it is now working in the psql console :B. Before that there was no response from the console, now it says CREATE SCHEMA and DROP SCHEMA after executing the corresponding command. Thank you so much for bearing up with me. Let's just say that you used The Force to fix it. – jigzat May 10 '13 at 01:47
  • 1
    hang on, did you just say *no response* from the console? Not an error? Bet you just forgot to put the semicolons in when you entered the commands in the console. – Craig Ringer May 10 '13 at 01:52
  • No no, I meant that after executing CREATE SCHEMA my schema; or DROP SCHEMA myschema; the console outputs the message CREATE SCHEMA or DROP SCHEMA. Before all this I use to get nothing, there was no error nor response at all. – jigzat May 10 '13 at 02:44
  • Yep, sounds like you probably forgot the semicolons when you were running the commands in psql. Try it and see what I mean. – Craig Ringer May 10 '13 at 03:21
  • 1
    you are right, I feel ashamed but I remember that I use them. Maybe the bug was related to Mac OS terminal. – jigzat May 10 '13 at 20:07

1 Answers1

1

There are a number of things that can cause commands not to be properly terminated. I believe this answer will help others as well. This may seem too localized but I have heard of it happening to enough beginners it is worth writing up.

In general utility statements like CREATE SCHEMA or DROP SCHEMA will return something in psql, whether an error or a description of what was done. If that doesn't happen something isn't right. The first thing to do is look at the prompt.

The prompt in PostgreSQL is in the form of: [dbname][line-status][is-superuser]

So for a new line, database mydb, nonsuperuser, the prompt looks like: mydb=>

If I am superuser I get:

mydb=#

Now the key character to look at is the one where the = is at. Various symbols have special meanings:

  • = New line (ready for new input)
  • - Continuation from previous line. Did you forget the semicolon?
  • ( In a parenthesis. Your parentheses are unbalanced.
  • $ In a dollar quote block. These are like $quote$string literal$quote$
  • ' Inside a single quoted string literal block
  • " inside a double-quoted identifier

There may be some others but these are the most common. Usually aside from = and -, they are pretty self-explanatory.

Chris Travers
  • 25,424
  • 6
  • 65
  • 182