Asked  7 Months ago    Answers:  5   Viewed   20 times

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?

 Answers

64

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.

Tuesday, June 1, 2021
 
Whakkee
answered 7 Months ago
36

There are several ways to authenticate to PostgreSQL. You may wish to investigate alternatives to password authentication at https://www.postgresql.org/docs/current/static/client-authentication.html.

To answer your question, there are a few ways provide a password for password-based authentication:

  1. The obvious way is via the password prompt
  2. Instead of that, you can...
  • provide the password in a pgpass file or

  • through the PGPASSWORD environment variable.

  • See these:

    • https://www.postgresql.org/docs/9.0/static/libpq-pgpass.html
    • https://www.postgresql.org/docs/9.0/interactive/libpq-envars.html
  1. Command-line There is no option to provide the password as a command line argument because that information is often available to all users, and therefore insecure. However, in Linux/Unix environments you can provide an environment variable for a single command like this:
PGPASSWORD=yourpass psql ...
Thursday, June 3, 2021
 
rblarsen
answered 6 Months ago
68

The error states that the psql utility can't find the socket to connect to your database server. Either you don't have the database service running in the background, or the socket is located elsewhere, or perhaps the pg_hba.conf needs to be fixed.

Step 1: Verify that the database is running

The command may vary depending on your operating system. But on most *ix systems the following would work, it will search for postgres among all running processes

ps -ef | grep postgres

On my system, mac osx, this spits out

501   408     1   0  2Jul15 ??         0:21.63 /usr/local/opt/postgresql/bin/postgres -D /usr/local/var/postgres -r /usr/local/var/postgres/server.log

The last column shows the command used to start the server, and the options.

You can look at all the options available to start the postgres server using the following.

man postgres

From there, you'd see that the options -D and -r are respectively the datadir & the logfilename.

Step 2: If the postgres service is running

Use find to search for the location of the socket, which should be somewhere in the /tmp

sudo find /tmp/ -name .s.PGSQL.5432

If postgres is running and accepting socket connections, the above should tell you the location of the socket. On my machine, it turned out to be:

/tmp/.s.PGSQL.5432

Then, try connecting via psql using this file's location explicitly, eg.

psql -h /tmp/ dbname

Step 3: If the service is running but you don't see a socket

If you can't find the socket, but see that the service is running, Verify that the pg_hba.conf file allows local sockets.

Browse to the datadir and you should find the pg_hba.conf file.

By default, near the bottom of the file you should see the following lines:

# "local" is for Unix domain socket connections only
local       all       all       trust

If you don't see it, you can modify the file, and restart the postgres service.

Friday, July 9, 2021
 
mopsyd
answered 5 Months ago
73

First of all, regardless of priorities, you can not make any assumptions about which parallel job finishes first.

Also, please read this article by Jeff Atwood about why using thread priorities is a bad idea.

http://www.codinghorror.com/blog/2006/08/thread-priorities-are-evil.html

In case of tl;dr just one quote: No matter how brilliant a programmer you may be, I can practically guarantee you won't be able to outsmart the programmers who wrote the scheduler in your operating system.

Monday, August 30, 2021
 
matthy
answered 3 Months ago
66

Welcome to Cocoa! :) I suspect this is happening as part of the new user interface preservation features in OS X Lion. (In fact, I just created a simple app with 3 text fields, and I see this behavior too.) Because windows automatically restore themselves, you will see a lot of this behavior happening automatically even if you didn't implement it. This is probably desirable — most applications will work this way, and the user will come to expect it.

However, if you really want to disable it, you can probably do so by subclassing NSWindow or perhaps NSTextField and overriding -encodeRestorableStateWithCoder:. But, I definitely recommend you leave the default behavior alone.


Edit with a little further information: the app state seems to be stored in ~/Library/Saved Application State/com.yourapp.savedState. There you can see a plist file with information about the windows. The other files don't seem easily readable, but they probably contain information about which field is first responder, etc.

Wednesday, October 13, 2021
 
Jim
answered 2 Months ago
Jim
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :  
Share