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
1answer
31 views

What's the difference between INSERT … SELECT and SELECT INTO

Let's say I have a table item and an other one versionned_item. From time to time, I want to copy data from item to versionned_item. What I did is WITH subset AS (SELECT ...) INSERT INTO ...
0
votes
0answers
10 views

Multischema view for postgres , querying multiple databases. [duplicate]

Im managing SaaS app where each customer has a separate database (Postgres 9.3) . I need to figure out the best way to aggregate and query this data ( READ-only ). I know postgres has problems with ...
1
vote
0answers
82 views
+100

Multischema view for postgres , querying multiple databases

Im managing SaaS app where each customer has a separate database (Postgres 9.3) . I need to figure out the best way to aggregate and query this data ( READ-only ). I know postgres has problems with ...
1
vote
0answers
13 views

How to restore everything (including “postgres” role) from pg_dumpall backup

I have a postgres 9.1 server running and I do backups with pg_dumpall -c -f /nfs/backup/fulldump.sql I'm testing restoring the backup to another server with psql -f /nfs/backup/fulldump.sql ...
0
votes
2answers
30 views

How to implement a dynamic string into a (prepared) sql statement?

I have some spatial datasets which are available in a open source metadata catalog software GeoNetwork and in a PostGIS database. The problem is that the student who created the database used ...
2
votes
3answers
40 views

Constrain a postgres timestamp to “End of day”?

Given a table named bananas and a timestamp without time zone column named end_time and some psql clients have set timezone to 'UTC' and other psql clients have set timezone to 'US/Eastern' and the ...
3
votes
2answers
184 views

Query planner thinks doing a sequential scan on a very large table is faster than using an index?

I've got a query that selects the most favorited photo for each of the last 12 editions for a particular domain (this is just a segment of a larger query, but the problem seems to stem from here): ...
0
votes
1answer
26 views

Useful guide & FAQ for Application+SQL [closed]

Of course when you are making your first application for fun it doesn't matter that much how you deal with things. But when you need everything to be up to the current standards you don't always know ...
0
votes
0answers
29 views

DB design, when to split out into schemas

I am building a storage solution to store important themes/concepts referenced in books. At a high level the system will contain one or more catalogs and each catalog will contain one or more books. ...
1
vote
2answers
34 views

PostgreSQL get first letter of each word (except…)

I don't think I drank enough coffee today, but stuck on this for some reason. Trying to get the first letter of each word, except a few stopwords. Example "The United States of America" -> "USA" ...
0
votes
1answer
35 views

hierarchical retreival of records in postgresql

I am using postgresql-9.2 . I have a table of products which stores the information about the products and its child table is content table , which contains the products linked to it. PRODUCT MASTER ...
2
votes
1answer
66 views

PostGIS: Cutting Holes in Self Geometry

I have a table of geometries in PostGIS, some of which overlap. When I display the geometries, this has the unfortunate consequence of blending the two stacked geometries together and producing an ...
2
votes
2answers
25 views

How to tell which indexes are not being used

Postgresql 9.3 Debian 7 I have a lot of huge indexes in a legacy database I'm trying to optimize. Thinking about dropping all the useless ones, but how can I tell how often they are used and if they ...
0
votes
0answers
13 views

Hstore or multiple columns for this scenario?

I have a table called Template which needs quite a lot of attributes in order to describe the template itself. Most of the attributes are boolean, such as: template_requires_images, ...
0
votes
1answer
173 views

Taking hot backup of slave node in postgres (master-slave config with repmgr)

I have a postgresql setup with master-slave configuration using repmgr. I'm struggling with taking a consistent snapshot backup (pg_start_backup) of the slave node, this is due the fact that the slave ...
2
votes
2answers
61 views

Adding standard set of constraints (rules) to PostGIS `raster` type column

Following on from my related prior question, let me elaborate this topic a bit. raster2pgsql is a raster loader executable that loads GDAL supported raster formats in PostGIS. It has a -C flag ...
4
votes
2answers
3k views

Preventing PostgreSQL from starting on boot in Ubuntu

I am using PostgreSQL 9.2 and it runs automatically when I start the system (Kubuntu). How can I avoid this? I need to start it manually, and I don't want it to start unattended because I need to ...
1
vote
1answer
145 views

