PostgreSQL is a powerful, enterprise class, open source RDBMS. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability and data integrity. It runs on all major operating systems, including Linux, UNIX and Windows. It prides ...

learn more… | top users | synonyms (1)

0
votes
0answers
5 views

Postgres Order By

I am attempting to run a Query to sort by one of our columns study.version. However every time I run this query, I get hit with a syntax error and can't seem to find out why this would be wrong: ...
0
votes
0answers
17 views

Trying to set up automatic process to append and update tuples [on hold]

I am trying to figure out a way to automate a process that I currently have that appends a new column on and updates two or three other columns when I add a new table into a database. The goal is to ...
3
votes
1answer
63 views

For alignment optimized table is bigger than original table - why?

In another question I learned that I should optimize the layout from one of my tables to save space and have better performance. I did this, but ended up with a larger table than before and ...
0
votes
0answers
9 views

Problems installing Postgis extension on Ubuntu 14.04

I have Ubuntu 14.04 with Postgres 9.3 and I'm trying to install Postgis extension. One of the most recommended and straightforward ways I found is very simple: sudo apt-get install postgresql-contrib ...
1
vote
1answer
29 views

What is the impact of building Postgres with --disable-thread-safety option?

I am a newbie in the database field. Recently I have been trying to install Postgres 9.3.4 on AIX 6.1. When I was building the code with the configure command, I ran into this error: checking thread ...
0
votes
0answers
34 views

Select script for selecting two columns in a table [migrated]

I need help writing a Select script which selects data from two columns based on a condition. Say I have a table Table: X Columns: ID, Type , and Old_type Column "Old_type" has nulls or historic ...
1
vote
1answer
36 views

Good layout of 3d point data for spatial queries in Postgres?

Like shown in another question, I deal with a lot (>10,000,000) entries of points in a 3D space. These points are defined like this: CREATE TYPE float3d AS ( x real, y real, z real); If I am ...
1
vote
1answer
12 views

Is 'internallength' always useful in custom type definition in Postgres?

In our database we use a custom type to store 3D locations: CREATE TYPE float3d AS ( x real, y real, z real); In many examples I see INTERNALLENGTH used as a parameter with the definition of ...
0
votes
2answers
19 views

starting pgbouncer on startup

I installed pgbouncer and configured it and it starts only via using this command pgbouncer /etc/pgbouncer/pgbouncer.ini however using service pgbouncer start it dose not start. I then set up ...
2
votes
1answer
14 views

Replicate single table from Postgres 9.3 replica

I have an existing Postgres 9.3 server acting as a read-only replica using the built-in streaming replication. I need to replicate a single table from a database on that server to another physical ...
6
votes
2answers
71 views

Index that is not used, yet influences query

I've got a PostgreSQL 9.3 table with some numbers and some additional data: CREATE TABLE mytable ( myid BIGINT, somedata BYTEA ) This table currently has about 10M records and takes 1GB of ...
2
votes
1answer
39 views

PL/pgSQL function or rCTE to detect depth of relations between two tables

I need to perform a PostgreSQL Function for checking the depth of relations. There is a circular binding from table a to b, back to another element in a and, in some cases back again to b. These ...
3
votes
3answers
244 views

Checking whether two tables have identical content in PostgreSQL

This has already been asked on Stack Overflow, but only for MySQL. I'm using PostgreSQL. Unfortunately (and surprisingly) PostgreSQL does not seem to have something like CHECKSUM table. A PostgreSQL ...
1
vote
1answer
31 views

Missing the pg_ctl package in Postgres 9.3 installation

I have installed postgresql 9.3 on ubuntu 14.04 using "apt-get install postgresql". Everything was going well until I discovered that I do not have access to the "pg_ctl" commands. Installing the ...
0
votes
0answers
26 views

Import xml data into postgresql table [on hold]

I've got xml file with data : <teryt> <catalog name="TERC" type="all" date="2014-01-01"> <row> <col name="WOJ">02</col> <col name="POW"/> <col name="GMI"/> ...
0
votes
0answers
17 views

How to make inet_server_addr() return localhost in spite of ::1/128

How to make inet_server_addr() return an name as IPv4?
-1
votes
1answer
39 views

Database Design for classifieds site [on hold]

I'm creating a classifieds site where there are many categories and subcategories. 1) Each category has different fields. 2) Each listing has one or more images I need help with the database design. ...
0
votes
2answers
31 views

