PostgreSQL 9.3 : 2013 release of PostgreSQL
0
votes
0answers
10 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 ...
-1
votes
2answers
27 views
How to use aggregate function and group by with inner join? (Postgres 9.3)
I would like to select "extra" payments made in association with normal appointments for a one week period and SUM them up by contractor when calculating payroll. Here's what I attempted:
SELECT ...
2
votes
2answers
23 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
42 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
36 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
7 views
PostgreSQL , user with “SUPER USER” and all permission to particular database can't read a specific column
I am trying to edit a website. I created a user for myself and granted all permissions and set up that user as SUPER USER, using the user postgres
I still can't read a specific column (which is ...
1
vote
0answers
30 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
21 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
24 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
29 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
108 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
46 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
148 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
22 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
25 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
55 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
34 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
119 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
0answers
15 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
235 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
32 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
75 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
14 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
30 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
66 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
21 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
54 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
20 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
52 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
65 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
86 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
60 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
19 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
36 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
29 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
45 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
16 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
65 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
27 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
64 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
46 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
74 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
69 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
86 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
137 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
19 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
79 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
47 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
26 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
109 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 ...
