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
13 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 ...
0
votes
1answer
16 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 ...
4
votes
2answers
49 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 ...
0
votes
0answers
21 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 ...
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 ...
2
votes
0answers
41 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 ...
3
votes
1answer
37 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 ...
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" ( ...
1
vote
0answers
11 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
1answer
27 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 ...
-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
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 ...
0
votes
1answer
36 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
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
25 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
0answers
33 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
16 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 ...
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... ...
1
vote
2answers
41 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 ...
-3
votes
0answers
14 views

compact database [on hold]

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 ...
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 ...
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
1answer
35 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 ...
-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 ...
0
votes
2answers
33 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
36 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 ...
0
votes
1answer
22 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: ...
1
vote
1answer
25 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 ...
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 ...
0
votes
1answer
57 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 ...
2
votes
1answer
42 views

Retrieving latest record using DISTINCT ON is slow [duplicate]

Using Postgres 9.4. I have a table cartests with 5.5M rows. Each row is a car test: \d log.cartests; Table "log.cartests" Column | Type | Modifiers ...
1
vote
1answer
165 views

Designing a High Score/Leaderboard table

I'm looking for input on how to design a highscore table for a game I'm creating. The database I'm using is postgres. That's not really something I can or want to change (unless there's very good ...
0
votes
1answer
25 views

How does Amazon create the RDS for Postgres? Some kind of Docker?

I am really curious about the infrastructure of Amazon. When I create a new instance of RDS, it gives me a host, username, password.. Everything. Behind the curtains, how do they do this? It´s some ...
3
votes
1answer
52 views

Is there a way to insert multiple rows into a table with default values for all columns?

I can insert multiple rows into a table with default values for all columns the RBAR way: create table course(course_id serial primary key); do $$ begin for i in 1..100000 loop insert into ...
0
votes
0answers
45 views

Partial unique constraint spanning multiple tables in postgres

To enforce partial uniqueness in postgres, it is a well known workaround to create a partial unique index instead of an explicit constraint, like so: CREATE TABLE possession ( possession_id serial ...
-2
votes
0answers
15 views

how to design Postgres data agregation properly? [closed]

I have to solve aggregation data problem. I have a big table of data (40 millions od records). Table contains 2 columns (id, zipcode). I want to make searches by ZIP code. I know I have to create ...
0
votes
0answers
54 views

Upgrade Postgres 9.3 to 9.4 after Ubuntu 14.04 to 14.10 upgrade

I've just recently upgraded my Ubuntu install from 14.04 to 14.10. This automatically pushed Postgres 9.4 onto the system. I'm trying to migrate my cluster like this: sudo pg_dropcluster 9.4 main ...
1
vote
0answers
27 views

PostgreSQL timezone setting

While I've spent many years as a SQL developer, I'm not really a DBA, but I'm the closest thing that my company has to one. We have recently started moving many of our PostgreSQL databases from Heroku ...
1
vote
1answer
42 views

Inserting into related tables

I have a database setup with the following schema: CREATE TABLE siteInfo ( siteID SERIAL NOT NULL, siteName TEXT NOT NULL, siteHost TEXT ...
6
votes
1answer
457 views

Would index lookup be noticeably faster with char vs varchar when all values are 36 chars

I have a legacy schema (disclaimer!) that uses a hash-based generated id for the primary key for all tables (there are many). An example of such an id is: 922475bb-ad93-43ee-9487-d2671b886479 There ...
1
vote
1answer
21 views

Pre-existing shared memory block (key 5432001, ID 32768) is still in use

I have been seeing this error lately that is causing the server to crash all of a sudden. I have seen elsewhere that the solution is to delete the postmaster.pid and postmaster.opts files and restart ...
2
votes
1answer
58 views

How to return a record from function, executed by INSERT/UPDATE rule (trigger)?

I'm having some issue when I'm trying to join two tables in a single view (for migrating code we want to split one big record into two records). I'm using Postgres 9.4! Here is an SQL fiddle. The ...
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 ...
0
votes
1answer
29 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 ...
1
vote
1answer
23 views

How do you drop an unnamed check constraint in a PostgresQL table?

I created a PostgresQL table but I added an unnamed check constraint on one of the columns: CREATE TABLE FOO ( id serial primary key, price_range smallint CHECK (price_range > 0), url varchar(255) ...
0
votes
2answers
34 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 ...
5
votes
2answers
151 views

Feasible to have thousands of users in Postgres?

We are creating SAAS where we will at most have 50.000 customers. We are considering creating a user in the Postgres database for each customer. We will map each user that logs into our service to a ...
0
votes
1answer
52 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
20 views

synchronizing schemas in server

In production server there's a database per company, with each produced from a single template. CREATE DATABASE company_x WITH TEMPLATE company_template; Let's say I have updated the schema, is ...