Auto increment char varying column prefixed with some characters

I have a table in PostgreSQL for hospital patients and I want to make a primary key for it as PAT0000001 (char varying) and to keep that field as auto-increment as well. Is there a way to achieve ...
0
votes
2answers
51 views

Database Design Help: MySql/PostgreSQL Single Database/Multiple Databases

We are planning to build a web based custom business analytics tool using PHP and MySQL/PostgreSQL. In our application, we are planning to upload customer’s data using a Excel sheet and these data ...
1
vote
1answer
44 views

Improve performance on concurrent UPDATEs for a timestamp column in Postgres

I am using a timestamp column called updated_at for cache invalidation. More information on this particular technique here. The queries all have the same format UPDATE "managers" SET "updated_at" = ...
0
votes
2answers
21 views

Filling in missing dates in record set from generate_series()

Consider: with days as (select day::date from generate_series(date '2013-01-01', date '2013-01-01' + 365, interval '1 day' day) day ) select 'Inspections'::text as data_label, count(i.reporting_id) ...
2
votes
1answer
35 views

Why is my composite index not used for this table?

I am working with a fairly complicated schema, but I've simplified it for the purposes of this question. Table "public.scheduler_appointment" Column | Type ...
0
votes
1answer
18 views

PostgreSQL - If I run multiple queries concurrently, under what circumstances would I see a speedup? Under what circumstances would I see a slowdown?

I approach you all humbly as one who is NOT a DBA, and I'm sure that my question is fraught with conceptual shortcomings and "it depends on" land mines. I'm also pretty sure that all of you who ...
0
votes
2answers
54 views

How to change schema so that account_id reference is unique among 3 tables

Original image: Updated to have more-correct terminology and an 'is_debit' column: I am designing a (PostgreSQL) schema for a lottery website that uses double-entry accounting. 'jackpots', ...
0
votes
0answers
32 views

Wrapper function with SECURITY DEFINER is slow

I have a strange behavior of wrapper function with SECURITY DEFINER option. Function 1 - Queries data what is not directly accessible by user. Function 2 - Is a wrapper that calls function 1 with no ...
1
vote
2answers
56 views

How to construct a set-based query for subset problem?

I have a DB of the following structure (picture shows simplified version) How should I construct a query to get only those recipees, that can be cooked, i.e. for each ingredient in a receipe ...
1
vote
0answers
23 views

postgres - generating documents for full text search taking forever and eating up disk space

We use PostgreSQL and Django-Haystack with Elasticsearch for search on our service, and were thinking of switching to Postgres's built in full text search because of various annoyances using a NoSQL ...
1
vote
0answers
39 views

Should one always VACUUM ANALYZE before REINDEXing in PostgreSQL 8.4?

Early in the morning every day a pgAgent job refreshes the contents of table A from table B on my PostgreSQL 8.4 database. Table A contains around 140k records across 91 columns and has two indexes - ...
0
votes
1answer
18 views

Custom personal monitor sql by writing the .psqlrc file

I want to custom my personaly monitor sql by writing the .psqlrc file , but I encounter some problems . --tablespace skypcsuit=> \db List of tablespaces Name ...
2
votes
1answer
25 views

Prevent postgres function of running more than 1 instance at the same time

DBMS: Postgres 9.3.4 OS: Debian 7 I have a plpgsql function that will be put in crontab to run every 15 minutes. The function should finish in about 8 minutes, but just in case it takes more than 15 ...
1
vote
0answers
25 views

Selecting aggregated string from database

I have a table in my database consisting of one column which is NewLanguage(varchar). I insert new languages to the table and I want to select the rows of the table and assign them to a string ...
2
votes
0answers
41 views

Fast hamming distance queries in postgres

I have a large database (16M rows) containing perceptual hashes of images. I'd like to be able to search for rows by hamming distance in a reasonable timeframe. Currently, as far as I properly ...
3
votes
2answers
93 views

How to avoid the SORT operation in SQL query that contains ORDER_BY condition?

I am optimising a PostgreSQL query which involves three tables and a ORDER_BY condition. It seems that PostgreSQL prefer to arrange a SORT operation to perform the ORDER_BY condition, rather than ...
0
votes
0answers
17 views

Using PGBouncer with HAProxy results in connection errors only after load testing

