PostgreSQL 9.3 : 2013 release of PostgreSQL

learn more… | top users | synonyms (1)

1
vote
0answers
8 views

Optimizing postgres row overlap constraints involving UUIDs and GIST

For various reasons all my tables have UUIDs as primary keys and I need to exclude duplicate rows where the combination of area_id, user_id, and finished_at <-> started_at window. The only way I ...
2
votes
1answer
35 views

PostgreSQL CREATE INDEX memory requirement

I've got 6GB of RAM on my laptop and for a test I'm creating a 50mil-rows table in PostgreSQL 9.3. I then want to create an index on the table. The table and the resulting index together (or twice ...
0
votes
1answer
42 views

Reassigning surrogate keys?

I have a few tables with surrogate keys (SERIAL PRIMARY KEYs unique to each tables). These tables are used by other tables, with references to these keys. My usage of these is quite simple : select ...
0
votes
1answer
15 views

Postgresql 9.3: How to find the name of a domain constraint?

Consider the following scenario on PostgreSQL 9.3: -- Create a domain with no constraints => CREATE DOMAIN dom_zipcode AS text; CREATE DOMAIN -- Add a named constraint => ALTER DOMAIN ...
1
vote
1answer
26 views

Slow query performance on simple select

I have 2 tables: Organization (2.500.000 rows) City (35.000 rows) 'Organization' has 'city_id' set as foreign key to 'cities'.'id': Organization: ALTER TABLE public.organization ADD CONSTRAINT ...
2
votes
0answers
33 views

ST_GeomFromGeoJSON() Causes postgres to crash

I'm running into a strange problem which I can't solve. The server crashes when executing some postgis related queries. After some debugging, using the examples provided by postgis, it appears the ...
2
votes
1answer
36 views

Marking duplicate records

I have a list of things, some of which are duplicates. create table things ( id int primary key, title text not null, author text not null, duplicate_of int null references things(id) ); ...
0
votes
1answer
20 views

What determines if a bytea has 1 or 4 extra bytes?

When inserting a BYTEA string in PostgreSQL the documentation says that such a string will occupy 1 or 4 bytes plus byte string length. The question that I can't seem to find the answer to is what ...
0
votes
1answer
7 views

Remove all values of a key in PostgreSQL hstore

How do I delete/remove the value of a postgresql hstore column? I've got a table named pages and a column named settings - I need to remove the read_only value (basically, set it to empty string or ...
0
votes
1answer
44 views

copy data from public schema to another schema in postgresql

I'm creating a multi-tenant RoR app using PostgreSQL 9.3.5 running on linux (Ubuntu 14). I'd like to copy the data from my public schema to another schema. I tried: pg_dump -U my_username -h ...
0
votes
0answers
29 views

PostgreSQL queries slower than before?

I am using PostgreSQL 9.3 server on my production server. Everything had been working fine for the past couple of months. But since the past week or so, PostgreSQL seems to have becomes very slow with ...
0
votes
1answer
45 views

Error: /var/lib/postgresql/9.3/main is not accessible or does not exist

I have a PostgreSQL db that was running on my server. I tried to do a db_dump and got a weird error message. I eventually thought I would just restart PostgreSQL. However its now stopped and won't ...
0
votes
1answer
26 views

why pg_restore ignores --create ? Error: failed: FATAL: database “new_db” does not exist

I am trying to run following command: sshpass -p "pass" ssh x@1.2.3.4 "pg_dump -Fc -U foo some_db" | pg_restore --create --dbname=new_db I get: failed: FATAL: database "new_db" does not exist
1
vote
1answer
21 views

script for restore of postgres database localy from dump given on stdin - ambiguous redirect

Runing this command: sshpass -p "pass" ssh x@1.2.3.4 "pg_dump -U foo some_db" runs perfectly fine. It returns on stdandard output the dump of db. Then I would like to restore new_db based on dump ...
3
votes
2answers
85 views

Why postgres is using an odd index with no relation to the query

(Debian 7, Postgres 9.3, dedicated machine with huge cache) I have one big table called process_data (14gb) and another tiny lookup table called process_location. I'm doing a query between these two ...
1
vote
1answer
53 views

Deadlock detected on CREATE TABLE in PostgreSQL

I'm creating multiple tables asynchronously when I get deadlock detected. Both tables (Table A & B) have FK to one table (Table C) and I get deadlock on relation to Table C. Scripts CREATE TABLE ...
0
votes
1answer
18 views

Order specific columns in json_agg

I want to sort the rows inside the json_agg function. I found this: PostgreSQL : select columns inside json_agg Which gave me a solution to selecting specific columns for json_agg, but now I want to ...
0
votes
1answer
17 views

Postgresql 9.3: Add Unique constraint using an existing unique index

I would like to add a unique constraint to a DB field in one of my production databases. Unfortunately, the following statement is blocking: ALTER TABLE package ADD CONSTRAINT unique_package_id ...
1
vote
2answers
46 views

Memory usage of PostgreSQL

I'm using postgreSQL 9.3 database and I have increased the shared_buffers and effective_cache_size in the settings. Will postgreSQL always take this amount of memory or only when needed? I'm ...
1
vote
1answer
42 views

Can't run digest() on PosrgreSQL 9.3 on RDS

Following the advice given on another answer, I tried using the digest() function on PostgreSQL 9.3 on AWS RDS: devdb=> SELECT digest('blah', 'sha1'); ERROR: function digest(unknown, unknown) ...
0
votes
1answer
16 views

Postgres Grouping of Like Results

Our database is running on Postgres 9.3. We have a table called notifications. Notifications contains id, type, triggering id, source id, and some other information. What I am trying to do is select ...
0
votes
1answer
46 views

PostgreSQL + PG_Monz (Zabbix) DB size too large > 1073741824

I an new to Postgres (9.3 on Ubuntu 14.04) monitoring and I have been using Zabbix/PG_monz. I am seeing errors that my database has "too many temp bytes" and that my database is "too large". My ...
0
votes
1answer
17 views

Compare table content from different PostgreSQL 9.3 DBs

The problem How do I efficiently and idiomatically compare the content of two tables, each in a different DB? I have 3 DBs (the classic dev, stg and prod). Each DB has a few tables which should be ...
3
votes
2answers
50 views

Transactions within a Transaction

What behaviour would PostgreSQL display if for example the script below were called BEGIN; SELECT * FROM foo; INSERT INTO foo(name) VALUES ('bar'); BEGIN; <- The point of interest END; Would ...
0
votes
0answers
32 views

Combine 3 tables

I have 3 tables, they have in common row index, date, profile. These 3 tables each have partitions based on profile. I need to combine the data from those tables to produce a report at the moment I ...
0
votes
0answers
50 views

How to install Slony for 64-bit PostgreSQL 9.3 on Windows?

I have installed 64-bit version of PostgreSQL 9.3.5 from EnterpriseDB installer. Now I want to install Slony-I through Application Stack Builder, but it offers only 32-bit version of Slony, ...
0
votes
1answer
21 views

Insert any type of text in a column

How can I insert any type of text into a column of type text? At the moment I am using this: $$description$$ But a lot of my descriptions contain $ and other signs, so the insert will fail. Do I ...
1
vote
1answer
38 views

“Wrong affix file formate for flag” while loading dictionaries from LibreOffice in PostgreSQL 9.3

Trying to get good TEXT SEARCH results out of PostgreSQL in English, German and Dutch made that I downloaded proofing tools of LibreOffice (tried of version 4.3 and 3.3) and iSpell. I placed the files ...
0
votes
0answers
49 views

Optimizing query - understanding explain

I've got a query (partly generated by django orm, but can be modified). For some reason, It's very slow (between 1.5 -3 seconds, without having lots of data on the system) . I've got an "explain" ...
0
votes
1answer
22 views

Postgresql two tablespaces one on SSD and one on SATA III HD

Continuing my previous question PostgreSQL maximize performance SSD the DBA SE community has given me efficient advice on optimal settings for PostgreSQL on SSD. The problem is that I will define two ...
0
votes
0answers
36 views

Recursive query

I am trying to calculate the running difference between 1 row and the row before. To make it a little easier to myself I created a properly grouped representation of the data in a temp table before ...
1
vote
2answers
34 views

Postgres top-k with ties

Assume I have the following DB table with 3 integer fields. A | B | C 1 | 2 | 3 1 | 2 | 4 1 | 3 | 1 2 | 4 | 2 2 | 4 | 3 When I do: SELECT * FROM dbTable ORDER BY A,B LIMIT 1; I get 1 | 2 | 3 ...
0
votes
0answers
8 views

Creating a rule increases load disproportionally

I hope the group can forgive the vagueness of the question: In Postgres-9.3 we created a rule to be executed after an update on a table's values. The rule inserts values to another table. We tested ...
0
votes
1answer
23 views

Have multiple foreign keys or one

I have 3 table: brand: brand_id(pk), name product: product_id(pk), name brand_product: brand_product_id(pk), brand_id, product_id And I have a report table, at the moment it looks like this: ...
1
vote
1answer
42 views

Linking first and second level composite primary keys

I am trying to create a new database with a hierarchical structure of two many-to-many tables using composite keys. Unfortunately the creation of tables fails stating that the foreign keys do not ...
0
votes
0answers
29 views

Bucardo doesn't do fullsync

I have a 2 computers, one of them called master, and the other called slave both using PostgreSQL 9.3.5, but has different OS (32-bit and 64-bit) I have installed and setup the bucardo on the server ...
0
votes
2answers
26 views

Best way to update the content of large lookups tables

In our application we have a about 20 tables that contain lookup information (read only data that the application doesn't modify). Nightly we'd like to update this information by deleting all the ...
1
vote
1answer
67 views

PostgreSQL 9.3 Amazon RDS gives error while using UUID-OSSP extension for generating uuid

I have created DB instance on RDS and used below function it gaves error while with same postgreSQL version on local development machine works fine. create or replace function uuid() returns uuid as ...
1
vote
1answer
43 views

Change existing column in PG to auto-incremental primary key

I have a database in Postgresql, which was migrated from SQL Server (only data). On SQL Server, a table from this database has these columns: measure_id datum measure where measure_id is ...
2
votes
1answer
44 views

(How) can I return the most specific row on a join?

I'm trying to work out a query that can parse through a table of prices (and exceptions to those prices) to yield the applicable price for a product, ideally using a join. I'm on PostgreSQL. For ...
0
votes
1answer
35 views

Postgres table contains 1 column, and that column is PK. How to insert new record?

In my application, I have an abstract base class, and 2 derived classes. The number of derived classed will probably not increase. If it does, it won't be more than a few. The class structure looks ...
0
votes
1answer
49 views

Postgres columns of latitude and longitude from varchar to numeric

I have a table with latitude and longitude columns and they are in varchar. I have to convert them to numeric because that is how the fields are in the master table. Is there a way to change all the ...
1
vote
1answer
52 views

Upgrading 8.1 to 9.3 - using statement log and WAL to check compatibility?

We're migrating from postgres 8.1 to 9.3, since we need streaming replication badly. All the application developers have deserted the company a long time ago, way before my time here, so there is ...
0
votes
2answers
52 views

Postgresql. Generate sets of random integers

With this command I can generate 16384 random integers between 1 AND 200,000. SELECT generate_series (1,16384),(random()*200000)::int AS id I want to generate 10 sets of such integers. Each set ...
2
votes
2answers
297 views

PostgreSQL maximize performance SSD

I will have a huge PostgreSQL 9.3 database with many tables with more than 100M entries per table. This database will be basically be read-only (once I fill all the necessary tables and build the ...
1
vote
2answers
174 views

Slow querys on billions-rows-table // index used

Since I'm a young developer and not realy skiled in using databases (PostgreSQL 9.3) i ran into some problems with a project, where i realy need help with. My project is about collecting data of ...
0
votes
0answers
20 views

when install pgpool is also needed install hearbeat and watchdog

I am trying to get the PostgreSQL services High Availability (HA) and then I am using pgpool-II. When I install and configure pgpool, the WATCHDOG section seems to doesn't works, then I have a ...
0
votes
0answers
32 views

could not connect to pgpool server

I am configure my pgpool server to allow access from: host all all 0.0.0.0 trust The service pgpool is running: user@pgpool:~$ sudo service pgpool2 status * pgpool-II is running. ...
0
votes
0answers
36 views

Using a Virtual IP for pgpool instances doesn't work

I did a configuration of my pgpool instances, I am using a virtual ip address, delegate_IP = '192.168.1.100' <-- floating IP address ifconfig_path = '/sbin' if_up_cmd = 'ifconfig eth1:0 inet ...
0
votes
1answer
18 views

Using pgpool and PostgreSQL in differents hosts.

I want to use pgpool but I did some research and I have a doubt, if neccesary install PostgreSQL and pgpool in the same machine? When I did my first configuration and installation in a same host. But ...