Trouble with installing additional modules on Postgresql 9.1 on ALT-Linux

I'm trying to create extension under postgresql 9.1 (Linux): CREATE EXTENSION pg_trgm; but got error: ERROR: could not open extension control file "/usr/share/pgsql/extension/pg_trgm.control": No ...
-1
votes
2answers
30 views

Indexing into two “buckets”

I'm creating a database with PostgreSQL that has an integer field called "delay". All of my queries on this column will be checking if the value of this column is less than or equal to 0, or greater ...
0
votes
1answer
25 views

Postgresql tries to connect to fe80::1, permission denied

I upgraded a couple of days ago to IPv6 on my local test machine and since then I've been experiencing some issue with connecting to my local PostgreSQL server. I set the host to 'localhost', which ...
0
votes
1answer
20 views

Row-level subquery caching?

I have a query that uses a CTE which contains millions of rows. I plan to call this query many times, and the CTE returned each time will have most of the same rows. Is it possible for me to cache the ...
0
votes
0answers
6 views

Using range types (with GiST index)

Say I have 2 tables with a PostgreSQL range column: CREATE TABLE events ( id serial, during tsrange ); and CREATE TABLE storms ( id serial, during ...
2
votes
0answers
21 views

Stack Depth Limit Exceeded (After delete trigger)

CREATE TABLE parent ( parent_id VARCHAR(30) PRIMARY KEY ); CREATE TABLE child ( parent_id VARCHAR(30) REFERENCES parent ON DELETE CASCADE, child_id VARCHAR(30) PRIMARY KEY ); CREATE OR ...
1
vote
1answer
18 views

Index on integer column not helping. Help? (EXPLAIN ANALYZE output inside)

The table market_trades has an integer column market_id which takes values from 1 to 5. This seems like a pretty straightforward query to get the number of trades on each market: WITH markets AS ( ...
0
votes
1answer
17 views

Import binary database file into postgres

I have a quite newbie question that I do not know if is possible or not: I had to format my computer, and the only way to keep my postgres database was accessing to data with my folder browser ...
1
vote
1answer
13k views

How can I convert a string to a double precision in PostgreSQL?

How can I convert a string to a double precision in PostgreSQL ? I tried something like : update points set latitude2 = cast(latitude as double) ; where latitude is a string and latitude2 is a ...
1
vote
1answer
36 views

Optimizing a query on a large table (EXPLAIN ANALYZE result inside)

I have a table market_trades with a timestamp column: api_production=# \d market_trades; Table "public.market_trades" Column | Type ...
2
votes
5answers
78 views

Make IN clause behave like AND clause

I've asked the same question on stackoverlow, but got no answer, so that's why I'm asking it here (I've read that simple sql questions should be asked at SO, but I have no choice). Suppose I have 3 ...
1
vote
1answer
2k views

Installing and configuring postgresql 9.3 on ubuntu 13.10 using puppet

I'm having some trouble getting postgresql[1] to install with puppet[2] on a blank ubuntu box. I need to install postgresql 9.3 on Ubuntu 13.10. Since postgresql 9.3 is apparently not in the official ...
2
votes
1answer
5k views

Postgres client copy (\copy) command doesn't have access to a temporary table?

I'm generating a list of SQL commands to export some data that I ultimately run using psql -f. The queries all get the same subset of data, so I thought I'd factor the qualifications out and put a ...
2
votes
1answer
46 views

How to optimise window queries in postgres

I have the following table with approximately 175k records: Column | Type | Modifiers ...
11
votes
5answers
3k views

How to efficiently copy millions of rows from one table to another in Postgresql?

I have two database tables. One contains hundreds of millions of records. Lets call that one history. The other one is calculated on daily basis and I want to copy all of its records into the history ...
0
votes
1answer
465 views

Configuring Postgres 9.2 streaming replication with Redhat Clustering Suite for High Availability

I'm in the process of configuring postgresql 9.2 streaming replication with Redhat Clustering Suite for high availability. I have 1 master and 1 hot standby configured to replicate the data from ...
0
votes
1answer
39 views

How to create a Postgres trigger to ensure field is non-updateable

I'm trying to create a Postgres trigger to ensure after a column's value has been set, it cannot be updated (basically make it readonly). So far I've come up with the following draft trigger. 1) ...
0
votes
1answer
13 views

Postgres-9.1 having more Idle connections

The connections were created 2 days before. Can I kill the process manually by using the below query? select pg_terminate_backend(procpid) from pg_stat_activity where datname = 'mydb' and procpid ...
0
votes
1answer
17 views

PostgreSQL SQL syntax - JOIN and substrings

I have a single table of hostnames and NFS exports, some but not all of which are related. Example entries: HOST VOLUME ----- ---------- host1 vol1 host1 vol2 host1 ...
1
vote
1answer
24 views

PGAdmin III Server Status Error: Could not read directory “pg_log” No such file or directory

Running PostgreSQL 9.3 on Ubuntu 14.04 and pgAdmin III 1.18.1 on Windows 7. I tried Tools->Server Status and I get an error: "Could not read directory "pg_log" No such file or directory" I ...
2
votes
2answers
75 views

Tuning Postgres for Single connection use? Or is postgres wrong tool?

Any rules of thumb for work_mem, maintenance_work_mem, shared_buffer, etc. for a database that DOESN'T anticipate concurrent connections and that is doing lots of aggregate functions? I'm a social ...
2
votes
1answer
440 views

MATCH FULL vs MATCH SIMPLE

I've noticed a MATCH SIMPLE or MATCH FULL option in phpPgAdmin, but I can't find a description in the docs. How do they function? The default is set to MATCH SIMPLE.
0
votes
1answer
14 views

How to access pg_settings from separate connection

I am trying to access the pg_settings table from a separate connection belonging to a legacy application. I know how to identify the connection's procid from pg_stat_activity, but I am unsure how to ...
5
votes
3answers
14k views

Granting access to all tables for a user

I'm a new to Postgres and trying to migrate our MySQL databases over. In MySQL I can grant SELECT, UPDATE, INSERT, DELETE privileges on a low privileged user and enable those grants to apply to all ...
1
vote
0answers
35 views

How to measure bandwidth requirements for MySQL, Postgres, …?

TLTR: how to configure MySQL/Postgres to get a fair measurement of the I/O requirments for analytical queries? Hello, for certain database and especially database hardware experiments for want to ...
1
vote
1answer
44 views

How to insert into view through GUI in pgAdmin?

I have some updateable views for inserting the data. The reason is not just "security layer", inserting into some 10 tables directly would be a pain without the view with INSTEAD OF trigger. In ...
0
votes
1answer
42 views

Implementing Permissions Database structure on a PHP site to restrict the Access in Multiple levels

I am working on a PHP site that shows the business of a Vehicle Showroom company in which we have 3 users as, Admin(Owner) = Here Admin Have all Access by default(View,Edit,Delete to all) Super ...
0
votes
1answer
595 views

PgPool 2 with HA Proxy

So, I'm looking to add high availability to a postgresql server. I've looked into some tutorials on the web but, haven't been able to find exactly what I'm looking for. E.g.: PostgreSQL ...
1
vote
1answer
10 views

Create user to monitor database activity on Postgres

Postgres 9.3 Debian 7 I want to create a monitor user called monitor that the only permission is to run queries to monitor all the database activity. I created the user and it can select from ...
1
vote
2answers
81 views

Creating a unique constraint on a PostGIS 'raster' type column

I am using the following command to add constraints to one of the raster image in PostGIS-2.1.3 (PostgreSQL-9.1.14). ALTER TABLE schema1.table1 ADD CONSTRAINT enforce_scalex_rast unique (rast); But ...
1
vote
2answers
57 views

Limiting read access in postgres to few rows

We have a requirement where we want to limit the maximum no. of rows that a select query can return when its done using a particular login. So basically we would like to append a limit clause to any ...
3
votes
5answers
95 views

Find all values not present for other values of other column

Let's suppose I have a table with integer columns a and b (each pair (a,b) is unique) and following data: a | b ------- 1 | 2 1 | 3 1 | 4 1 | 5 2 | 1 2 | 3 2 | 4 3 | 1 3 | 3 Now I need to ...
1
vote
1answer
30 views

Joining timestamped records with the most-recent-prior records from another timestamped table

Say I have 2 tables: Table: sales timestamp timestamp without time zone amount numeric Table: prices timestamp timestamp without time zone price numeric How can I get a ...