PostgreSQL is an enterprise class, open-source, object-relational database management system (ORDBMS) available for all major platforms including Linux, UNIX, Windows and OS X. Please mention your exact PostgreSQL version when asking questions.

learn more… | top users | synonyms (1)

0
votes
1answer
34 views

Setting PostgreSQL data_directory in postgresql.conf not working

I'm attempting to configure PostgreSQL 9.3 on a Centos 6.6 box to keep the configuration files (postgresql.conf, pg_hba.conf, pg_ident.conf) in a different directory than the data directory. According ...
0
votes
1answer
17 views

Is it possible to backup my ubuntu postgresql database by copying the /var/lib/postgresql/9.1/main file and pasting it back in?

A developer working on my server has fiddled with some settings and knocked my postgresql database offline. I've been messing with it for an hour and am considering just reinstalling postgresql fresh ...
0
votes
1answer
40 views

Select rows from table where each row refers to a different schema

I have customer table in public schema. And I have a schema named after each customer. Each customer schema contains different tables like portals, plugin, ... When I want to select portals used by ...
0
votes
1answer
21 views

Is there any way to get the optimization time in PostgreSQL? [duplicate]

I'd like to see how long it takes to optimize a query. Does PostgreSQL support this feature?
0
votes
1answer
25 views

How to convert time without time zone to timestamp without time zone in PostgreSQL?

I received this error when trying to alter a column of type time to type timestamp: PG::CannotCoerce: ERROR: cannot cast type time without time zone to timestamp without time zone It's not ...
0
votes
1answer
26 views

Get statistics of slow running queries

My database application runs many different queries. I have set log_min_duration_statement to 1000. But queries logged there are not always slow, most times they just take a few ms. Is it possible to ...
0
votes
1answer
29 views

PostgreSQL utilize data from SELECT in BEGIN or use WITH?

I went through the documentation for 9.3 and did not find anything suggesting that I can or can not use data from a SELECT query within a BEGIN query. Is it possible or will I have to resort to a WITH ...
0
votes
1answer
28 views

Connect to remote EC2 Postgresql database

I want to setup a connection between a webserver and a PostgreSQL 9.3 server. Both are hosted on Amazon EC2 servers and are part of the same security group. I can't get the connection working. The ...
0
votes
1answer
42 views

Is it possible to make database owner read only?

The database owner can revoke certain privileges from themselves such as INSERT but is it possible to revoke further privileges such as DROP for read-only operation?
0
votes
1answer
29 views

How to know when Postgresql password is changed

My question is how to know the date when a postgresql user password is changed. Is it possible? I know postgresql has account expiry feature, but I'm asked to check if users have changed their ...
0
votes
1answer
23 views

Querying sums of grouped consecutive rows in PostgreSQL 9

I have data about people traveling in different countries like this: country | begintimestamp | distance Germany | 2015-01-01 00:00:00 | 100 Germany | 2015-01-01 01:12:13 | 30 France | ...
0
votes
1answer
36 views

Get TEXT value of a CLOB OID in Postgresql

I have a database table that looks like: create table answers( id int not null, question_id int not null, answer text null ) This table was originally build by Hibernate using the @Lob ...
0
votes
1answer
45 views

Amazon RDS non superuser create function in C

I am asked to restore a Rails application to a postgresql RDS instance. There are many functions in C but looks like only superuser can create functions in C and I could not get superuser access on ...
0
votes
3answers
92 views

Getting around the constraint “column must appear in the GROUP BY clause or be used in an aggregate function”

I'm using Postgres, which enforces the constraint that all columns in a SELECT...GROUP BY must appear in the GROUP BY clause or be used in an aggregate function. Lets say I'm modelling peoples' cars, ...
0
votes
1answer
28 views

NOT IN statement or what to use in this case?

I need to delete each row in fabricante_producto_solicitud (fps from now on) table if fps.id has not relation (NOT IN) fabricante_modelo_marca_producto (fmmp from now on). Since my input will be an ...
0
votes
2answers
57 views

PostgreSQL: Use column with hash based on other columns as unique index?

