PostgreSQL 9.3 : 2013 release of PostgreSQL

learn more… | top users | synonyms (1)

0
votes
1answer
34 views

How to avoid duplications using a function

I am using this function in a bulk insert to avoid duplication of url paths. CREATE OR REPLACE FUNCTION "univ"."gc_landing"(IN _name text, OUT landing_id int4) RETURNS "int4" AS $BODY$ DECLARE ...
1
vote
1answer
14 views

Using psql with string variable that has two periods

psql -d mydb -U me -h localhost -f db_log.sql -v db_user_string='Me' -v version="1.7.3" into the script begin; create table db_log ( db_owner varchar(255) not null, db_user varchar(255) ...
2
votes
2answers
38 views

UNIQUE constraint on large VARCHARs - PostgreSQL

I have a column defined like: data_url character varying(32768) NOT NULL and UNIQUE constraint on that column: CONSTRAINT unique_data_url UNIQUE (data_url) When a large object is being inserted ...
0
votes
1answer
56 views

Random unguessable primary key which preserves correct order

I want primary keys in my table to be random and unguessable while still preserving correct order (order in which documents were inserted to database). Firebase uses this algorithm: ...
2
votes
2answers
44 views

PostgreSQL CHECK constraint is evaluated wrongly

Following schema allows to violate the CHECK constrait, but only once. Then the constraint is correctly enforced. drop database if exists example; create database example; create table example_table ...
0
votes
2answers
45 views

How to rollback transaction if row changed?

I am using Postgres 9.3. When I update row in a table my flow looks like this: Fetch old row (ex. SELECT * FROM tbl WHERE id = 1) Validate new data in scope of old data (old row fetched in point 1) ...
-2
votes
1answer
25 views

Best way to query for a list and then run a query for each result in that list [closed]

I am new to databases in general and particularly new to PostgreSQL (v 9.3). I often find myself in the situation that I first run a select statement which will return a list of users stored in an ...
0
votes
0answers
6 views

How to execute cursor in PostgreSQL?

This is my program for cursor, but I am not getting how to execute the cursor on a database. emp=# CREATE or replace function cursor_demo() returns integer as $$ declare emp_rec employee%rowtype; ...
1
vote
1answer
21 views

How do I debug an Idle Query?

I have a batch query that I'm running daily on my database. However, it seems to get stuck in idle state, and I'm having a lot of difficulty debugging what's going on. The query is an aggregation on ...
1
vote
2answers
28 views

Speed of WAL send/receive when replicating

1/ Description: I have a cluster include two machines Master: postgresql 9.3 run on Centos Slave : postgresql 9.3 run on Centos Master ----> Slave : relpica from Master to Slave by asynchronous ...
0
votes
1answer
18 views

Avoid PG::TRDeadlockDetected

I am using the below function in a bulk insert, usually the bulk insert has around 60 rows and each row will have the below function. But from time to time I get PG::TRDeadlockDetected: ERROR: ...
0
votes
0answers
5 views

Can duplicate pg_hba entries result in a connection error?

I have received the following error when attempting to connect to an Arch Linux server: Sequel::DatabaseConnectionErrorreplication#monitor PG::Error: could not connect to server: Connection refused ...
1
vote
0answers
56 views

How to speed up query which is using <@> operator?

I am using earthdistance extension (along with cube extension). I have following query: SELECT "User"."id" ,"User"."displayName" ,"User"."firstName" ,"User"."lastName" ...
0
votes
0answers
14 views

Insert into a specific partition, create if not exists

So I have my master table: p_reports: id(pk), web_id, other(data) At the moment I create my partitions which inherits from p_reports by using: CREATE TABLE IF NOT EXISTS ...
0
votes
1answer
35 views

Setting PostgreSQL data_directory in postgresql.conf not working

I'm attempting to configure PostgreSQL 9.3 on a Centos 6.6 box to keep the configuration files (postgresql.conf, pg_hba.conf, pg_ident.conf) in a different directory than the data directory. According ...
0
votes
0answers
32 views

Postgres Won't Start - incomplete startup packet

I have an issue that is preventing a Postgres server from starting. The server fell over after it ran out of disk space, how ever this has now been resolved although now Postgres will not start. It ...
0
votes
0answers
28 views

Duplicate key value violates unique constraint

I'm new to PostgreSQL. I'm currently working on an open source software package which is java based using postgresql 9.3. For some requirement I populated a table (organisationunit) without using the ...
1
vote
0answers
25 views

Postgresql: Partition table by hour

I have a system that inserts several million records over the course of 24 hours. Users don't care about the data that's older than 24 hours, and typically query data over a small time frame (5 to 60 ...
3
votes
1answer
55 views

How can I implement a sequence for each foreign key value?

In a PostgreSQL (9.3) database, I have a one-to-many relationship between Accounts and Customers, so within the Customers table, I have a globally-unique id for the primary key of each Customer, plus ...
0
votes
1answer
26 views

How to convert time without time zone to timestamp without time zone in PostgreSQL?

I received this error when trying to alter a column of type time to type timestamp: PG::CannotCoerce: ERROR: cannot cast type time without time zone to timestamp without time zone It's not ...
0
votes
0answers
18 views

How do I alter my role to be a superuser?

I'm working my way through the steps in this slide to configure Postgresql & PostGIS. I configured my user role as directed on this slide. Note, the new role should not be a superuser. That ...
2
votes
1answer
59 views

How to speed up select distinct?

I have a simple select distinct on some time series data: SELECT DISTINCT user_id FROM events WHERE project_id = 6 AND time > '2015-01-11 8:00:00' AND time < '2015-02-10 8:00:00'; And it ...
0
votes
1answer
18 views

Compare only day / month / year portion of a timestamp field

I am creating a view for a certain table, where rows have timestamp fields (planned_ts). The view needs to be sorted according to these timestamps, but since the application is only interested by the ...
0
votes
0answers
21 views

How to create counter for table column (not id) which reset to 1 every month?

I suppose that I need to do something like this: ALTER SEQUENCE product_id_seq RESTART WITH 1 But I want to do this automatically every month.
1
vote
1answer
72 views

Reuse SELECT query by adding results in array?

I have written a PostgreSQL function, which returns a specific order of products. Now I would like, not only to show, but to put the results of the first SELECT query to an array as well, so I can ...
0
votes
1answer
28 views

Connect to remote EC2 Postgresql database

I want to setup a connection between a webserver and a PostgreSQL 9.3 server. Both are hosted on Amazon EC2 servers and are part of the same security group. I can't get the connection working. The ...
2
votes
0answers
47 views

Optimizing a single table, write-once, read-many PostgreSQL database

I've just finished constructing a table of ~835 million rows using Google's ngram dataset, aggregated on the years in which they occurred so that each 2-,3-,4-, and 5-gram is represented by a single ...
0
votes
0answers
33 views

Append elements to array only if the element does't exist

WITH upd AS ( UPDATE univ.products SET source = source::int[] || _source WHERE project_id = _project_id AND product_id = _products AND NOT(_source = ANY(source)) RETURNING id ) , ins AS ( ...
1
vote
1answer
30 views

invalid memory alloc request size on simple query with array_agg on Postgres 9.3

Postgresql 9.3 - Debian 7 54gb RAM 8 cores (On google compute engine) I have a huge table called search_token (~50M rows) and I'm trying to execute a very simple query that is: select ...
0
votes
0answers
14 views

Exception handler on multiple row insert PostgreSQL [duplicate]

I'm trying to create a function which inserts rows from one database table to another, this is the part of the function which goes wrong: BEGIN INSERT INTO suppliers("id","name", "url", "logo", ...
0
votes
1answer
82 views

AWS RDS PostgreSQL dump/restore - syntax error

I am trying to import a postgresql database from an EC2 instance to RDS on the same subnet, so far I am doing the following... 1) Dump EC2 DB: pg_dump --host localhost --port 5432 -Fc ...
1
vote
2answers
46 views

How to update the parent/child of all rows in the tree (ltree)?

Here are my configurations: DB type: postgresql (9.3 if that's necessary) Table name: product_sections Column names: section_id (integer) & section_path (ltree) Detail: I have one reference ...
0
votes
0answers
20 views

pg_restore certain tables only

I'm looking for a way to use pg_restore to restore a newly-created database from a dump file, but only certain tables from that file. (There are a lot of extra tables in the database that are slow to ...
0
votes
1answer
22 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
16 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
46 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
34 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
46 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" ( ...
1
vote
1answer
58 views

Maximum sum() of overlapping ranges

Essentially my question is: how does one do aggregate operations involving overlapping 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
35 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
3answers
72 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
58 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
60 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
310 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
24 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
89 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
51 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
1answer
191 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 ...
2
votes
1answer
85 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(' ', ...