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
2answers
106 views

Logging in to phppgadmin remotely Ubuntu 14.04 Amazon EC2

I created an Ubuntu server instance on Amazon EC2 and installed php, postgreSQL, phppgadmin, and any other required features. After SSH'ing into the server using command prompt, I executed the ...
0
votes
0answers
36 views

Best practice for select or insert into [duplicate]

I was looking for quite some time on whats the best way of selecting or inserting some data. I have two tables: ReportData: report_data_id(pk), source_id(fk), value1; Source: source_id(pk), ...
0
votes
0answers
26 views

User with Connect privilege can create tables [duplicate]

postgres=# \list+ List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | ...
2
votes
1answer
32 views

WAL files are not copied

I'm using postgres 9.3 on Ubuntu 14.04 and I'm currently setting up a WAL backup. These are my changes to the postgresql.conf: [as described here: ...
1
vote
1answer
104 views

Using EXCEPTION to ignore duplicates during bulk inserts

In pq 9.3.5, I'm importing records from an external source where duplicates are VERY rare, but they do happen. Given a readings table with a unique compound key on (real_time_device_id, recorded_at), ...
0
votes
1answer
29 views

Query with column data for a max

In Postgres, I have a table like this: Column | Type | Modifiers ...
0
votes
0answers
16 views

Is it possible to create materialized view based on another materialized view in postgres?

I have a very large table and I summarize it in a materialized view. I then create an even smaller materialized view based on the earlier mview. The second mv is always showing zero rows. Is there a ...
0
votes
1answer
265 views

connect to PostgreSQL server: FATAL: no pg_hba.conf entry for host

Hello I am trying to run a website sent to me but after doing so this error appeared connect to PostgreSQL server: FATAL: no pg_hba.conf entry for host "4X.XXX.XX.XXX", user "userXXX", database ...
2
votes
2answers
129 views

PostgreSQL joining using JSONB

I have this SQL: CREATE TABLE test(id SERIAL PRIMARY KEY, data JSONB); INSERT INTO test(data) VALUES('{"parent":null,"children":[2,3]}'); INSERT INTO test(data) ...
0
votes
0answers
36 views

Best way to store a function map

I am building a site where data gets filtered through user defined functions to create an output. It is a requirement that the functions are represented in the database as formulas. For example a ...
0
votes
0answers
29 views

PostgreSQL: Store hierarchial data and return a recursive result with full path data

Background: I am considering whether to use LTREE or Recursive CTE with Materialized Path. For storing data, I am deciding between HSTORE or JSON. Objective: I need to store a hierarchial data. Each ...
1
vote
1answer
53 views

PostgeSQL 9.3: What is the difference between text and pg_catalog.text?

I have a classic 3-environments setting (dev, staging and production), each with its own RDS PostgreSQL 9.3. The client/server versions are psql (9.3.5, server 9.3.3). In order to keep the schemas ...
1
vote
1answer
50 views

PostgreSQL, UPDATE CASE statement uses huge amount of space

I am running a CASE statement on one of my tables and it consumes a huge amount of hard drive space that is never freed up even when i run VACUUM ANALYZE. Autovacuum is ON as well. I am using ...
0
votes
0answers
215 views

ERROR: relation “name” does not exist

I am trying to import a postgresql database table sent to me in phpPgAdmin, I am following the instruction from this import database But after doing so, several error appear, I also tried to import ...
3
votes
1answer
154 views

Get list of characters in column

I have a database table (UTF-8) with a lot of names in different languages with lots of special characters. In another script I use these names and one part is to replace the special characters. To ...
0
votes
1answer
44 views

Dump all binary columns from a table as files

I have a table in a postgresql database with a column of type bytea. I want to drop each binary entry as a file using the id as filename. What would a SQL query look like for this?
0
votes
2answers
392 views

Performance Benchmark - MySQL vs PostgreSQL [closed]

this is my first question here in DBA. I wish to do a performance comparison between MySQL and PostgreSQL using same database on the basis of total time taken to parse + execute + send result back ...
0
votes
1answer
34 views

Postgresql - Streaming replication - STONITH

Current situation: PostgreSQL, streaming replication Master failed, I created the trigger file. Slave is promoted to new master After the old master restarted successfully, I have to create base ...
1
vote
1answer
29 views

can I give permission to insert into view, but not to referred table?

Here I have a view in public schema referring to the table in private schema: CREATE OR REPLACE VIEW public.products AS SELECT products.id, products.product_id, products.title, products.company_id ...
0
votes
1answer
17 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
0answers
23 views

Is content of the pg_autovacuum saved when pg_dumpall is used to backup the database?

From the docs: pg_dumpall also dumps global objects that are common to all databases. (pg_dump does not save these objects.) This currently includes information about database users and groups, ...
1
vote
0answers
34 views

Optimizing postgres row overlap constraints involving UUIDs and GIST

For various reasons all my tables have UUIDs as primary keys and I need to exclude duplicate rows where the combination of area_id, user_id, and finished_at <-> started_at window. The only way I ...
3
votes
1answer
67 views

PostgreSQL CREATE INDEX memory requirement

I've got 6GB of RAM on my laptop and for a test I'm creating a 50mil-rows table in PostgreSQL 9.3. I then want to create an index on the table. The table and the resulting index together (or twice ...
2
votes
0answers
90 views

UPDATE/DELETE performance on partitioned table

Hi I'm using table partitioning in my Postgres 9.3. I've notice that the DELETE and UPDATE queries are much slower when I'm doing them via the parent table. I see that the constraint_exclusion is ...
0
votes
1answer
31 views

Email open rate calculations in subqueries with postgres

So I'm relatively new to PostgreSQL, but here we go: I have a pgsql database that is receiving HTTP Post Email Events from my Sendgrid email server, and am trying to make a dashboard that displays ...
0
votes
1answer
30 views

Can any one Explain me What is the Use of Cascading Replication in Postgresql?

What are the advantages of cascading replication in PostgreSQL. I have done Testing On this With three nodes. M1---->S1----->S2 What is the Use if My master(M1) goes Down(failover). Where the ...
0
votes
1answer
70 views

Reassigning surrogate keys?

I have a few tables with surrogate keys (SERIAL PRIMARY KEYs unique to each tables). These tables are used by other tables, with references to these keys. My usage of these is quite simple : select ...
3
votes
1answer
70 views

How to determine cost/benefit of adding an index?

According to Craig Ringer: While it's usually a good idea to create an index on (or including) your referencing-side foreign key columns, it isn't required. Each index you add slows DML operations ...
2
votes
1answer
41 views

Removing a FAILED node from repmgr cluster

How do I remove failed node from a PostgreSQL cluster? postgres@db-prod-ms47ox-10-3-104-92:~$ repmgr -f /etc/repmgr/repmgr.conf cluster show Role | Connection String * master | ...
3
votes
3answers
122 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
44 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
71 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
38 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
61 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
79 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
62 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
128 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
64 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
39 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
37 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
66 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
44 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
62 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
24 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
114 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
15 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
40 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
38 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
36 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
69 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 ...