4

In Postgres, PSQL one can execute commands from file with the \i <path> command.

I did this by creating a DATABASE, a SCHEMA, some TABLES, and a VIEW with this little file:

DROP DATABASE IF EXISTS databaseName;
CREATE DATABASE databaseName ;

CREATE SCHEMA IF NOT EXISTS databaseName_schema;

CREATE TABLE IF NOT EXISTS databaseName_schema.tableName (
   id SERIAL PRIMARY KEY,
   name VARCHAR(80),
   surename VARCHAR(80),
);


CREATE TABLE IF NOT EXISTS databaseName_schema.tableMovies (
    id SERIAL PRIMARY KEY,
    priority SERIAL,
    movieName VARCHAR(40)
);
   
create or replace view versionViewTest as select version();

I then checked with \l and the database "databaseName" does exist. If I then

DROP DATABASE databaseName;

and recheck \l the database is gone! However when I then execute the same script again with

\i <path>

it says:

...schema already exists, skipping CREATE SCHEMA ...relation already exists, skipping CREATE TABLE

I already spent some time reading about schemas, relations, and Postgres and can not tell what I am missing.

My expectation would be: If I drop a database it's containing components also are dropped but somehow psql does not drop them but reuses them in case of the creation of a database with the exact same name in the future. Why would that be useful? And how would I drop the database with all its content?

PS: Any lecture on that topic is welcomed as well as a direct answer.

Promise Preston
  • 24,334
  • 12
  • 145
  • 143
Simeon
  • 748
  • 1
  • 9
  • 26
  • 2
    you miss connect to newly created database – Vao Tsun Apr 12 '17 at 09:23
  • `create database` copies `template1` maybe you have those schemas and tables in the template database? –  Apr 12 '17 at 09:24
  • I believe Simeon creates schema in database he is connected to, so drop/create database become irrelevant to next statements – Vao Tsun Apr 12 '17 at 09:26
  • @VaoTsun I was not actively connected to any database at the time I executed the script with the missing connect statement. Does your comment imply that I am always connected to a default Database in such a situation? – Simeon Apr 12 '17 at 09:40
  • yes. if you would be connected to the db you drop, it would not allow you to. also psql starts "terminal" only after it connects to some db – Vao Tsun Apr 12 '17 at 09:41
  • It could not be that I was connected to the old db I dropped for it was gone and did no longer show up when listing \l all databases – Simeon Apr 12 '17 at 09:43
  • no, you must have been connected to postgres or template(0)... – Vao Tsun Apr 12 '17 at 12:38

1 Answers1

4

you forgot to connect to a db:

DROP DATABASE IF EXISTS databaseName;
CREATE DATABASE databaseName ;
\c databaseName 
CREATE SCHEMA IF NOT EXISTS databaseName_schema;
...
Vao Tsun
  • 47,234
  • 13
  • 100
  • 132
  • Thank you very much! I will accept this as the correct answer as soon as the timer allows me to. Additional clarification: Would it be concidered "dirty" to name the script ".sql" after adding the psql-specific command '\c databaseName'? – Simeon Apr 12 '17 at 09:38
  • it's common practice, and yet psql meta-commands are not SQL statements by all means. I believe it's a matter of choice. – Vao Tsun Apr 12 '17 at 09:43