Tagged Questions

4
votes
2answers
676 views

Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?

In connection with this answer I stumbled upon a phenomenon I cannot explain. Version: PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 64-bit Consider ...
1
vote
2answers
277 views

PostgreSQL crosstab/pivot problems

I have a prefs table, and here are the relevant columns: mydb=> SELECT pref_id, pref_name, pref_value FROM prefs; pref_id | pref_name | pref_value ---------+--------------+---------------- ...
3
votes
2answers
3k views

Rails: rake db:create:all fails to connect to PostgreSQL database

I am trying to create a Rails app that uses PostgreSQL. Here is a description of what I did. PostgreSQL setup: I installed PostgreSQL 9.1.3 via the ppa:pitti/postgresql maintained by Martin Pitt. ...
6
votes
3answers
3k views

How do I import modules or install extensions in Postgres 9.1?

Firstly, if you're not using 9.1+, please refer to this question. How do install install an extension to Postgresql 9.1?
4
votes
1answer
617 views

How to create a new database with the hstore extension already installed?

Recently I went into trouble trying to use hstore with Django. I installed hstore this way: $ sudo -u postgres psql postgres=# CREATE EXTENSION hstore; WARNING: => is deprecated as an operator ...
9
votes
3answers
4k views

Mountain Lion Postgres could not connect

After my update to mountain lion my postgres doest work. It is still running but my applications cant connect to it anymore. $ ps aux | grep postgres postgres 204 0.0 0.0 2446960 836 ...
5
votes
3answers
649 views

Determining the OID of a table in Postgres 9.1?

Does anyone know how to find the OID of a table in Postgres 9.1? I am writing an update script that needs to test for the existence of a column in a table before it tries to create the column. This ...
4
votes
1answer
1k views

Query a parameter (postgresql.conf setting) like “max_connections”

Does anyone know if it's even possible (and how, if yes) to query a database server setting in PostgreSQL (9.1)? I need to view the max_connections (maximum number of open db connections) setting.
3
votes
1answer
129 views

How to tell if record has changed in Postgres

I have a bit of an "upsert" type of question... but, I want to throw it out there because it's a little bit different than any that I've read on stackoverflow. Basic problem. I'm working on ...
3
votes
1answer
84 views

Find cluster given node in PostgreSQL

I am representing a graph in Postgres 9.1 (happens to be bidirectional and cyclic): CREATE TABLE nodes ( id SERIAL PRIMARY KEY, name text ); CREATE TABLE edges ( id ...
2
votes
3answers
221 views

Postgres - Is this the right way to create a partial index on a boolean column?

I have the following table: CREATE TABLE recipemetadata ( --Lots of columns diet_glutenfree boolean NOT NULL, ); Most every row will be set to FALSE unless someone comes up with some crazy new ...
0
votes
3answers
149 views

PostgreSQL next value of the sequences?

I am using PostgreSQL for my Codeigniter website. I am using grocery crud for add, edit and delete operations. While doing an edit or add, I want to rename an uploaded file dynamically based on the id ...
0
votes
2answers
95 views

Allow only postgres user list roles

How to forbid non superusers to see other users in postgresql server? ex. If currently logged in user is not a superuser then the result from SELECT * from pg_roles; or \du should be ...
4
votes
3answers
105 views

Postgresql: Calculate rank by number of true OR clauses

I need to order/rank the results of a PostgreSQL query by the number of OR-clauses that are true. For example, given a query like SELECT * FROM mytable WHERE cond1 OR cond2 OR cond3 ORDER BY rank ...
3
votes
2answers
1k views

PL/pgSQL checking if a row exists - SELECT INTO boolean

I'm writing a function in PL/pgSQL, and I'm looking for the simplest way to check if a row exists. Right now I'm SELECTing an integer into a boolean, which doesn't really work. I'm not experienced ...

1 2 3
15 30 50 per page