PostgreSQL 9.3 : 2013 release of PostgreSQL

learn more… | top users | synonyms (1)

0
votes
1answer
17 views

PostgreSQL determine column type when data_type is set to ARRAY

I came across an oddity where PostgreSQL sets the data_type to ARRAY in the information_schema.columns table. I did the following query to gain some insight: SELECT * FROM information_schema.columns ...
1
vote
1answer
9 views

Upgrade PostgreSQL 9.1 to 9.3 and server no longer starts

Technically, I didn't upgrade from 9.1 to 9.3, I built a new server with Ubuntu 14.04 and used my old 9.1 config files. PostgreSQL wouldn't start: * Starting PostgreSQL 9.3 database server * The ...
0
votes
0answers
31 views

PostgreSQL Performance - Many Large Databases

I run an internal (development) database server containing 25 databases each ~71GB in size. There are usually between 50 and 100 active connections at any given time. What can I do to help improve ...
0
votes
0answers
31 views

Can I manage NULL = 'SomeDefault' in the database so applications and SQL don't have to remember this optimization?

I have a table with lots of default values, and want to store very common values as NULL to save space, without forcing my SQL statements or application logic to become overburdened with conditionals. ...
1
vote
1answer
32 views

PostgreSQL: Foreign key on string column

I'm currently in a process of redesigning a legacy database. One of the tables primary key is a varchar(254). I need to create a table relating to it. So for example: create table "Item" ( ...
0
votes
0answers
27 views

Maximum sum() of overlapping ranges

Essentially my question is: how does one do aggregate operations involving overlaping ranges in Postgresql 9.3 (or 9.4)? The specific problem I have at hand is that given a range, I want to find the ...
0
votes
0answers
20 views

PostgreSQL and array of custom type

