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

learn more… | top users | synonyms (1)

4
votes
1answer
211 views

Truncate parent table but not children

I have a table in PostgreSQL 9.4 that I partitioned so that all inserts to the parent actually go into one of the child partitions. The problem is that I already have hundreds of millions of rows of ...
4
votes
1answer
2k views

set session - custom variable to store user id

I want to store user id in custom session variable and use it (read) in trigger procedures to authorize user actions. I found something like this: set session "myapp.user" = '12345'; ... SELECT ...
4
votes
2answers
2k views

Add function parameter to SQL query WHERE clause

I have a web application in java and it uses a query. I don't want to write the query into Java, so I made a function: CREATE OR REPLACE FUNCTION testFunc(inputs text) RETURNS TABLE(...) AS $$ ...
4
votes
1answer
4k views

Postgresql doesn't accept replication connection

Plain old streaming replication. PostgreSQL: 9.2.7 Windows 8.1 64 bit My primary and secondary clusters are on same windows machine. I have already done pg_start_backup() and everything, so both ...
4
votes
2answers
2k views

Slow index scans in large table

Using PostgreSQL 9.2, I have troubles with slow queries on a relatively large table (200+ million rows). I'm not trying anything crazy, just adding historic values. Below is the query and the query ...
4
votes
1answer
3k views

Why use RDS over EC2 for Postgres?

I have a database currently running on EC2. As I have to move it to a bigger machine the question of using RDS came up. Pricing: I can get an EC2 c3.large instance with two SSDs (16GB each), two ...
4
votes
1answer
1k views

How to optimise window queries in postgres

I have the following table with approximately 175k records: Column | Type | Modifiers ...
4
votes
1answer
676 views

Pull data from multiple tables in a view or function

There are 3 tables from which I need to pull data, and print it in a specific way. http://sqlfiddle.com/#!15/59481/8 Refer to this question to see how the query was produced. The version in the ...
4
votes
3answers
2k views

How to SUM previous sum, e.g N = (N-1) + (N-2) + … + 1?

I have a table name "TABLE_A (id integer, no integer)" . I want to sum "no" with group by "id" and current "sum of no" = previous "sum of no" Here is my code : 1/ Create table & insert data: ...
4
votes
2answers
234 views

Compound Index on 43 Million PostgreSQL table

This question is related to a prior one of I asked: Order of columns in a compound index in PostgreSQL (and query order) Rather than overload that question, I figure I can sharpen and limit my ...
4
votes
1answer
3k 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: ...
4
votes
1answer
1k views

How to correctly return a query result only if it isn't NULL?

I'm writing a PL/pgSQL function that creates a cursor for a query that I need to check whether it returns something. What I'm doing is this: Run the query Check whether it returns something. If it ...
4
votes
2answers
6k views

Which Postgresql Replication Solution to Use? (Asynchronous Multimaster / Bucardo?)

First, I'm not a DBA, so pardon me if any of this question seems "off." I've written a peer-to-peer multiplayer game (the client) which connects to one of multiple servers for match making. ...
4
votes
1answer
1k views

No way to set maximum retrieved rows in pgadmin query tool

I'm using version 1.16.1, and I can't find a "max(imum) rows" param to tweak. Any help would be appreciated.
4
votes
1answer
2k views

PostgreSQL 8.4: How to tell if a procedural language is installed or not?

I have a setup program that requires plpgsql to install stored procedures in a PostgreSQL 8.4 database. I need to make sure the language is installed or the app will fail. I don't want to drop the ...
4
votes
1answer
177 views

Postgresql, write archive file over mount, secure against simultaneous access?

I am using Postgres 9.2.1 and am saving my archivable WAL over a NFS share. I just use the basic command, given as an example in the postgresql.conf test ! -f /mnt/server/archivedir/%f && cp ...
4
votes
1answer
639 views

PostgreSQL psql command line tool and SET CONSTRAINTS DEFERRED

