In psql, why do some commands have no effect?

Question

Sometimes my commands in psql seem to be having no effect. Any idea why?

The below is the list of all tables in the database library_development:

library_development=> \d

               List of relations
 Schema |       Name        | Type  |  Owner
--------+-------------------+-------+----------
 public | Pavan             | table | postgres
 public | schema_migrations | table | sai
(2 rows)

After this I dropped the table Pavan using:

library_development-> drop table Pavan

But the Table isn't dropped and its shows as shown:

library_development=> \d
               List of relations
 Schema |       Name        | Type  |  Owner
--------+-------------------+-------+----------
 public | Pavan             | table | postgres
 public | schema_migrations | table | sai
(2 rows)

Also:

1. I am using PostgreSQL in Windows. Is there any command to clear the console (Like cl scr present in Oracle)?

2. Is there any concept of a "commit" I need to perform in Postgresql when working with DML scripts?

Answer 1

Statements end with semicolons.

In psql, pressing enter without a semicolon continues the statement onto the next line, adding what you wrote to the query buffer rather than executing it. You will notice that the prompt changes from dbname=> to dbname-> to indicate that you're on a continuation line.

regress=> DROP TABLE sometable
regress-> \r
Query buffer reset (cleared).
regress=> DROP TABLE sometable;
ERROR:  table "sometable" does not exist
regress=> 

Notice how after I press enter without a semicolon, the prompt changes to regress-# and no action is taken. There is no table sometable, so if the statement had run an error would be reported.

Next, see the use of \r on the next line? That clears the query buffer. Notice that the prompt changes back to regress=# when the buffer is cleared, as there's no partial statement buffered anymore.

This shows how statements can be split across lines:

regress=> DROP TABLE
regress-> sometable
regress-> ;
ERROR:  table "sometable" does not exist

The confusing thing is that psql backslash commands like \d are newline-terminated, not semicolon terminated, so they do run when you press enter. That's handy when you want to (say) view a table definition while writing a statement, but it's a bit confusing for newcomers.

As for your additional questions:

1. If there's a "clear screen" command in psql for Windows I haven't found it yet. On Linux I just use control-L, same as any other readline-using program. In Windows \! cls will work.

2. DDL in PostgreSQL is transactional. You can BEGIN a transaction, issue some DDL, and COMMIT the transaction to have it take effect. If you don't do your DDL in an explicit transaction then it takes effect immediately.

Answer 2

I just encountered something similar, but the cause was different.

I was trying to use the dropdb command and what I was seeing was like my command having no effect (change of prompt means my command is in the buffer, but that's not the issue here):

postgres=# dropdb databasename
postgres-#

Commands like dropdb should be entered OUTSIDE psql. What i had been doing was:

$ psql postgres
postgres=# dropdb databasename

Notice the first command is in Bash and fires psql, and the second is sent within psql.

The correct way is to send the command directly in bash (the console):

$ dropdb databasename