One of the table I have looks like this CREATE TABLE orders_suppliers_contacts ( supplier_id bigint NOT NULL, name character varying(64) NOT NULL, phone character varying(18), notes character ...
1
vote
2answers
42 views

Slow query when adding additional where clause

Here's my setup (Postgres 9.3) Posts project_id Messages post_id kind updated_at I'm trying to get the most recently updated 100 messages that belong to posts (on a specific project) that are ...
0
votes
2answers
36 views

PostgreSQL: Use column with hash based on other columns as unique index?

My previous problem is still not fixed: We cannot set a unique index because NULL values are allowed... we use this table in our database: CREATE TABLE offer ( offer_id serial PRIMARY KEY , ...
1
vote
2answers
43 views

Return the id after insert or select

I want to build a function which will insert an email if the email value doesn't exist in the table and return the email_id of the row. How can I do this? Also how can I return the id if the email was ...
0
votes
0answers
66 views

Upgrade Postgres 9.3 to 9.4 after Ubuntu 14.04 to 14.10 upgrade

I've just recently upgraded my Ubuntu install from 14.04 to 14.10. This automatically pushed Postgres 9.4 onto the system. I'm trying to migrate my cluster like this: sudo pg_dropcluster 9.4 main ...
0
votes
0answers
21 views

Using Code to Access Database Dump via pgAdmin

I looked over this question on how to import a data dump using pgadmin and found the following code: CREATE OR REPLACE FUNCTION db_to_csv(path TEXT) RETURNS void AS $$ tables RECORD; gmdeclare ...
0
votes
1answer
29 views

PostgreSQL and MONEY data type for currency values

For a project, I created a table with a column price MONEY NOT NULL column. And I thought it would handle decimals properly, unlike a floating number (i.e. IEEE rounding issues), but I end up having ...
1
vote
0answers
35 views

postgresql 9.3 with Ubuntu 14.04.1 LTS throw 's ERROR: invalid page in block 105 of relation base/16385/17407

This is Ubuntu 14.04.1 LTS with postgresql 9.3 in production, Now i got this below error's in log. # tail -f /var/log/postgresql/postgresql-9.3-main.log 2015-01-08 14:10:16 IST CONTEXT: automatic ...
0
votes
1answer
17 views

Postgres Error for table

I want to create this table, but i can’t, Look this error. CREATE TABLE ATTR( window character varying(64) NOT NULL ); ERROR: syntax error at or near "window" LINE 2: window ...
3
votes
0answers
154 views

Monitoring Amazon RDS with pgtop

I recently migrated a Postgres database to Amazon RDS and I am lacking a good live monitoring tool. I tried pgtop, but I can't get it to work on Amazon. I can see the header with the DB statistics ...
1
vote
1answer
61 views

Should database be denormalized if I need an index across multiple tables

I have a catalog of album and song names. The song has a foreign key to the album table. I expect queries like: SELECT * FROM song s JOIN album a ON a.id = s.album_id WHERE LOWER(CONCAT_WS(' ', ...
0
votes
0answers
26 views

can Pgpool Work With Multi-master replication in PostgresPlus 9.3 AS?

My query is I have One application server and two database servers(multi-master setup with xDB-replication). Now How can i Connect to two different databases servers having different Ipaddress,Port ...
0
votes
0answers
16 views

Postgresql CRUD operation on Single json data row

Actually my requirement is that i have large chunk of json data associated with each id and i have to somehow get the data for my front end by querying through that large chunk . Table structure is ...
0
votes
1answer
37 views

Can not run PostgreSQL after drop table

I've deleted database from PostgreSQL and after this I can do nothing. Here what i receive with createuser $ createuser -D new_user createuser: could not connect to database postgres: FATAL: role ...
0
votes
0answers
27 views

Database design of role permission on resource

I have this database design to manage resources, roles and permissions. role_permission.permission contains an integer as combination of many privileges. The value are power of 2. For example: 2 ...
0
votes
0answers
12 views

Error “Slony-I creation script not avilable ; only joining possible” in SlonyI replication using pgadmin3

I am facing the above issue while Creating a base replication objects like database etc.. As a workaround i have done the changes like editing the options of Binary Paths in Pgadmin Console but no use ...
1
vote
0answers
20 views

pgAdmin-III - Option to load a favorite automatically when opening query window

pgAdmin-III - Query window option Does pgAdmin-III has any option to load a default favorite file automatically when opening new query window? This would be much useful to have commit/rollback ...
1
vote
2answers
69 views

Refresh materalized view incrementally in PostgreSQL

Is it possible to refresh a materialized view incrementally in PostgreSQL i.e. only for the data that is new or has changed? Consider this table & materialized view: CREATE TABLE graph ( ...
0
votes
0answers
19 views

Possible to get log file with only statements sent to Postgres from my app (and not from psql prompt)?

I'm using an ORM in my app and would like to see all statements sent to Postgres so that I can figure out exactly what the ORM is doing. In the Postgres config file I added log_statement = 'all' to ...
1
vote
1answer
29 views

How to Select the First (or last) entry for a specific day

I have a table "AttendanceRecords" with entries like this EntryID UUID PRIMARY KEY, StafID UUID REFERENCES staffmembers(ID) NOT NULL, ArrivalTime TIMESTAMP NOT NULL, DepartureTime TIMESTAMP For one ...
1
vote
1answer
50 views

Performance of single vs multiple column unique constraint

I'm using PostgreSQL 9.3. I want to understand if I have an option to make a constraint unique across the entire table vs. unique across a subset of the table (i.e. by using 2 columns in the unique ...
2
votes
1answer
65 views

Get products of category plus all child categories till leaf nodes

We have a hierarchy which looks this: Now the problem is that most products are only connected with 1 category, which is most of the time the lowest level. I am trying to get ALL products below a ...
0
votes
0answers
11 views

Sum of nested group query

I'm very new to SQL, so please bear suggest improvements anywhere. I have a PaymentsReceived table like this (simplified to only what is relevant) AccountID refrerences Accounts(ID) PaymentDate ...
0
votes
1answer
27 views

Initialize, Start Server and Create Database in Single Script

I am writing a provision script for a dev environment with the following: sudo su - postgres <<EOF cd ~/data pg_ctl -D . initdb pg_ctl -D . -l run.log start createdb mydb pg_ctl -D . stop EOF ...
5
votes
1answer
71 views

Sort by number of related rows in referencing table

Let there be two tables: Users id [pk] | name --------+--------- 1 | Alice 2 | Bob 3 | Charlie 4 | Dan Emails id | user_id | email ----+---------+------- 1 | 1 ...
0
votes
1answer
30 views

Can I use `on delete set null` on a compound foreign key which contains a primary key column?

The docs say: Restricting and cascading deletes are the two most common options. RESTRICT prevents deletion of a referenced row. NO ACTION means that if any referencing rows still exist when the ...
0
votes
0answers
45 views

How to make pg_dump skip extension?

This is on 9.3 but I can remember simillar things happening since 7.x. So I create database and install plpgsql extension into it. Later on I create a pg_dump and before restoring it into databse I ...
3
votes
1answer
53 views

Counting repeated occurences for a group of fields

I am trying to count the continuous repeated value for a field in order to obtain the maximum repeated occurrences for each person in the table. eg: Time person Result 08:12:36 ...
2
votes
0answers
62 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) ...
0
votes
1answer
30 views

Error during pg_restore. “text search dictionary ”unaccent“ does not exist”

I'm trying to move the data between servers. I recreated the whole database structure, checked that same users exist and unaccent extention that we use is enabled for all schemas of the target ...
1
vote
0answers
28 views

Postgresql service run failure

Trying to setup replication through the wal shipping using Postgresql 9.3 x64. Used this instruction from youtube. The problem is - the slave postgresql service won't run. This is what in logs ...
0
votes
1answer
12 views

Query function won't take rows with given default value

I have only recently delved into the depths of PostgreSQL and have finally hit a snag. There are 3 tables, Notes, Tags, and Notes_Tags (Many to Many). Some Notes do not contain tags, but still need to ...
3
votes
1answer
41 views

How do you truncate a `date` (not `timestamptz`) to month?

Postgres has date_trunc which operates on timestamp or interval, and: Values of type date and time are cast automatically to timestamp or interval, respectively. In other words we can use ...
1
vote
2answers
65 views

How to calculate the cumulative difference between rows in Postgres?

I am in need of calculating the difference from the cumulative values for the previous day in Postgres based on session_id. Example as mentioned below: Date session_id upload_usage ...
1
vote
1answer
54 views

How to remove object from json array?

My table: CREATE TABLE items ( id BIGINT PRIMARY KEY NOT NULL, name VARCHAR, images json ); images format: [ { "id": "owner", "full": "<url>", "thumb": ...
2
votes
3answers
119 views

Return total number of rows and selected (aggregated) data

I have a function which is selecting some data from a table. I would like to return the selected data and the total number of rows in that table. How can I do that or how can I get the same result in ...
0
votes
1answer
19 views

Do PostgreSQL toasted fields use any per row, in-table space?

I understand that PostgreSQL will store large fields in a separate storage space automatically. When this occurs, is there anything stored per row for that field in the table? A reference or hint of ...
0
votes
0answers
36 views

Best practice for select or insert into [duplicate]

I was looking for quite some time on whats the best way of selecting or inserting some data. I have two tables: ReportData: report_data_id(pk), source_id(fk), value1; Source: source_id(pk), ...
2
votes
1answer
32 views

WAL files are not copied

I'm using postgres 9.3 on Ubuntu 14.04 and I'm currently setting up a WAL backup. These are my changes to the postgresql.conf: [as described here: ...
1
vote
1answer
99 views

Using EXCEPTION to ignore duplicates during bulk inserts

In pq 9.3.5, I'm importing records from an external source where duplicates are VERY rare, but they do happen. Given a readings table with a unique compound key on (real_time_device_id, recorded_at), ...
0
votes
0answers
172 views

ERROR: relation “name” does not exist

I am trying to import a postgresql database table sent to me in phpPgAdmin, I am following the instruction from this import database But after doing so, several error appear, I also tried to import ...
1
vote
0answers
34 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 ...
3
votes
1answer
67 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
67 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 ...