My previous problem is still not fixed: We cannot set a unique index because NULL values are allowed... we use this table in our database: CREATE TABLE offer ( offer_id serial PRIMARY KEY , ...
0
votes
1answer
27 views

how to query multiple counts on related tables

For each Forum, there are many Posts, and for each Post, there are many Comments I'm trying to query the total number of Posts and Comments for each Forum So I'm looking to have an output as such: ...
0
votes
1answer
68 views

How to make a single row result query to return multiple rows

I have the following WITH SQL Query which does select a set of filling stations along a route within the distance of 1000m. To have the measurement in meter I cast the geometries to geography. I also ...
0
votes
1answer
87 views

PostgreSQL and MONEY data type for currency values

For a project, I created a table with a column price MONEY NOT NULL column. And I thought it would handle decimals properly, unlike a floating number (i.e. IEEE rounding issues), but I end up having ...
0
votes
2answers
51 views

What indexes should one use to optimize a PostgreSQL query with a JOIN depth of 2?

Disclaimer: I am relatively new to PostgreSQL. I'm wondering how to optimize a query that does 2 INNER JOINs. My scenario is fairly simple: Select Posts with a photo (Posts.photo IS NOT NULL) and a ...
0
votes
1answer
63 views

Free universe tool monitoring all database [closed]

Is there any free tool to monitor most popular databases like Oracle, MySQL, MSSQL, PostgreSQL. I want to monitor the real time performance of all the above mentioned databases with a single tool like ...
0
votes
1answer
60 views

PGpool Promote to master and Slaves wont replicate on failure

Sorry for the copy paste from stackoverflow, but this is probably a better place to post this. Version pgpool-II-pg93-3.4.0-3pgdg.rhel6.x86_64.rpm I’ve seen some chat online about this from a ...
0
votes
2answers
53 views

Can postgresql WAL be regenerated or resent to the standby server?

Does anyone know if there's a way to resend or regenerate a WAL file that was missed by the standby server? During my testing I've gotten into a situation where the main server fails to send a file or ...
0
votes
1answer
450 views

Postgres incomplete startup packet error

I am attempting to install Postgres 9.3 on Ubuntu 14.04 and I am getting an irksome error on startup. Here's what I'm seeing: $ sudo service postgresql restart * Restarting PostgreSQL 9.3 database ...
0
votes
1answer
17 views

Selecting one variant out of different similar results

I have a table that looks as follows: Book Format Contents A html ... A markdown ... B html ... C markdown ... I want to return only 1 version of a book. ...
0
votes
1answer
34 views

Postgres Table Ownership

I'm new to PostgreSQL and I'm trying to change the table owner for a bunch of tables. I was able to change the ownership by logging in a the postgres user and executing alter table owner to user1, but ...
0
votes
1answer
25 views

How can I aggregate multiple results from another table using something like BOOL_AND?

I'm using PostgreSQL as my database. CREATE TABLE users ( id SERIAL NOT NULL, name TEXT NOT NULL, PRIMARY KEY (id), UNIQUE (name) ); CREATE TABLE groups ( id ...
0
votes
2answers
100 views

insert inserted id to another table

Here's the scenario: create table a ( id serial primary key, val text ); create table b ( id serial primary key, a_id integer references a(id) ); create rule a_inserted as on insert to a do ...
0
votes
1answer
34 views

How can I create a bool column for a view based on the result of a statement?

I'm using PostgreSQL as my database. CREATE TABLE groups ( id SERIAL NOT NULL, name TEXT NOT NULL, PRIMARY KEY (id), UNIQUE (name) ); CREATE TABLE users ( id ...
0
votes
1answer
42 views

Can I use `on delete set null` on a compound foreign key which contains a primary key column?

The docs say: Restricting and cascading deletes are the two most common options. RESTRICT prevents deletion of a referenced row. NO ACTION means that if any referencing rows still exist when the ...
0
votes
2answers
66 views

Alter table statement waiting (for hours, on dev machine), but no locks shown

I have been trying to issue a simple: ALTER TABLE tablename ADD COLUMN id_col character varying (30) type statement on a Postgres 9.1.13 build on Debian. The application is still in private beta, so ...
0
votes
1answer
42 views

Moving Local PostgreSQL 8.4.20 Database to Amazon EC2 or RDS

I'm running PostgresSQL 8.4.20 on a local server and plan to migrate the data to either Amazon EC2 or RDS. I have working knowledge of databases and need advice from experts. I've been researching ...
0
votes
1answer
21 views

postgresql unable to login

I created a user named 'client', witch refuses to connect. Here's my client: CREATE ROLE client LOGIN ENCRYPTED PASSWORD 'md5d5bc32b75da65fe60067f501a4bb6665' NOSUPERUSER INHERIT NOCREATEDB ...
0
votes
1answer
56 views

how are passwords encoded in pg_shadow

How are passwords encoded in column passwd of pg_shadow? Doing a: select passwd from pg_shadow; I see values like the following: md52c92c858aa1fa12144f1dcbd4ca9c3a0 ... which, doesn't seem to ...
0
votes
1answer
72 views

Database Normal Form (financial statement)

I would like some advice on how to design the database for financial statement data which objectively addresses the tradeoffs between usability, performance, maintenance and adherence to normal form. ...
0
votes
1answer
27 views

What determines if a bytea has 1 or 4 extra bytes?

When inserting a BYTEA string in PostgreSQL the documentation says that such a string will occupy 1 or 4 bytes plus byte string length. The question that I can't seem to find the answer to is what ...
0
votes
1answer
17 views

benefit of using [md5] over [trust] authentication for local Unix sockets

I am following a PostgreSQL installation guide that advises (subsequent to installing the database for the first time), to setup a password for the postgres user: alter role postgres password ...
0
votes
1answer
47 views

pgbench-tools failed authentication

I'm trying to configure pgbench-tools and went to run it per the README and got this error -bash-4.2$ ./newset 'Initial Config' psql: FATAL: Ident authentication failed for user "postgres" psql: ...
0
votes
1answer
346 views

Error: /var/lib/postgresql/9.3/main is not accessible or does not exist

I have a PostgreSQL db that was running on my server. I tried to do a db_dump and got a weird error message. I eventually thought I would just restart PostgreSQL. However its now stopped and won't ...
0
votes
1answer
201 views

why pg_restore ignores --create ? Error: failed: FATAL: database “new_db” does not exist

I am trying to run following command: sshpass -p "pass" ssh x@1.2.3.4 "pg_dump -Fc -U foo some_db" | pg_restore --create --dbname=new_db I get: failed: FATAL: database "new_db" does not exist
0
votes
1answer
170 views

Query optimization for multiple joins postgres

ER Diagram Brief description The database to be used is postgres. The database might contain upto 2 million AccountHolder records. Since the AccountHolder table is accessed and updated frequently ...
0
votes
1answer
28 views

multi-column FKs with NULL values

I understand that in PostgreSQL if there is a multi-column FK from table B to table A and one or more of the FK columns in table B are nullable then the FK is still considered as "honored" for the ...
0
votes
1answer
147 views

Postgres JSON field for many to many relationships

I'm designing a schema that has a many-to-many relationship as such: A lead can have several stages and each stage can have several leads. I currently have three tables, with one of them as being a ...
0
votes
1answer
43 views

modeling data dimensions as columns - some dimensions are highly variable while others have low variability

Please excuse my question if it is a bad one - I am not a DBA... I would like to model the following data: Let's say that I have one column that is the row id as generated by a sequence. Let's also ...
0
votes
1answer
97 views

Order specific columns in json_agg

I want to sort the rows inside the json_agg function. I found this: PostgreSQL : select columns inside json_agg Which gave me a solution to selecting specific columns for json_agg, but now I want to ...
0
votes
1answer
112 views

PostgreSQL data directory/files portability

I have lost my PostgreSQL 9.1 installation on my Ubuntu machine by update to Ubuntu 14.10. Now I have no (reasonably easy) way to export the data from my PostgreSQL 9.1 database. Is it possible to ...
0
votes
1answer
17 views

Postgres Grouping of Like Results

Our database is running on Postgres 9.3. We have a table called notifications. Notifications contains id, type, triggering id, source id, and some other information. What I am trying to do is select ...
0
votes
1answer
71 views

Trying to map postal codes to their respective cities, but running into some issues

The Problem I am creating a database of Countries, Regions, Cities and Postal Codes. Here's an example to give an idea of the database structure as it is now: Now, let's take Tuskegee University ...
0
votes
2answers
44 views

Postgresql single base on multiple HDD partitions

Imagine a situation: I got a Postgres database working on single server on one of HDD partitions (partition size 100GB) Database grows up to 100GB taking whole free place on the partition Database ...
0
votes
1answer
36 views

Insert any type of text in a column

How can I insert any type of text into a column of type text? At the moment I am using this: $$description$$ But a lot of my descriptions contain $ and other signs, so the insert will fail. Do I ...