All versions of PostgreSQL. Add a version-specific tag like postgresql-9.4 if that context is important.

learn more… | top users | synonyms (1)

1
vote
1answer
332 views

Function quote_nullable(timestamp without time zone) is not unique

When I run this query on "enterprisedb 9.2", it raises an error: select quote_nullable(to_date('09-02-2014 ','dd-MM-yyyy'))::date; Error: LINE 1: select quote_nullable(to_date('09-02-2014 ...
1
vote
1answer
330 views

Compare Number of Rows of Tables

I have two or more tables different by structure and I want to compare their numbers of rows, which I get with the count-star query: select count(*) from table_a For two tables I would like to see ...
5
votes
1answer
794 views

Order by distance

If I have a query returning nearby cafes: SELECT * FROM cafes c WHERE ( ST_DWithin( ST_GeographyFromText( 'SRID=4326;POINT(' || c.longitude || ' ' || c.latitude || ')' ), ...
2
votes
2answers
187 views

SELECT DISTINCT with another table

I want to use distinct to display my data. But I don't know how to do it.This is my code.I want to display table gtest_version field max(poly_version) select distinct on (rule_name) ...
0
votes
1answer
279 views

Postgres Point-In-Time Recovery with a limited point in time

I'm configuring PostgreSQL WAL-based backup for point-in-time recovery. Actually, I don't really need any point in time beyond 1 month ago and don't want to waste time for the logs older than that. ...
5
votes
2answers
721 views

How can streaming replication be kept lagged by a certain 'delay' time?

Postgres has had streaming replication since 9.0 and since 9.1 it is possible to pause and resume the application of the streamed WAL records using pg_xlog_replay_pause() and pg_xlog_replay_resume(). ...
0
votes
1answer
144 views

How can I get the latest five scores per user per subject?

There is a Postgres table in our system called "scores" which records test scores. It has test takers (users of our system), scores from 0-10, depts and dates each time a test was taken, like this: ...
2
votes
1answer
255 views

What does: ERROR: tuple offset out of range: 0 mean?

It seems (but I can't tell for sure) that after I added a postgis indexed column (USING gist) to my database last week, I have been seeing a lot of those errors: ERROR: tuple offset out of range: 0 ...
0
votes
0answers
79 views

How large should a database table grow?

I have been experiencing some very slow queries for a specific table. This table grows daily. I ran the pg_relation_size function, which gives the size of a table in the database: mydb=# select ...
1
vote
2answers
341 views

optimization of expensive subquery

The following query gets the last report (where latitude and longitude and secs are <> 0) associated with the specified units: SELECT reports.* FROM reports INNER JOIN ...
1
vote
2answers
156 views

Should I create an index for non key columns?

I have a table in a PostGreSql database defined as following: CREATE TABLE public."MATCH"( "ITEM_A_ID" bigint DEFAULT 0, "ITEM_B_ID" bigint DEFAULT 0, "OWNER_A_ID" bigint DEFAULT 0, ...
3
votes
1answer
263 views

Use of integer instead of interval (of one type)

Our DB design presently has a interval column which will only be storing days (no other interval type) so it is making sense to use INT2 (smallint) instead of interval. Reference to documentation. ...
16
votes
3answers
16k views

upgrading from postgres 9.1 to 9.3 on ubuntu server

I have my production server(ubuntu 13.10) running with postgresql 9.1. I want to use few features of 9.3, hence want to upgrade. Could someone help me with upgrading from 9.1 to 9.3 so that there is ...
3
votes
1answer
781 views

Why does this limit make the postgres planner use a much slower index scan instead of a much faster bitmap heap/index scan?

I have a table of about 700k rows in Postgres 9.3.3, which has the following structure: Columns: content_body - text publish_date - timestamp without time zone published ...
2
votes
0answers
3k views

Unable to install postgresql 9.3 on windows 8.1

I have been trying, without success, to get posgresql 9.3 up and running on Windows 8.1 on and off over the last ten days. Here is what I have done so far: I got the windows installer from ...
11
votes
3answers
628 views

Can IS DISTINCT FROM be combined with ANY or ALL somehow?

Is a postgres way of combining IS DISTINCT FROM with ANY or some other neat way of getting the same result? select count(*) from (select 'A' foo union all select 'Z' union all select null) z where ...
1
vote
1answer
215 views

Best approach on storing/displaying sanitized data

Surfing the web I find two approaches on storing data in databases. Approach 1: Store data in the database with the html-specialcharacters sanitized $userdata = $_POST['field']; ...
1
vote
1answer
1k views

How do I return JSON structured as the transpose of json_agg() in PostgreSQL?

I'm trying to convert an arbitrary subquery to JSON in PostgreSQL. json_agg() seems like the obvious tool for the job. For example: select json_agg(a) from ( select count(lead_source), ...
2
votes
1answer
3k views

What is the difference between MySQL and PostgreSQL in terms of writing SQL queries? [closed]

Other than performance and security is there really any difference between MySQL and Postgres like SQL queries, built-in functions, procedures and so on? I'm asking this because I'm familiar with PHP ...
1
vote
1answer
460 views

Removing records from database periodically that surpass timeline

I have a reports table which has literally millions, if not hundreds of millions of report records, which date back to 2011. I think it's slowing down my queries exponentially. I only want to keep ...
1
vote
1answer
41 views

Has my 'cluster' operation succeeded?

I ran this command: cluster verbose address using address_locality_lookup; And got this result: INFO: clustering "dba.address" using sequential scan and sort INFO: "address": found 0 removable, ...
0
votes
0answers
291 views

requested shared memory size overflows size_t PostgreSQL

I am running PostgreSQL 9.2.3 on SUN Solaris 9. This is 64 bit system with 32G swap and 16G RAM. I use same configuration file as on Linux or SUN Solaris 10, where everything is ok. I am unable to ...
11
votes
1answer
5k views

Foreign key constraint on array member?

Suppose I have a table containing job roles: CREATE TABLE roles ( "role" character varying(80) NOT NULL, CONSTRAINT "role" PRIMARY KEY (role) ); Suppose I further have a table, users, and each ...
0
votes
1answer
875 views

Effective duplication of DELETE … LIMIT in Pg

I have an unusual problem: I need to delete a certain number records from a table, of a particular type; in certain non-compliant DBs it would be easy to accomplish this with DELETE FROM sometable ...
0
votes
2answers
754 views

25M x 25M inner join (postgresql) performance

I have a one time need to do an inner join of 25M rows on 25M rows. The box is a Alienware area 51, 4 cores 25GB memory and SATA drive (non system disk). So far it has taken 22 hours. I did btree ...
1
vote
1answer
103 views

Some SQLs sometimes take all available RAM

I'm running postgresql server on Windows Server 2008, which has 6GB of ram. My dataset is something near 1,5GB big. I have problems, when I run specific SQLs in table, which has 51mio of records. ...
0
votes
1answer
734 views

Close all connections to postgres without having a connection

I have a cron job that runs a script that connects to a remote postgres database that I do not administer. We are allotted a single connection and, on the occasion that I or a colleague log in to run ...
3
votes
1answer
1k views

Debugging why postgres is snowballing with system cpu usage

I am having an issue where my postgres database which run on a dedicated machine is getting hammered with kernal level cpu usage. This essentially takes down the web service that run on this database. ...
1
vote
1answer
341 views

complex update query postgres

I've got a bunch of ids from table messages and I want to update the table activities. This is how I imagined my query would work (so you get better idea what I'm trying to accomplish): update ...
1
vote
2answers
715 views

Extract all tags from hstore field

I need to extract (copy) all hstore tags by object id from a postgres 9.2+postgis 2.1 database into an external file for post-processing. Since the keys and values vary per record, and I don't know ...
1
vote
1answer
2k views

Secure postgresql database encryption

According to this answer, it's not possible to have a fully secure database encryption with pgcrypto. Now, 2 years later (in postgresql 9.3) is there any new extension or new method to have a ...
5
votes
2answers
827 views

Order of columns in a compound index in PostgreSQL (and query order)

I have a table with 50K rows. It is actually a PostGIS table. The query has 4 parts (1 mandatory) (3 Optional) intersection box (a geography rectangle) with 4 lat,long (I use st_intersects) ...
3
votes
2answers
2k views

Streaming Replication Log is puzzling me

I have a streaming replication hot-standby setup, which seems to be running all good. I can login, execute selects and the result seems to be up-to-date with the master. However in the log of the ...
0
votes
0answers
314 views

Postgres vs MySQL Database Maintenance

I have been using Postgres and MySQL for a long time now. There are a few routine maintenance chores that must be performed on a regular basis to keep a PostgreSQL server running smoothly. As as ...
2
votes
2answers
507 views

Postgres point() index not being used

\d aggregate Materialized view "public.aggregate" Column | Type | Modifiers ------------------+-----------------------------+----------- ...
3
votes
0answers
505 views

Poor performance in my pgpool cluster

I've configured a postgres cluster with pgpool2 using the following link. The replication seems to be ok, but I ran some benchmarks (pg_bench) and the performance is lower than a single node, for ...
1
vote
1answer
80 views

How can I improve performance of update query in PostgreSQL

I have a table with about 80 million records. I have added a new attribute (heigth_category) which I want to fill based on another attribute (heigth). If the heigth is between 0 and 6, I want to fill ...
1
vote
1answer
490 views

PostgreSQL - Return unique combinations of columns based on where clause

This is quite a long question, please bear with me. So I'd like to first explain I have a database of firewall logs created using the following command: CREATE TABLE firewall_logs_mapped ( ...
3
votes
1answer
346 views

PostgreSQL - VACUUM FULL does not free space back to the OS

On a Postgres 9.1 installation, I'm trying to reduce the amount of Postgres' disk usage by dropping records from tables and then running VACUUM FULL on those tables. My understanding of VACUUM FULL is ...
2
votes
1answer
526 views

Logging Postgres startup errors on Windows

I have been experimenting with Postgres configuration parameters and noticed that when I use settings that prevent Postgres from starting nothing shows up in the pg_log directory logs. I have ...
2
votes
2answers
85 views

Nearest value for each foreign key

I have a table with a primary key, timestamp, value, foreign key. I would like to find the nearest value to a timestamp for each of the foreign keys in a list. Unfortunately, the gap from the given ...
3
votes
1answer
1k views

What PL/pgSQL debugger can I use?

I'm building a lot of PL/pgSQL functions and a debugger would really come in handy because RAISE NOTICE is not enough. I read about a pgAdmin plugin you can install but it was only on Windows and I'm ...
0
votes
0answers
109 views

Postgres 9.2 on Windows

Simple question, but utterly infuriating. Trying to use psql from a command prompt, but can't access any of the previous commands with the up/down cursors. I could also really do with finding out ...
3
votes
1answer
263 views

Postgresql query slowed with table growth

I have a very simple table in Postgresql, and need some guidance to speed up my queries. Recent large expansion of the data in the table has made everything grind to halt. Table body_feature: ...
3
votes
1answer
1k views

PostgreSQL Streaming Replication - Slave remains in recovery mode

PostgesSQL 9.3.x I cannot run some commands on slave because: "ERROR: recovery is in progress" I set up streaming without archive. Initialized with pg_basebackup. Dataset has about 180GB . This is ...
3
votes
1answer
218 views

Postgresql 9.3 replication: Preventing wal-files from building up, if receiver goes offline

I have a streaming replication running between two servers. I just upgraded to postgresql 9.3 from 9.2. Earlier on in my 9.2 setup, I have tried that my slave server went offline due to a network ...
2
votes
1answer
372 views

Pg_upgrade failed - what does “re-initdb” mean?

My pg_upgrade command failed. During the upgrade it told me, that if things were failing after a curtain point (which they did) Then I should: If pg_upgrade fails after this point, you must re-initdb ...
1
vote
1answer
1k views

Postgres, levenshtein distance and nested queries

I have a DB Table with 3,000,000 rows or more like that: |ID|T4|T16|T64|TL where ID =INTEGER (PK) and T4,T16,T64,TL are text fields. Each one is larger in length than the previous, i.e., LENGTH(T4) ...
4
votes
1answer
2k views

pgBouncer works great but occasionally becomes unavailable

I'm running pgBouncer in front of a busy postgres 9 database. For most of the time it works fine. But every few hours I'll get an error email from my application with an exception from psycopg2: ...
1
vote
1answer
153 views

Postgres settings reported differently in pgsql

We are running PostgreSQL 9.2 on CentOS 6 and I am seeing the settings are reported differently when I run the show effective_cache_size; command vs select * from pg_settings where name = ...