PostgreSQL 9.3 : 2013 release of PostgreSQL

learn more… | top users | synonyms (1)

-1
votes
2answers
5 views

psql: could not connect to server and socket /var/run/postgresql/.s.PGSQL.5432

I have installed postgresql 9.4 and I removed postgresql 9.3 from my computer. Now when I try to run: foo@bar:~$ psql I get the error psql: could not connect to server: No such file or directory ...
0
votes
0answers
5 views

Unable to import database through osm2pgrouting

I am very frustrated by the following errors while trying to import NewDelhi by osm2pgrouting tool: osm2pgrouting -file "/home/user/Downloads/new-delhi_india.osm" -conf ...
0
votes
1answer
43 views

Reduce the number of queries to the same table

I need to build some reports using two tables: AuthorTitles: id(pk), author_id, title_id AuthorReports: id(pk), author_id, title_id, items_sold, date My report needs to output the fallowing: ...
0
votes
0answers
16 views

Implementing json_object_agg() in Postgres 9.3

I feel like I need the json_object_agg() function of Postgres 9.4 but I will not be able to upgrade from 9.3 right now. Is there a way to do what I want in 9.3? Here's my scenario. I have a table ...
4
votes
2answers
67 views

Optimizing queries on a range of timestamps (one column)

I am using Postgres 9.3 through Heroku. I have a table, "traffic", with 1M+ records that has many inserts and updates every day. I need to perform SUM operations across this table over different time ...
0
votes
0answers
15 views

PostgreSQL - Calculating opening and closing balances

I am having the following scenario some time right after midnight I'd like to calculate was the closing balances were for the day before and the opening balances for the current day. Those two values ...
2
votes
2answers
25 views

Aggregate all joined rows with where clause

Three nested tables exist: user_groups, users, projects. Each user_group has many users and, in turn, each user has many projects. Each project has an ordinal assigned, 1-5 (step_number). I would ...
0
votes
2answers
53 views

Automatically set value on child rows when deleting parent row?

Description We have a table and data below : Version: PostgreSQL 9.3 Table name: tree_data (id int, code text, name text, parent_id int) Primary key: id Foreign key: parent_id (refer to id) and ...
1
vote
1answer
44 views

Postgresql query history hacking

I believe that PostgreSQL can log slow or unsuccessful queries. Probably I can set also PostgreSQL to log all queries executed. Contrarily, I am interested to know if there is a way that a malicious ...
0
votes
0answers
14 views

A PostgreSQL user with SUPERUSER and all permissions to particular database can't read a specific column [closed]

I am trying to edit a website. I created a user for myself and granted all permissions and set up that user as SUPERUSER, using the user postgres. I still can't read a specific column (which is ...
1
vote
0answers
38 views

Query for a table with paging and filtering vs. CTE (common table expression)

Intro In PostgreSQL 9.3: I am building a query that fetches data for a table that supports sorting, filtering and paging. Think Customers for example, you want to show name, surname, some detailed ...
1
vote
0answers
40 views

Replication Lag on Postgres AWS RDS Service

We have a single master/single streaming replica Postgres 9.3 db on AWS. The load is not terribly high - this is a development/staging environment. (The production shows similar metrics). Point is the ...
1
vote
1answer
26 views

purchasing item by client - how to ensure that user has enough funds?

I have to tables: users (id serial, coins integer not null default 0) purchases (id serial, price integer not null, user_id) When new row is inserted into purchases table I want to subtract amount ...
1
vote
1answer
59 views

Foreign Data Wrappers & Windows Authentication

I been trying to get data from a SQL Server to use it on a PostgreSQL database. After a previous question I got into use FOREIGN DATA WRAPPERS, first I try with tds_fdw but after a successfull install ...
3
votes
2answers
185 views

How to get exception context for a manually raised exception in PL/pgSQL?

In Postgres, we get the "stack trace" of exceptions using this code: EXCEPTION WHEN others THEN GET STACKED DIAGNOSTICS v_error_stack = PG_EXCEPTION_CONTEXT; This works fine for "natural" ...
0
votes
1answer
48 views

What will be best (for performance) transaction isolation level?

