EzDevInfo.com

PostgreSQL "DESCRIBE TABLE"

How do you perform the equivalent of Oracle's DESCRIBE TABLE in PostgreSQL (using the psql command)?

How to drop a PostgreSQL database if there are active connections to it?

I need to write a script that will drop a PostgreSQL database. There may be a lot of connections to it, but the script should ignore that.

The standard DROP DATABASE db_name query doesn't work when there are open connections.

How can I solve the problem?

Creating a copy of a database in PostgreSQL

What's the correct way to copy entire database (its structure and data) to a new one in pgAdmin?

Copying PostgreSQL database to another server

I'm looking to copy a production PostgreSQL database to a development server. What's the quickest, easiest way to go about doing this?

Would you recommend PostgreSQL over MySQL? [closed]

We are currently working with JavaEE and MySQL 5 in our company, but we have some queries, especially delete queries that take > 10 min to execute. We are considering a switch to PostgreSQL.

What are the advantages of PostgreSQL over MySQL, if there are any? Do you have experience with both DBs and give me a roundup about whether this is a good idea or does this fully depend on the needs of our server?

Which version of PostgreSQL am I running?

I'm in a corporate environment (running Debian Linux) and didn't install it myself. I access the databases using Navicat or phpPgAdmin (if that helps). I also don't have shell access to the server running the database.

Save PL/pgSQL output from PostgreSQL to a CSV file

What is the easiest way to save PL/pgSQL output from a PostgreSQL database to a CSV file?

I'm using PostgreSQL 8.4 with pgAdmin III and PSQL plugin where I run queries from.

Drop all tables in postgresql?

How can I delete all tables in postgresql, working from the command line?

I don't want to drop the database itself - just all tables and all the data in them.

Thanks.

How to reset postgres' primary key sequence when it falls out of sync?

I ran into the problem that my primary key sequence is not in sync with my table rows.

That is, when I insert a new row I get a duplicate key error because the sequence implied in the serial datatype returns a number that already exists.

It seems to be caused by import/restores not maintaining the sequence properly.

PostgreSQL error: Fatal: role "username" does not exist

I'm setting up my PostgreSQL 9.1. I can't do anything with PostgreSQL: can't createdb, can't createuser; all operations return the error message

Fatal: role h9uest does not exist

h9uest is my account name, and I sudo apt-get install PostgreSQL 9.1 under this account.
Similar error persists for the root account.

Modify OWNER on all tables simultaneously in PostgreSQL

How do I modify the owner of all tables in a PostgreSQL database?

I tried ALTER TABLE * OWNER TO new_owner but it doesn't support the asterisk syntax.

How to exit from PostgreSQL command line utility: psql

What command or short key can I use to exit the PostgreSQL command line utility psql? Here is the gist of the answers:

Type the following to exit the PostgreSQL command line utility psql:

\q + Enter

Press the following key combinations for Windows, Mac or Linux:

ctrl+D

For more command options type:

\? + Enter

`pg_tblspc` missing after installation of latest version of OS X (Yosemite or El Capitan)

I use postgres from homebrew in my OS X, but when I reboot my system, sometimes the postgres doesn't start after the reboot, and so I manually tried to start it with postgres -D /usr/local/var/postgres, but then the error occurred with the following message: FATAL: could not open directory "pg_tblspc": No such file or directory.

The last time it occurred, I couldn't get it to the original state, so I decided to uninstall the whole postgres system and then re-installed it and created users, tables, datasets, etc... It was so disgusting, but it frequently occurs on my system, say once in a few months.

So why does it lose the pg_tblspc file frequently? And is there anything that I can do to avoid the loss of the file?

I haven't upgraded my homebrew and postgres to the latest version (i.e. I've been using the same version). Also, all the things that I did on the postgres database is delete the table and populate the new data every day. I haven't changed the user, password, etc...

EDIT (mbannert): I felt the need to add this, since the thread is the top hit on google for this issue and for many the symptom is different. Homebrewers likely will encounter this error message:

No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

So, if you just experienced this after the Yosemite upgrade you now you're covered for now reading this thread.

How to start postgreSQL server on mac os x?

FINAL UPDATE:

I forgot to run the initdb command.

< /FINAL UPDATE>

by running this command

ps auxwww | grep postgres

I see that postgres is not running

> ps auxwww | grep postgres
remcat          1789   0.0  0.0  2434892    480 s000  R+   11:28PM   0:00.00 grep postgres

this raises the question: How do I start the postgresql server?

update:

>pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start
server starting
sh: /usr/local/var/postgres/server.log: No such file or directory

update 2:

The touch was not successful so I did this instead:

> mkdir /usr/local/var/postgres
> vi /usr/local/var/postgres/server.log
> ls /usr/local/var/postgres/          
server.log

But when I try to start rails server, I still see this:

Is the server running on host "localhost" and accepting
TCP/IP connections on port 5432?

update 3:

> pg_ctl -D /usr/local/var/postgres status
pg_ctl: no server running

update 4:

I found that there WAS NO pg_hba.conf (only pg_hba.conf.sample) so I modified the sample and renamed it (to remover the .sample). Here are the contents:

 # IPv4 local connections:
 host    all             all             127.0.0.1/32           trust
 # IPv6 local connections:
 host    all             all             ::1/128                trust

but I don't understand this:

> pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start     
server starting
> pg_ctl -D /usr/local/var/postgres status                                     
pg_ctl: no server running

also:

sudo find / -name postgresql.conf
find: /dev/fd/3: Not a directory
find: /dev/fd/4: Not a directory

update 5:

sudo pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start
Password:
pg_ctl: cannot be run as root
Please log in (using, e.g., "su") as the (unprivileged) user that will own the server process.

update 6:

this seems odd:

> egrep 'listen|port' /usr/local/var/postgres/postgresql.conf
egrep: /usr/local/var/postgres/postgresql.conf: No such file or directory

though, I did do this:

>sudo find / -name "*postgresql.conf*"
find: /dev/fd/3: Not a directory
find: /dev/fd/4: Not a directory
/usr/local/Cellar/postgresql/9.0.4/share/postgresql/postgresql.conf.sample
/usr/share/postgresql/postgresql.conf.sample

so I did this:

egrep 'listen|port' /usr/local/Cellar/postgresql/9.0.4/share/postgresql/postgresql.conf.sample 
#listen_addresses = 'localhost'     # what IP address(es) to listen on;
#port = 5432                # (change requires restart)
                # supported by the operating system:
                #   %r = remote host and port

so I tried this:

> cp /usr/local/Cellar/postgresql/9.0.4/share/postgresql/postgresql.conf.sample /usr/local/Cellar/postgresql/9.0.4/share/postgresql/postgresql.conf        
> cp /usr/share/postgresql/postgresql.conf.sample /usr/share/postgresql/postgresql.conf 

still getting the same "Is the server running?" message.

Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY.

Specifically, if I've got a purchases table that looks like this:

SELECT * FROM purchases;

id | customer | total
---+----------+------
 1 | Joe      | 5
 2 | Sally    | 3
 3 | Joe      | 2
 4 | Sally    | 1

I'd like to query for the id of the largest purchase (total) made by each customer. Something like this:

SELECT FIRST(id), customer, FIRST(total)
FROM  purchases
GROUP BY customer
ORDER BY total DESC;

FIRST(id) | customer | FIRST(total)
----------+----------+-------------
        1 | Joe      | 5
        2 | Sally    | 3