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)

1
vote
0answers
14 views

(How) does table inheritance interfere with locking in postgres?

I have a table inheritance setup that might be simplified like this: CREATE TABLE p ( id BIGSERIAL PRIMARY KEY, type_id BIGINT, approved BOOLEAN ); CREATE INDEX ON p(approved); CREATE ...
2
votes
1answer
38 views

Unused index in range of dates query (PostgreSQL, timestamp with time zone)

I think maybe I am making a dumb mistake here, so apologies in advance. But I have a query that is not using existing indices and I do not understand why. Am I reading the plan wrong? Are the indices ...
0
votes
1answer
16 views

Adding column to Postgres table fails

I have a Postgres (8.4.20) table that I'm supposed to add columns to, but it seems to have some problems that I have difficulties to debug: I'm attempting what I thought was a simple ALTER TABLE... ...
0
votes
0answers
14 views

Need help on transferring data from a PostgreSQL database to a future (to be decided) database!

(English isn't my native language) I'm doing an internship for school and my task is to theoretically describe the best way to transfer the data from their current database (PostgreSQL) that they have ...
1
vote
1answer
57 views
+50

Clustered index performance consideration in MySQL & PostgreSQL

In MySQL/InnoDB, the clustered index is synonymous with the primary key, so picking up a poor primary key affect your db performance, i.e. use of UUID as PK is a performance killer for database write. ...
7
votes
2answers
973 views

Migrate heroku database to Amazon RDS with minimum downtime

I have a heroku postgres database and want to migrate it to Amazon RDS to save cost. What's a way to do so with minimum downtime? Usually this involves replicating database in real time and then ...
1
vote
2answers
1k views

pgpool replication not allowing md5 authentication

I have installed both pgpool and postgresql via apt. In pgpool.conf, I have made the changes necessary to use replication mode, as well as my two postgresql databases (currently all running on the ...
4
votes
2answers
54 views

Take advantage of monotonic columns in PostreSQL

I have a PostgreSQL table with, among the other, two columns named col1 and col2, both of integer type (there are around 10M rows in the table). I want to perform SQL queries like: SELECT * FROM ...
232
votes
7answers
435k views

How do I list all databases and tables using psql?

I am trying to learn PostgreSQL administration and have started learning how to use the psql command line tool. When I log in with psql --username=postgres, how do I list all databases and tables? I ...
1
vote
1answer
509 views

Tree structure hierarchical query sorting

I have the following structure of table. CREATE TABLE table1 ( serial_num integer NOT NULL, parent_num integer, parent_key text, key_ary_num integer[], level integer, rank integer, ...
3
votes
0answers
45 views

PostgreSQL operator uses index but underlying function does not

I'm attempting to use JSONB with JDBC, which means that I have to avoid any of the operators which use the '?' character (as the PostgreSQL JDBC driver has no escaping for this character). Taking a ...
0
votes
1answer
21 views

Storing latitude and longitude

I know there are similar questions on here that have been answered but unfortunately none of them work for me. I am hacking my way through creating a page which will display multiple locations on a ...
25
votes
5answers
8k views

Working of indexes in PostgreSQL

I have a couple of questions regarding working of indexes in PostgreSQL. I have a Friends table with the following index: Friends ( user_id1 ,user_id2) user_id1 and user_id2 are foreign keys to ...
0
votes
2answers
103 views

no pg_hba.conf entry for host “10.1.1.91”, user “user” database “testing”, SSL off

I've come across different versions of this same issue on stackoverflow and on this forum. I've tried the recommendations posted on these forums but I am still unable to connect to a database. I've ...
0
votes
0answers
23 views

PostgreSQL Performance - Many Large Databases

I run an internal (development) database server containing 25 databases each ~71GB in size. There are usually between 50 and 100 active connections at any given time. What can I do to help improve ...
0
votes
0answers
19 views

Replication Strategy for Multi-Office Database

I'm considering options for setting up a repository of large spatial datasets that can be used by employees across our company. We have offices scattered around the US. Our employees utilize ...
-2
votes
1answer
37 views

Timeline or time function in PostgreSQL

In MySQL I can use this select which show timeline view on data in my table/tables. SELECT COUNT(atribute), connection_timestamp FROM db GROUP BY DAYOFYEAR(connection_timestamp) ORDER BY ...
1
vote
0answers
14 views

choosing between the slaves on streaming replication

We are building a new streaming replication setup on postgres 9.3 integrated with pgpool for load balancing and failover, and repmgr for replication business. We have seperate daemons running on all ...
1
vote
1answer
37 views

Why won't pg_basebackup output go to log?

I run a nightly pg_basebackup from a cron job (Postgres 9.3 on debian "wheezy"), but the backup has been failing. It actually creates output, but the .tar file is corrupt and won't extract. I went to ...
3
votes
1answer
40 views

PostgreSql JSONB SELECT against multiple values

I have a very simple JSON table which I populate with some sample data: CREATE TABLE jsonthings(d JSONB NOT NULL); INSERT INTO jsonthings VALUES ('{"name":"First","tags":["foo"]}'); INSERT INTO ...
0
votes
1answer
37 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
29 views

How to store thousands of properties related to a record in PostgreSQL?

I have a database of approximately 300K records. For each of theses records there are up to 1600 related properties. Each of these properties have a simple key=>value format. This data is very static ...
0
votes
1answer
187 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 ...
1
vote
0answers
18 views

PostgreSQL: Foreign key on string column

I'm currently in a process of redesigning a legacy database. One of the tables primary key is a varchar(254). I need to create a table relating to it. So for example: create table "Item" ( ...
2
votes
3answers
747 views

postgreSQL 9.3 streaming replication delay

I'm running a fairly simple WAL streaming replication setup between two postgreSQL 9.3 servers that are on a private 1Gb LAN, but recently I've noticed that there is an increasing amount of ...
1
vote
0answers
12 views

Simplifying UNION query that looks for duplicate names

create table sections( id serial, section_name char(255) not null, version_id int not null ); create table section_versions( id int primary key not null, version_name char(255) not null ); ...
0
votes
2answers
26 views

Lower() vs ilike on postgresql

What I want to do is to check if value of column equals some string, but case insensitive. Is there a performance difference when using ilike without wildcards (percents) and lower() ? I was planning ...
1
vote
1answer
403 views

Upgrading using pg_upgrade on windows fails

When upgrading from postgres 9.2 to 9.3 by following the upgrade procedure found in PostgreSQL 9.3.4 documentation about pg_upgrade, the following command fails: RUNAS /USER:postgres "CMD.EXE" ...
0
votes
1answer
35 views

Safe way to perform DB upgrade

If I am reading the documentation correctly, issuing this command psql -v ON_ERROR_STOP=1 database < script.sql where script.sql starts with BEGIN BEGIN; ALTER TABLE... some other DDL... ...
-4
votes
0answers
29 views

Getting count on multiple fields from table [on hold]

Please take a look at this page Travelmob. As you keep filtering on the "Amenities" or "Experience" on the left side, it update the total count on all of the fields. How do they do that? (could it ...
0
votes
1answer
172 views

Problems using “Excel PostgreSQL Import, Export & Convert Software”

I want to export data from Postgresql, and I tried many ways then find the tutorial of Excel PostgreSQL Import, Export & Convert Software on Youtube. I installed the software, but I could not ...
0
votes
1answer
24 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
0answers
42 views

Problem installing postgres 9.4 on Windows R2

I am trying to install postgres 9.4 on Windows using the "Enterprise db" installer and in the last part of the installation show the message "Failed to initialise the database cluster With initdb". I ...
0
votes
0answers
26 views

Maximum sum() of overlapping ranges

Essentially my question is: how does one do aggregate operations involving overlaping ranges in Postgresql 9.3 (or 9.4)? The specific problem I have at hand is that given a range, I want to find the ...
0
votes
1answer
335 views

Sync offline PostgreSQL server

I am new to PostgreSQL. I am looking for a solution wherein 2 mirror images of a database sit on a local and remote server. All the work is done on the local server and at periodically synced with the ...
6
votes
3answers
4k views

PostgreSQL Sequential Scan instead of Index Scan Why?

Hi All I've got a problem with my PostgreSQL database query and wondering if anyone can help. In some scenarios my query seems to ignore the index that I've created which is used for joining the two ...
0
votes
0answers
17 views

PostgreSQL and array of custom type

One of the table I have looks like this CREATE TABLE orders_suppliers_contacts ( supplier_id bigint NOT NULL, name character varying(64) NOT NULL, phone character varying(18), notes character ...
2
votes
1answer
38 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 | ...
1
vote
2answers
41 views

Slow query when adding additional where clause

Here's my setup (Postgres 9.3) Posts project_id Messages post_id kind updated_at I'm trying to get the most recently updated 100 messages that belong to posts (on a specific project) that are ...
1
vote
2answers
42 views

Efficient query to get last row group by multiple columns

I have a table like the following: spread_id serial NOT NULL, game_id integer NOT NULL, sportsbook_id integer NOT NULL, spread_type integer NOT NULL, spread_duration integer NOT NULL, home_line ...
1
vote
1answer
38 views

Use result of aggregate in same select?

Is it possible to feed the result of an aggregate select into the same select with Postgresql? I'm aware of the WITH clause -- but, this would be true syntactical sugar, wouldn't it? SELECT ...
0
votes
1answer
58 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 ...
1
vote
2answers
40 views

Return the id after insert or select

I want to build a function which will insert an email if the email value doesn't exist in the table and return the email_id of the row. How can I do this? Also how can I return the id if the email was ...
-3
votes
0answers
14 views

compact database [closed]

I am using postgres 8.4 for Jboss Operation Network on Centos, now I want to compact the data ! can you give me best idea about that compact the data and also share with me step be step procedure ...
12
votes
1answer
20k views

How to use psql with no password prompt?

I wrote a script to REINDEX indexes in a database. Here is one of them: echo -e "\nreindex for unq_vbvdata_vehicle started at: `date "+%F %T"`" >> ${LOG_FILE} psql -U ${USERNAME} -h ${HOSTNAME} ...
0
votes
0answers
20 views

Using Code to Access Database Dump via pgAdmin

I looked over this question on how to import a data dump using pgadmin and found the following code: CREATE OR REPLACE FUNCTION db_to_csv(path TEXT) RETURNS void AS $$ tables RECORD; gmdeclare ...
19
votes
4answers
17k views

Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

I had to write a simple query where I go looking for people's name that start with a B or a D : SELECT s.name FROM spelers s WHERE s.name LIKE 'B%' OR s.name LIKE 'D%' ORDER BY 1 I was wondering ...
0
votes
2answers
34 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
0answers
37 views

Is there any free, usable PostgreSQL formatter on Windows? [closed]

We're using pgAdmin3 and Notepad++ and we need some kind of a formatter that helps us format the old code we took from the lazy programmers. What I tried so far and didn't work: Poor Man's TSQL ...
1
vote
1answer
27 views

Is there a way to query all error messages in Postgres?

Postgres has a catalogue of possible error messages here: http://www.postgresql.org/docs/8.2/static/errcodes-appendix.html#ERRCODES-TABLE However, I would like to query this information from a ...