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
11 views

How can I automatically add a foreign key to a table on select which is stored in an associated other?

I have a database with some tables among wich these 3 : organizations organization_id PK teams team_id PK team_organization_id FK users user_id PK teams_users tu_id PK ...
0
votes
1answer
29 views

Translating MySQL query to PostgreSQL

I have a query which works fine in MySQL, however when I try to recreate the same query in PostgreSQL (same DB structure) I just get a syntax error. MySQL: SELECT ticket.queue_id, queue.name, ...
1
vote
1answer
27 views

Upsert statement never completes, infinite loop?

Edit: The function works, tested it by creating a small table. So the problem must be with my input, but I still can't see what the issue is - unless the function is just incredibly slow. It's not a ...
0
votes
0answers
11 views

listen_addresses does not respect postgresql.conf

I have PostgreSQL 9.3, and I'm trying to set it up on CentOS. One part that has been driving me nuts is the listen_addresses setting. I set it to * inside postgresql.conf: listen_addresses = '*' ...
0
votes
1answer
7 views

psql: could not connect to server: No such file or directory

The error in its entirety reads: psql: could not connect to server: No such file or directory. Is the server running locally and accepting connections on Unix domain socket ...
0
votes
1answer
18 views

Bulk insert in multiple tables

I have two tables on which I perform some bulk inserts: key: key_id(pk), key_name related_key: key_id(fk), related_key_id First Insert: values_data = "($$key_1$$), ($$key_2$$)" INSERT INTO key ...
0
votes
0answers
19 views

API on top of postgresql

I'm looking for one or several API's that can be put on top of postgres to make data input more dynamic. What I'm looking for is something that can handle a wider range of protocols and differently ...
1
vote
0answers
11 views

create trigger hangs

I have a trigger function that just copies inserted values to another table. Then, each night I need to drop and recreate this trigger for some export operation. --trigger function CREATE OR ...
0
votes
1answer
18 views

Separation of users columns in another table

I've got a quite a lot of columns on the users table and I have been cleaning it up recently. Now since I'm searching over the users I figured it would improve performance if I separated verification ...
0
votes
3answers
37 views

PostgreSQL equivalent of MySQL query variables?

Is there a straightforward way to adapt these types of MySQL queries to PostgreSQL: setting variables in MySQL like set @aintconst = -333 set @arealconst = -9.999 It seems not. Assigning ...
0
votes
1answer
9 views

Type of an id field that references another id field

I'm on postgresql. I have a table user (id serial not null) and another table client(id) i need to create a : foreign key client(id) references user(id). What the client(id) type should be? Int or ...
0
votes
2answers
19 views

postgresql cannot drop a table when autovacuum holds lock on it

Autovacuuming process holds RowExclusiveLock and ShareUpdateExclusiveLock on the table, which I want to drop. I read that autovacuum should be canceled automatically, when submitting DROP TABLE ...
0
votes
1answer
40 views

How do I maintain high INSERT-performance on PostgreSQL

I'm working on a project which is parsing data from measurement files into a Posgres 9.3.5 database. At the core is a table (partitioned by month) which contains a row for each measurement point: ...
0
votes
0answers
27 views

Storing DB credentials in an application source code?

I am making an app which connects to a DB, and the app will have plenty different userslogins. Is it a good design choice to have a specific DB login/password for the application, using which the ...
0
votes
0answers
27 views

Speed up sorting in this query

I'm trying to optimize the following plan and noticed that PostgreSQL spends a lot of time (relatively speaking) on sorting before the merge joins. Is there a way (using indexes, cluster the table, ...
0
votes
1answer
27 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
1answer
57 views

What is the best approach to process huge amount of data insertion efficiently

I'm dealing with a HUGE amount of data entry. I need to know the best practice to insert/update table records after new entry inserted in another table. By huge i mean we get more than 2 million ...
1
vote
1answer
28 views

Unique constraint to enforce max one null per item

I am using PostgreSQL 9.3. Suppose we are manufacturing some items of certain types, at any moment we are producing at most one item of a certain type. The following table captures our history of ...
1
vote
0answers
9 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 ...
1
vote
3answers
31 views

Query to return output column names and data types of a query, table or view

Is there a PostgreSQL query or command that returns the field names and field types of a query, table or view? E.g., a solution if applied to simple SELECT query like SELECT * from person should ...
0
votes
2answers
27 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 ...
0
votes
1answer
20 views

How to predict the growth of a PostgreSQL database

I'm looking for a way to predict growth of the database given its fields, indexes, constraints (as foreign key), number of lines, and so. I searched on Google, but I found only means to determine the ...
0
votes
0answers
24 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. ...
0
votes
1answer
23 views

Useful guide & FAQ for Application+SQL [on hold]

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 ...
2
votes
3answers
37 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 ...
1
vote
2answers
33 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" ...
1
vote
2answers
23 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
12 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
32 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 ...
1
vote
0answers
57 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 ...
0
votes
0answers
25 views

Server-Hoster for game [on hold]

Im currently working on a Multiplayer-Strategy-Game (C++ 11). It's pretty much like a mix of Browsergames like Travian and Total War games. I have a hexagonal grid, on which your armies move. A game ...
2
votes
2answers
76 views

GROUP BY one column, while sorting by another in PostgreSQL

How can I GROUP BY one column, while sorting only by another. I'm trying to do the following: SELECT dbId,retreivalTime FROM FileItems WHERE sourceSite='something' GROUP BY seriesName ...
0
votes
0answers
5 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 ...
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 ...
2
votes
0answers
20 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 ...
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
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 ( ...
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 ...
-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
2 views

Determining which database instance makes biggest IO

Assuming that I have a dedicated server on which I am running multiple instances of mysql and postresql servers. How without iotop determine which instance in particular time (proc/pid/io shows data ...
2
votes
5answers
75 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
22 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 ...
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 ...
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 ...
2
votes
0answers
5 views

Active record relation AND filtering [duplicate]

(Edit: since reposted to dba.stackexchange.com and answered there). Suppose I have 3 models: Post, PostCategory and Category. I'm implementing some kind of page with filters where I can filter posts ...
2
votes
1answer
45 views

How to optimise window queries in postgres

I have the following table with approximately 175k records: Column | Type | Modifiers ...
0
votes
1answer
37 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
0answers
6 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
1answer
9 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 ...
2
votes
2answers
58 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 ...