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)

3
votes
3answers
96 views

How long to learn PostgreSQL?

I want to learn PostgreSQL and How to make the learning process efficient, but really don't know how long it would take even after googling my question. I used MySQL before and knew relational ...
0
votes
1answer
34 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
46 views

Prevent Primary Key increment when an INSERT statement fail in PostgreSQL

I have a simple table in my PostgreSQL database like this: CREATE TABLE person_type ( id serial NOT NULL, name character(55) NOT NULL, CONSTRAINT person_type_pkey PRIMARY KEY (id), CONSTRAINT ...
0
votes
1answer
34 views

PostgreSQL Role Configuration

I am using PostgreSQL 9.3. I am working with a friend of mine on a project on which both of us would need access to the same database and all of the permissions on every table. So I created a Role ...
0
votes
1answer
50 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. ...
1
vote
1answer
40 views

postgres materialized views and indexes to improve aggregate performance

I have a large table with a number of materialized views used for pre-calculating aggregates. New data is inserted only once a day so think of this as a data warehouse type system. Obviously when I ...
1
vote
2answers
58 views

Remove duplicates and connect relations to the first one

Guess I can use some help over here. I have split a products table into two tables, products and prices. This is the original table: products: +----+--------+-----------+-----------+-----------+ | ...
3
votes
1answer
78 views

PostgresSQL dynamic execute with argument values in array

I'm wondering if this is possible in Postgres: Best explained using a contrived example: create or replace function test_function(filter_param1 varchar default null ...
2
votes
0answers
50 views

ST_GeomFromGeoJSON() Causes postgres to crash

I'm running into a strange problem which I can't solve. The server crashes when executing some postgis related queries. After some debugging, using the examples provided by postgis, it appears the ...
3
votes
2answers
30 views

Do Postgres GIN indexes need to be rebuilt periodically?

Do they get bloated like btree indexes? If so how can the bloat be measured e.g. by an automated reindexing script. I am interested in improving query performance and saving space.
2
votes
1answer
33 views

SQL Statements truncated in log files

Does PostgreSQL cut statements if there is a subselect in it? We use log_min_duration_statement = 20ms to find statements which need a long time. But unfortunately the statements are truncated: ...
1
vote
1answer
39 views

How to design tables for airbnb style booking system

all. My English is not very good, but I will try to describe my question as clear as possible. I want to design a airbnb(https://www.airbnb.com) style booking system, this kind of booking system ...
2
votes
1answer
42 views

Marking duplicate records

I have a list of things, some of which are duplicates. create table things ( id int primary key, title text not null, author text not null, duplicate_of int null references things(id) ); ...
1
vote
1answer
51 views

How to restrict access to current user record and its rights in postgres

Postgres 9 database contains additional information about postgres users: create table public.user ( user char(10) primary key, -- CURRENT_USER in postgres name char(30) ); and user roles; ...
0
votes
1answer
23 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 ...
1
vote
1answer
74 views

How many rows will be locked by SELECT … ORDER BY xxx LIMIT 1 FOR UPDATE?

As was asked on stackoverflow but then for MySQL, I was wondering how this works in PostgreSQL. How many rows get locked when I do a "FOR UPDATE" in combination with a "LIMIT", a "ORDER BY" and some ...
0
votes
1answer
12 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 ...
1
vote
1answer
30 views

remove password requirement for user postgres

I understand that, upon installation, PostgreSQL has no password for its db root user (postgres): postgres=# select usename, passwd is null from pg_shadow; usename | ?column? ----------+---------- ...
0
votes
0answers
26 views

Error importing postgres DB dump into empty database

I need to import my psql dump into my fresh psql database. When I execute the following command, I get errors. psql -U user new_database < filename.sql Error I got: ERROR: out of memory DETAIL: ...
1
vote
1answer
33 views

Copying database to different hard disk

Total noob at database administration here, excuse my ignorance. I have a server running a Rails application that's using PostgreSQL. The database is saved on drive /z/, which seems to have filled ...
0
votes
1answer
34 views

How to create a tablespace on Windows 7 for Postgresql 9.2?

I try to run this simple command: CREATE TABLESPACE tb_rec OWNER postgres LOCATION 'd:/tablespaces'; However, the tablespace is not created and I got this message: ERREUR: n'a pas pu ...
0
votes
1answer
28 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: ...
1
vote
1answer
34 views

Why “SET LOCAL statement_timeout” does not work as expected with PostgreSQL functions?

My understanding is that PostgreSQL functions are executed similar to a transaction. However, when I tried to "SET LOCAL statement_timeout" within a function, it did not work. Here's how it works ...
0
votes
2answers
30 views

Using pg_dump and psql to transfer table with timestamp into another Db

I am using the following pg_dump and psql command lines through the terminal in order to transfer my table from one database to another. pg_dump -U postgres -t public.table1 database1 | psql -U ...
1
vote
1answer
33 views

How can prevent writes to a Postgres schema for a period of time?

I am moving a schema from one server to another using pg_dump + pg_restore + drop schema cascade. I need to know that no applications are writing to it (or at least that the writes will fail) during ...
0
votes
1answer
49 views

After upgrade from 9.1 to 9.3, commands still point to 9.1

Upgraded from 9.1 to 9.3 on my Ubuntu 12.04 server. Everything worked great but running commands like 'createdb' still runs them from the 9.1 path. /usr/lib/postgresql/9.1/bin/createdb: unrecognized ...
0
votes
1answer
52 views

copy data from public schema to another schema in postgresql

I'm creating a multi-tenant RoR app using PostgreSQL 9.3.5 running on linux (Ubuntu 14). I'd like to copy the data from my public schema to another schema. I tried: pg_dump -U my_username -h ...
1
vote
1answer
28 views

Change constraint on column based on value of another

Is it possible to change a constraint on a column in postgres based on the value of another column? E.g. (pseudocode): CREATE TABLE transactions( id SERIAL PRIMARY KEY, type TXN_TYPE NOT ...
0
votes
0answers
36 views

PostgreSQL queries slower than before?

I am using PostgreSQL 9.3 server on my production server. Everything had been working fine for the past couple of months. But since the past week or so, PostgreSQL seems to have becomes very slow with ...
0
votes
0answers
43 views

pgAgent Job's “last result” is continuously “running”

I am using PostgreSQL-9.3 on Ubuntu-14.04 and using "/usr/share/pgadmin3/pgagent.sql" I have configured my pgAdmin3 to use pgAgent. postgres is my maintenance Db and I have run the pgagent.sql content ...
1
vote
0answers
47 views

Multiple databases with medium tables or Single database with huge tables

I've developed a shop management software using Postgresql and JAVA. I would like to know which of the solutions below is the best one for my specific situation: Only one database with huge tables ...
2
votes
0answers
46 views

Cannot drop a Postgres table

I want to drop a few tables in my postgresql database. I am able to drop a few tables but there are 3 tables which I am unable to drop. I need to drop a few tables in my Postgres DB. I tried the ...
0
votes
0answers
28 views

Calculate Quantity On Hand By Location

How can I calculate quantity on hand by location and validate the proposed location quantities against inventory transaction? Currently, I do not track location. I calculate total QoH via the ...
0
votes
1answer
41 views

PostgreSQL import XML to empty database, automatically create columns

The server is Linux but the development machine is Windows so I'm using psql command line. I tried \i C:/Users/John/Desktop/Parts.xml which did not work so either \i doesn't work with XML or I'm ...
0
votes
0answers
33 views

Looking for a recipe to merge multiple rows to single row

I have a aggregation table that is written by multiple processors (around 20). Each row has a composite key - location-id, campaign-id,... and aggregate stats about it. I need to merge the ...
1
vote
1answer
37 views

Will switching from Concrete Table Inheritance to Single Table Inheritance improve the performance of my queries?

I currently have an application that allows people to view and share 10 different types of posts (ex SportsPost, WorldNewsPost, TechnologyPost, etc). All posts types are conceptually the same, but ...
4
votes
2answers
344 views

How to understand the PostgreSQL discrepancy between connection pool recommendation ((2 * n_cores) + n_disks) and support for 100s of connections?

From PostgreSQL docs: Number of Database Connections -> How to Find the Optimal Database Connection Pool Size for optimal throughput the number of active connections should be somewhere near ...
1
vote
2answers
86 views

Need help designing a database

We are designing a system which holds statistical parameters about a particular user. As the users use the system the statistics change and the updated statistics are stored in the database. So for a ...
1
vote
1answer
40 views

How to manipulate join order of PostgreSQL optimizer?

I am working on table join order optimization. While query processing, I want to get table join order generated by optimizer, and update it by using my own algorithm in PostgreSQL? How can I update ...
0
votes
0answers
36 views

How to enable implicit casts in PostgreSQL >=9.2?

Coming from this topic on SO a user suggest me to enable implicit casts in my PostgreSQL and I don't know how to do that. I also don't know if this is a good practice or if it's the solution to the ...
0
votes
1answer
30 views

Drop/Create database and create schemas all in the same script at PostgreSQL it's possible?

I'm working on a project where all the time I need to repeat this tasks: Drop database sis_db Create database sis_db Create database schemas by running a custom SQL file which contains this code: ...
0
votes
3answers
72 views

Get latest status of an item in the database

I have this table: ID|Item-FK |timestamp|status ============================= 1 | 123 | ... | OK 2 | 123 | ... | FAILED ... I want to query get all items which last status was FAILED. ...
1
vote
1answer
49 views

EXECUTE within function not using index?

I have an accounts table with ~200k rows and an index for these columns: account_type_id BIGINT , member_id BIGINT , external_id VARCHAR(64) CREATE INDEX account_full_qualifiers_idx ON ...
0
votes
1answer
34 views

Database Security - Prevent column writing?

I am using a Postgres db. I am concerned if someone gets in my DB, they could change the price of my products to $0 and download my products. Am I able to prevent writing / editing of that column ...
0
votes
1answer
46 views

Postgresql doesn't accept replication connection

Plain old streaming replication. PostgreSQL: 9.2.7 Windows 8.1 64 bit My primary and secondary clusters are on same windows machine. I have already done pg_start_backup() and everything, so both ...
0
votes
1answer
32 views

Does PostgreSQL have a variable character storage layer that optimizes storage space by automatic dedupe?

My application has a Product and a Purchased table. The Purchased table includes a full copy of the Product row at time of purchase, so that, even if a product name or description was changed, the ...
2
votes
0answers
39 views

How can I avoid breaking Postgres sync replication when building a large index?

We have pairs of Postgres 9.2 servers using synchronous replication. Sometimes rebuilding an index on a large table will break replication due to WAL segments not being kept around long enough. We are ...
0
votes
0answers
13 views

missing pgpool-walrecrunning.so in PostgreSQL-9.4-pgpool2

I am new to PostgreSQL and pgpool concept. I have installed PostgreSQL-9.4 in master/slave setup(in pc1/pc2), and replication setup is successful. Setup of pooling is failing, I am following this ...
0
votes
1answer
40 views

PostgreSQL roles versus users, grant permissions

I'm currently learning the differences between PostgreSQL and MySQL as I've got a new project and I also intend to migrate my existing software from MySQL to PostgreSQL. I've actually started creating ...
0
votes
0answers
23 views

Does Postgres shutdown gracefully when I restart my Mac?

When I restart my Mac OS X computer, I assume the daemon controlling Postgres (the "Postmaster" as I recall) is notified by the OS and performs a graceful shutdown of any running databases with ...