We have a website (flask python based) which is load balanced between to instances. This two clients have both their own pgbouncer master which connects to a single HAProxy which is round robin ...
2
votes
2answers
102 views

Conditionally sum a column based on a mod result [closed]

I have the following query: SELECT y.datamov, y.n_conta, y.sinal, y.valor, y.qtde, a.nome, '', 0, a.lrdarc FROM ( SELECT k.datamov, ...
3
votes
2answers
77 views

How to inner join some tables and outer join others?

I'm certain I'm going to be embarrassed by the eventual answer, but I've been googling around and banging my head against the wall and can't figure it out. I have four tables described below. I'm ...
3
votes
1answer
29 views

duplicate key violates unique constraint when using multi-threading

I am using ExecutorService with a fixed thread pool of 50, and a fixed database connection pool of 50, using HikariCP. Each worker thread processes a packet (a "report"), checks if it is valid (where ...
3
votes
1answer
57 views

Very large btree index with few rows (openstreetmap gis data)

I tried to import a subset of open street map data to a Postgres database using the standard tool (osm2pgsql). I then tried to rebuild the indices on the table using plain sql (dropping them, then ...
1
vote
1answer
21 views

Explain PostgreSql ssl_cipher values: ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTH

In the postgresql.conf file, one can configure authorized values for ssl_cipher. Yet I could not find documentation explaining what the ALL, ADH, LOW, EXP, MD5 and STRENGTH values correspond to. MD5 ...
0
votes
1answer
26 views

PostgreSQL: How to list the comments attached to a database

How do I display the comments associated with a database, not a table? I've tried \dd and \list , but can't seem to find this. Thanks,
0
votes
0answers
27 views

What's causing Postgres to freeze?

I'm using Amazon RDS to run my Postgres database, v9.3.3. Recently, I had 2 instances where updating the schema caused the database to stop responding and I had to restart it in order to put the site ...
0
votes
0answers
8 views

Problems with PostgreSQL and ESRI Geoportal Installation on CentOS

I am installing geoportal 1.2.4 on CentOS from the command line terminal...during my installation I am receiving a fatal ident error message for my user geoportal. The pg_hba.conf file has been ...
0
votes
1answer
17 views

PostgreSQL, PostGIS: Copying a schema with a geometry column

According to the PostGIS manual, geometry columns should not by added directly, but using AddGeometryColumn(): AddGeometryColumn — Adds a geometry column to an existing table of attributes. I ...
1
vote
2answers
39 views

PostgreSQL/ Linux, way to have DNS forward to a port/cluster?

Aws linux(centos) ec2 Host: mydb. 10.10.10.10. 3 clusters: ports 5432, 5433, 5434 What I'd like to see is: db2.mydb or mydb/db2 connect/fwd to cluster2 (5433). Anything Linux or psql come to mind? ...
1
vote
2answers
55 views

Primary key with randomly varying increments (so it cannot be guessed easily)

I would like the primary keys to be auto-incremented and generated but in varying increments. For ex, if I have increment range as 100... then the auto generated keys would be something like below: - ...
0
votes
1answer
22 views

Postgres 9.3 changes the standard_conforming_strings value

I'm migrating a legacy application's database from Postgres 8.1 to Postgres 9.3 In order to avoid compatibility issues, I'd like to keep the standard_conforming_strings parameter to it's default ...
0
votes
1answer
29 views

Demo DB for book on PostgreSQL

The book PostgreSQL: Introduction and Concepts, printed in 2000, was highly recommended to me by the regulars in PostgreSQL IRC channel. However - I was trying to figure out which demo database ...
1
vote
1answer
28 views

Postgresql: set default psql client encoding

When I connect to a Postgresql DB using psql, I often get these messages: => SELECT * FROM question_view ; ERROR: character with byte sequence 0xd7 0x9e in encoding "UTF8" has no equivalent in ...
0
votes
1answer
25 views

Mixed search in multiple columns

I have a table which has a particular column of type JSON, where some user's contact information are stored. I can alter the table and add new columns with cached values to increase search performance ...
1
vote
0answers
57 views

Where Can I find *Current* PostgreSQL advice for a SQL Server DBA? [closed]

Been a MSSQL DBA for about 7 years. I have the fundamentals of performance, backups, replication, security and design down for MSSQL. I'm not setting up new applications using PostgreSQL 9.2. I have ...