I am using PostgreSQL 9.1.5 and the psql to run a scipt containing a series of INSERT statements in various tables: psql -U usernamefoo databasenamefoo -f dml_script.sql The table constraints have ...
4
votes
1answer
7k views

how do I execute a void-returning procedure in pgsql 9.1?

If I execute the following in a file with the pgsql command, it complains that the procedure needs to be called with 'perform'. But when I try to use perform, it tells me that perform is not defined. ...
4
votes
2answers
3k views

Improving performance of nasty nested-view joins

I have a moderate-sized database spread out over a few tables, the rough architecture is: Input Data (Data ID, Session ID and a few fields of statistical importance) Input File (Data ID and a blob) ...
4
votes
1answer
2k views

Creating UUID extension as the non super privileged user

I need to load the uuid extension on my database, which is being created in runtime. The user that created the db is not superuser, she only has the "CREATEDB" permissions. Is there a way to do this? ...
4
votes
2answers
3k views

PostgreSQL: count datasets / lines containing NULL values

I'm looking for a easy function to get the number of datasets (of any table) containing NULL values. It's not important how many columns are involved. For example: CREATE TABLE tab1 ( id INTEGER ...
4
votes
1answer
55 views

Is it possible to create a dynamic join?

I'm using PostgreSQL if it matters. Assuming these two tables: id | run ----+------- 1 | run_1 2 | run_2 3 | run_3 and sample | elapsed | run -------+---------+------- samp1 | 1:09 ...
4
votes
3answers
48 views

What is the easiest way to backup PostgreSQL and send backups to Google Drive?

I am new with PostgreSQL, So my question is: what is the easiest way to make regular backups and send them to Google Drive. Perhaps, there is a tool that can make it or something else.
4
votes
1answer
40 views

PostgreSQL join subquery can't restrict query

I'm working on a check-splitting project that splits out amount owed for an activity. Users can be a part of multiple 'groups', which each have their own running balances. I'm trying to run a query ...
4
votes
1answer
78 views

Improving sort performance in GROUP BY clause

I have two tables in Postgres 9.4.1 events and event_refs with the following schemas: events table CREATE TABLE events ( id serial NOT NULL PRIMARY KEY, event_type text NOT NULL, event_path ...
4
votes
1answer
49 views

Group and count array elements using intarray

I am working on a Postgres 9.4 project with the intarray extension enabled. We have a table that looks like this: items ------------------------------------- id name tag_ids ...
4
votes
1answer
234 views

Do indexes get transferred with pg_restore

I see other questions that have been answered in the negative (i.e. indexes do not get transferred over with the standard pg_restore). However, it looks like it did in my recent dump/restore and I am ...
4
votes
2answers
71 views

Can we execute an optimal plan instead of a generic one in the first execution of a PL/pgSQL function?

I have a really busy function that I need to optimize the best way I can. This function is just a nested select statement that is requested several times a sec by a legacy application. The indexes ...
4
votes
1answer
98 views

Order result by count of common array elements

Using Postgres 9.4, I'm interested in having an array of integers like user_ids_who_like and provide an array of users (like user_ids_i_am_following) to sort that intersection. Something like: ...
4
votes
2answers
103 views

How to get row_count diagnostic after EXPLAIN ANALYZE in Postgres?

I normally retrieve the row_count of a query by doing GET DIAGNOSTICS row_count (normally within a function). But if I want to run EXPLAIN ANALYZE on a query AND get row_count, I can't see any easy ...
4
votes
1answer
178 views

Why does Postgres ORDER BY seem to halfway ignore leading underscores?

I have an animal table with a name varchar(255), and I've added rows with the following values: Piranha __Starts With 2 Rhino Starts With 1 0_Zebra _Starts With 1 Antelope _Starts With 1 When I run ...
4
votes
1answer
969 views

Creating crosstab() pivot table in PostgreSQL 9.0

I have a rather complicated issue for myself which I hope someone can help me with. I want to use PostgreSQL to enable me to create pivot tables from a geometry-less table. To keep it simple I will ...
4
votes
1answer
68 views

Single table or multiple table design when records have a clear distinction but are queried together?

Let's say I have the following types of recurring data (all types periodically come in). Post (Title, Summary, Text, Timestamp) Image (URL, Caption, Timestamp) Event (StartDate, EndDate, Title, ...
4
votes
1answer
324 views

Best index for similarity function

So I have this table with 6.2 millions records and I have to perform search queries with similarity for one for the column. The queries can be: SELECT "lca_test".* FROM "lca_test" WHERE ...
4
votes
3answers
440 views

Speed up creation of Postgres partial index

I am trying to create partial indexes for a large (1.2TB), static table in Postgres 9.4. My data is completely static, so I am able to insert all data, then create all indexes. In this 1.2TB table, ...
4
votes
2answers
438 views

What is the most efficient way to count the number of rows in a table?

I am using Postgres with the following query: select count(*) from image; The primary key on this table is non-incrementing; it's a unique serial number for the images stored in the table. Our app ...
4
votes
2answers
174 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 ...
4
votes
1answer
467 views

Why is the CTE much worse than inline subqueries

Im trying to better understand how the query planner work in postgresql. I have this query: select id from users where id <> 2 and gender = (select gender from users where id = 2) ...
4
votes
2answers
5k views

Check whether empty strings are present in character-type columns

I have an application that (as part of its logic) trims strings and replaces empty strings with NULL prior to insertion in the database. I guess one way to ensure that this is enforced would be to ...
4
votes
1answer
4k views

PostgreSQL complaining about shared memory, but shared memory seems to be OK

I've been performing kind of intensive schema dropping and creating over a PostgreSQL server, but now complains..: WARNING: out of shared memory ERROR: out of shared memory HINT: You might need to ...
4
votes
2answers
125 views

Removing all columns with given name

I am converting a MSSQL schema to PostgreSQL and in that schema most tables have a column called Timestamp that are of MSSQL timestamp datatype which is effectively rowversion. When inserting records ...
4
votes
2answers
408 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 ...
4
votes
1answer
4k views

How to grant drop table/function privileges to a role in PostgreSQL?

I want to grant drop privileges on all tables and functions (not only those owned by the user) in certain schema of a specific database to a specific role. However, GRANT ALL PRIVILEGES is not enough ...
4
votes
1answer
565 views

RANGE clause error within window function

I'm trying to use postgres' RANGE clause within a window function to dynamically set the window to the past 4 weeks not including the current date. Here is my attempt at it: SELECT ...
4
votes
1answer
3k views

How do I sort the results of a recursive query in an expanded tree-like fashion?

Let's assume you have a nodes tables like this: CREATE TABLE nodes ( node serial PRIMARY KEY, parent integer NULL REFERENCES nodes(node), ts timestamp NOT NULL DEFAULT now() ); It ...
4
votes
1answer
167 views

Non-overlapping rectangles constrained to a boundary

I am trying to model placement of parts on a circuit board. Without any meaningful constraints, my basic schema looks like this: create table part ( part_id bigserial primary key, name text ...
4
votes
2answers
898 views

Equivalents to `ora_rowscn` in other databases?

Do other databases other than Oracle have ora_rowscn (http://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns007.htm) equivalents, or do you have to implement them yourself in your fields, ...
4
votes
3answers
9k views

Select rows, where 3 columns have the same values

I have a structure like this: col0 col1 col2 col3 1 1 1 1 1 1 1 2 1 1 1 3 1 2 1 1 1 2 3 1 2 3 4 5 2 3 4 9 I now want to select ...
4
votes
1answer
130 views

How to get the information of the optimizer of PostgreSQL?

I am curious about the information like total optimization time the time spent on each stage of optimization the number of alternative plans and the cost of each alternative plan.
4
votes
1answer
2k views

Is replication from SQL Server 2008 to PostgreSql possible?

Is it possible ? Sql Server as publisher(master) and PostgreSql as slave(subscriber) ? Any type of replication really.