why do I require RESTRICT wildcard to drop schema in PostgreSQL

Question

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

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
– Craig Ringer, Commented May 9, 2013 at 2: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
– jigzat, Commented May 9, 2013 at 16:54
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
– Craig Ringer, Commented May 10, 2013 at 0:49
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
– Craig Ringer, Commented May 10, 2013 at 1:52
you are right, I feel ashamed but I remember that I use them. Maybe the bug was related to Mac OS terminal. — jigzat
– jigzat, Commented May 10, 2013 at 20:07

Chris Travers · Accepted Answer · 2013-06-05 14:33:40Z

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.

Collectives™ on Stack Overflow

why do I require RESTRICT wildcard to drop schema in PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related