There, are two types of queries that are executed in my app: DELETE FROM tbl1 WHERE user_id = 1; INSERT INTO tbl1 VALUES (...), (...), ...; and UPDATE tbl2 WHERE id = 1 SET ...; DELETE FROM tbl3 ...
3
votes
2answers
160 views

Have: Postgres database files. Need: Functioning database

I am running Ubuntu 14.04 with a postgres/postgis setup and in a weak moment removed PostgreSQL 9.1 (seemingly a legacy install from 12.04 from before I upgraded to 14.04), having erroneously ...
1
vote
1answer
23 views

Search results mismatch after postgresql update

Today we had to update the PostgreSQL version on our database servers. Before update the version was 9.3.4 and the new one is 9.3.6. Despite the small difference in the database version and the ...
2
votes
1answer
40 views

Linking SQL Server tables to a PostgreSQL Server

I have a SQL Server 2005 instance and also a PostgreSQL 9.3 running on the same network, I would like to know if its possible to somehow create or have 'views' on a database within the PostgreSQL ...
2
votes
0answers
63 views

Unique Index much bigger than the table itself

Pg 9.3 on debian 7. I have a very huge table in rows, about 178 million (will turn 250 million in a week and then grow 500 thousand a day), but not that huge in size, wich is 20GB, it's a small ...
1
vote
1answer
38 views

Return type of JOINing stored procedure - PostgreSQL

Is it possible to express return type of stored procedure that returns joined tables using table type of one of joined tables? My stored procedure looks like this: CREATE or REPLACE FUNCTION ...
1
vote
1answer
134 views

How to return values from “upsert” query in case when UPDATE has been performed?

WITH upsert AS (UPDATE tbl SET a = 2 WHERE a = 1 RETURNING tbl.*) INSERT INTO tbl (a) SELECT 1 WHERE NOT EXISTS( SELECT * FROM upsert ) RETURNING * This "upsert" statement works however I would like ...
2
votes
1answer
31 views

GRANT USAGE on all schemas in a database? postgres

I want to GRANT USAGE to a user/role for a given database. The database has many schemas. I know there's an ON ALL TABLES IN SCHEMA, but I want "all schemas". I tried GRANT USAGE .. ON DATABASE, but ...
4
votes
3answers
242 views

Custom sorting of a query

The application I am working on displays some tasks based on status, and date (in that order). However, for a particular status, the sort condition should be inverted. For example, a table which looks ...
1
vote
0answers
33 views

How to return updated data from UPDATE … FROM query?

UPDATE "tbl1" SET "col" = 'val', FROM "tbl2" WHERE "tbl2".id = 1 RETURNING * This query returns values from tbl2 instead of updated data from tbl1 - how can I get updated data? (PG 9.3).
0
votes
1answer
131 views

Where do you find the citext module in postgres 9.3 on ubuntu 14.04?

So I'm trying to use citext, but it appears to be MIA from ubuntu 14.04 postgres install. Where can you find this mythical beast called citext for postgresql 9.3? $ manage dbshell Password for user ...
0
votes
0answers
18 views

PostgreSQL analyze only parent table only

I am moving data from a few billion records table to newly created partitions. I am inserting into the child table directly and deleting from the main table with filters on the appropriate columns in ...
0
votes
1answer
37 views

Errors importing data using COPY comand at postgresql 9.3.5

I am trying import a database table to Postgres 9.3.5 database server using COPY command as follows: COPY comment (generatedid, id, "timestamp", message, bugreport_id, personcontainer_id) FROM ...
1
vote
2answers
70 views

Combine results of two queries, where the second relies on the first

I have one query which is returning name(text), total, created_at And another query which is returning the same thing name(text), total, created_at The second query usually is returning more ...
1
vote
1answer
24 views

changing postgres passwords without plaintext passwords in logs

I do not have access to a Postgres installation, so I cannot check. I am a security guy, and I'm seeing plaintext passwords in the logs: create user user1 with password 'PLAINTEXT PASSWORD' How ...
0
votes
1answer
56 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
23 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
57 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
73 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
132 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
64 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) ...
0
votes
0answers
42 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
48 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
31 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
76 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
1answer
20 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
69 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
29 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 ...
1
vote
1answer
87 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 ...
1
vote
0answers
58 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
95 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
105 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 ...
4
votes
1answer
101 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
213 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
21 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 ...