All versions of PostgreSQL. Add a version-specific tag like postgresql-9.4 if that context is important.
1
vote
2answers
12 views
Find objects linked to a PostgreSQL role
Some times ago I created a PostgreSQL user named user1 (PostgreSQL 9.4.9).
I want to drop this user. So I first revoke all permissions on tables, sequences, functions, default privileges and ...
1
vote
0answers
19 views
Postgres - how to avoid unnecessary comparisons inside a WHERE with a JOIN?
Background
We're running this query:
SELECT COUNT(*) AS "__count"
FROM "xfiler_document"
INNER JOIN "xfiler_tx" ON ("xfiler_document"."tx_id" = "xfiler_tx"."id")
WHERE (
"xfiler_document"."...
0
votes
1answer
12 views
get result of search in database postgresql and work with it every time
I have a query who spend alot of time because there is thousand of ligne in the database, so I need if it's exist a method to put the result of the query some where and work with them the time i want. ...
0
votes
1answer
10 views
pg_start_backup and small max_wal_size
What will happen, if max_wal_size gets exceeded during backup?
Specifically, let's consider the following scenario on master:
Set max_wal_size to a very low number.
Run SELECT pg_start_backup('label'...
3
votes
1answer
184 views
Row by row subtraction with single input number
I want to subrtact dynamically from previous row subtraction result and as input i want to give a single number.
I have table a
CREATE TABLE a (id int, code text, qty numeric);
And data in it
...
0
votes
0answers
12 views
PostgreSQL Changing Varchar Values While Copying From CSV File
I have a CSV file that I want to load in a table, when I do that using the COPY command of PostgreSQL it copies normally, but when querying I noticed that some of the varchar values are changed. below ...
0
votes
0answers
35 views
Postgresql FULL TEXT search LEFT JOIN
I have 2 tables.
Products - with 70 000 rows
Id, name, name2, listname, onlinename
Synonyms - with 100 rows
Id, value, product_id
SELECT products.id FROM product
LEFT JOIN synonyms ON synonym....
0
votes
1answer
33 views
Differences between .dump and .sql in pg_dump PostgreSQL
I'm trying to take a backup of schema using pg_dump like,
$ pg_dump -U ganapathy -n hotel_management > ganapathy_schema.dump
It create the backup, But I can't restore it using pg_restore. It give ...
2
votes
2answers
29 views
xml parse in postgresSQL
I have a problem with parse simple xml:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<data-set>
<department>
<ID>1</ID>
&...
1
vote
1answer
25 views
Access articles from a table if user belongs to qualified group
The tables below are myuser & mygroup, where each user has membership to 1 or more groups via an M2M table membership.
The articles are accessible by users if one of the condition is satisfied
1)...
3
votes
2answers
29 views
MVCC in PostgreSQL: visible tuple with xmin > current txid
I'm trying to better understand MVCC and am confused by the following (contrived) scenario. I was under the impression that for a row to be visible to a transaction, the transaction's id must be ...
0
votes
0answers
13 views
How to extract namespace from XML in PostgreSQL or PL/pgSQL?
I have an XML document that looks like this:
<something xmlns="http://exmple.com/etc/etc/etc">...
In order to write an xpath for PostgreSQL, I have to pass the namespace as the third argument ...
2
votes
1answer
21 views
Which BI solutions cooperate with Postgres database and aren't too expensive? [on hold]
I'm looking for solution letting me and my team track changes in Postgres database with daily updates. Data doesn't have to be updated instantly, once a day is enough. We use Excel/Google Sheet ...
0
votes
1answer
20 views
insert is not working as expected while doing partitioning
Created a table with one column and sample values
create table t1 ();
alter table t1 add column id_ numeric;
insert into t1 values (generate_series(1,10));
And, done partitioning odd numbers in one ...
-3
votes
0answers
24 views
Unable to convert stored procedure from MySQL to Postgres [on hold]
I am trying to convert the following SP to Postgres
DELIMITER //
CREATE PROCEDURE `kamailio_cdrs`()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE bye_record INT DEFAULT 0;
DECLARE v_src_user,...
1
vote
1answer
24 views
Where are the username and password stored in postgresql?
I'm using the PostgreSQL database. In it I create users. So I want to know
Where are the User name and Password stored?
Whether the Password is stored as plain text or encrypted data?
Whether root ...
1
vote
1answer
25 views
What is the differences between to and = in set search_path?
I have different schemas in my database like,
public
Hotel_Management
Hospital_Management
My default schema is Public. So I try to change from default schema to another schema.
I know ...
1
vote
1answer
15 views
Distributing and managing Postgres SCHEMAs with an EXTENSION?
Postgres permits distribution and management with CREATE EXTENSION. Is it more proper to write internal EXTENSIONS to manage third-party provided data, or to use scripts that DROP and CREATE SCHEMA? ...
3
votes
1answer
35 views
Adding id column of table to a hstore column postgres [on hold]
I am trying to figure out how to store the id column of a table into a hstore column. That way the id is stored in two places. Going forward the table will be moved to another database and the ...
-1
votes
1answer
33 views
What is the optimal data type for columns that retain dates, DATE or INTEGER? [on hold]
In my experience, old school thinking tells that, for optimal performance, date data points should be stored as an INTEGERs (e.g., as in 20160101), since they are manipulated faster, accelerate ...
0
votes
0answers
5 views
RoR items are not rendered in view by :id because they are not listed in order of :id in rails_admin?
Apology for my ignorance but I have not got solution to this problem:
Have this items in database and when rendered in rails view, they are not listed in order of the :id which I expect. When I ...
0
votes
1answer
24 views
Why doesn't postgresql COPY command work with files in mapped drives
I have a problem copying CSV files from a network shared drive into postgreSQL, at present the fix is to copy the files to a local drive first, but it's another process in a chain which is becoming ...
1
vote
1answer
21 views
Is it possible to modify postgresql.conf using pg_ctl?
I am programmatically starting PostgreSQL instances using pg_ctl init and pg_ctl start. I need to modify the configuration in postgresql.conf (specifically listen_addresses, and maybe some other ...
0
votes
0answers
17 views
upgrading postgresql version to 9.5 leads to high cpu utilization [closed]
Upgrading to Postgres version 9.5 has lead to high CPU utilization.
Is there any specific reason which is causing the high CPU utilization?
0
votes
1answer
9 views
Can you edit recovery.conf or postgressql.conf using pdadminIII for an Amazon RDS instance? [closed]
Right now, when I access Server configuration under Tools in pdadmin, I see server configuration is disabled. Looks like I cannot edit?
0
votes
1answer
43 views
Efficient pagination for big amount of data [on hold]
I need to be able to quickly (1-2 seconds) retrieve pages of 50,000 records from table that contains ~3,000,000 records. There's a UNIQUE index on a string primary key which contains values like '...
0
votes
0answers
16 views
Scheduling automatic backup while doing PITR in Postgres
I am new to postgres. I have tried to do a PITR in postgres and able to recover to a specific point doing backup with pg_basebackup. I want to schedule the backup to run everyday at a specific time. ...
1
vote
1answer
18 views
Show the number of all tables, views and base tables
How to do that?
I was thinking of something like:
SELECT COUNT(A.table_name), COUNT(B.table_name)
FROM information_schema.tables AS A, information_schema.views AS B;
But sadly it gives me bad ...
1
vote
0answers
13 views
Using PGPool with RDS Slave replicas, any advice?
I have a Postgres 9.4 RDS instance with Multi AZ, and there's a slave, read-only replica.
Up to this point the load balancing was made in the business layer of my app, but it's inneficient and I was ...
1
vote
2answers
33 views
I can't figure out how to call a function with a trigger, keeps saying function doesn't exist
CREATE OR REPLACE FUNCTION calculate(nr char) RETURNS char AS $$
BEGIN
RETURN (SELECT COUNT(*) FROM patient_notitie WHERE pn_patient_nr = nr);
END;
$$ LANGUAGE plpgsql;
This worked:
...
3
votes
1answer
19 views
Postgresql trigger update whole table after delete
I have a table "measurements" which looks like this (with empty cells in the last col)
ser_nr | meas_ser | meas_value | last_meas
-------+----------+------------+-----------
A1 | 1 | 12 ...
0
votes
2answers
47 views
Duplicate entries in query with sum
I have the following tables:
players: Columns: name, school_name, gender
double: Columns: player_name_fkey, school_name_fkey, category, tournament_number, score
My entries in the simple table will ...
-1
votes
0answers
15 views
The server how to store a mockup route on the database with the polyline, starting point, end point and, duration [closed]
I used remote server, postgresSQL and Python languge, Ihave created tables.but how to store a mockup route in the database, i do not know, please teach me.
1
vote
0answers
44 views
Pessimistic locking and Postgres
I've encountered this comment on reddit:
I love Postgres but the one thing I wish is had is pessimistic
locking. It's just such a huge help when you need multi-statement
queries when multiple ...
0
votes
1answer
14 views
Get a Table from XML in postgresql
Have a look at the following XML data:
<data>
<test color="red">Red text</test>
<test color="green">green</test>
</data>
Let's say I have several xml-...
0
votes
3answers
29 views
How to check which table is called which trigger in postgresql?
I have ten tables and five triggers in my database.
I don't know the trigger name, but I need to edit the trigger.
So I need to know which table is called to which trigger?
Using,
SELECT tgname
FROM ...
5
votes
1answer
69 views
Filter on array text[] and sort on timestamp
Description: PostgreSQL 9.6 on Linux, size of tags_tmp table ~ 30 GB (10 million rows), tags is a text[] and has only 6 values.
tags_tmp(id int, tags text[], maker_date timestamp, value text)
id ...
1
vote
0answers
21 views
Import Data From csv Into Postgresql WITHOUT COPY
I am writing a function for a python environment which needs to import data from a csv file into an existing table. The scope in which the function can execute does not allow for a db SUPERUSER to run ...
1
vote
0answers
26 views
If exists error in nested query
I am learning sql and I am doing another tricky query. I have the following tables.
players: Columns: name, school_name, gender
simple: Columns: player_name_fkey, school_name_fkey, category, ...
1
vote
1answer
33 views
Avoiding foreign key repetition on related models in a relational DB
I have a simple DB schema describing certain objects that belong to a historical time period of a country and (optionally) a subdivision of that country at that period. The contrived class diagram is ...
3
votes
1answer
26 views
SQL - Find top rows where parameter is the same
I am learning sql and I am jumping into more complicated queries. I have the following tables:
players:
Columns: name, school_name, gender
simple:
Columns: player_name_fkey, school_name_fkey, ...
3
votes
1answer
274 views
Query for highest version
I have the following table (stripped to the bare essentials):
(script is in postgresql)
CREATE TABLE t_version (
name VARCHAR(64) NOT NULL,
major INTEGER NOT NULL,
minor INTEGER NOT NULL,
...
4
votes
1answer
54 views
SUM over distinct rows with multiple joins
Schema:
CREATE TABLE "items" (
"id" SERIAL NOT NULL PRIMARY KEY,
"country" VARCHAR(2) NOT NULL,
"created" TIMESTAMP WITH TIME ZONE NOT ...
-2
votes
0answers
46 views
Postgres cache memory usage [closed]
I'm using Postgres version 9.5 installed on a Debian server.
I've 32Gb of total memory in my server and the cache consumed is 30Gb.
Can anyone tell the recommended cached memory that Postgres needs ...
0
votes
1answer
14 views
psqlODBC access encoding problem
My local machine: Windows 7, 32-bit. I have installed on it the psqlODBC for 32-bit. MS Access is 32-bit as well. I have tried both Unicode and Ansi connections to...
The server: a 64-bit Ubuntu, and ...
2
votes
1answer
31 views
Creating a summary table for an averaging query
In relation to another post of mine regarding slow performance for an averaging query on a table with ~30 million rows, I tried creating a summary table to store the averages.
Firstly, I have a table ...
0
votes
1answer
22 views
pglogical for OLAP queries
I am looking to setup a replica for OLAP queries. Currently in my prod I have a master (OLTP node) replicating to a slave via streaming replica. I don't want to use streaming replica for OLAP queries ...
-2
votes
1answer
52 views
Difference between assertion and trigger in PostgreSQL?
A trigger is a statement or a block of statement which are executed automatically by the system when an event like insert, update or delete takes place on a table.
But I don't know where we use ...
1
vote
1answer
42 views
Find which stored procedures are used
I have a legacy PostgreSQL database schema with hundreds of stored procedures. I know that some of them aren't used anymore, and I want to delete them.
What is the easiest way to find which ...
4
votes
2answers
53 views
Abysmal postgres query performance when joining by jsonb_exists (question mark operator)
Our application recently changed how some data was modeled, changing the schema to add a jsonb array column, and populating it from data in another column.
We're finding that the new queries joining ...
