All versions of PostgreSQL. Add a version-specific tag like postgresql-9.4 if that context is important.
0
votes
0answers
5 views
controlling postgresql log generation
My disk space fills up daily with postgresql logs. One optimzation solution I'm pursuing is that instead of turning off logs, I should overwrite the original log file (i.e. rotate?) once it hits a ...
0
votes
0answers
6 views
How do I easily create the DDL statements for a schema represented by the contents of an XML file?
I have been given an xml file and it's pretty obvious in looking at the contents that it contains an inherent schema. What I'd like to do is run a program against it that outputs the schema ...
0
votes
1answer
7 views
SQL command to clean job queue in pgAdmin 4
I have a Postgres 9.6 installation on a developer pc and a restore seems to have failed. Even after reinstalling (remove Postgres and PgAdmin completely, incl. database and installed new version) the ...
5
votes
1answer
57 views
Documentation for selecting table name returning csv of the row
It seems that in Postgres 9.2 this syntax
SELECT <table name>
FROM <table name>
Returns rows with all columns aggregated as pseudo-CSV, like so:
(1,test,48,,48,,"2016-10-29 00:47:09....
0
votes
0answers
7 views
Postgres Foreign table postgres_fdw ::LOG: could not receive data from client: Connection reset by peer
"FOREIGN_TABLE" created with postgres_fdw. LOCAL_TABLE is just a local table...
Symptoms:
I run in psql query SELECT * from FOREIGN_TABLE. No log generated
I run in bash psql -c "SELECT * from ...
2
votes
1answer
15 views
Postgresql on AWS RDS JDBC Connection Refused, psql connection succeeeds
I have an Amazon RDS Postgres instance running. I can see the data from my local machine (whitelist office IP) and our web app can connect (running on EC2 instance).
On that EC2 instance, these ...
0
votes
0answers
29 views
How to get the maximum amount of memory (RAM) consumed by a single query?
I know this topic is pretty complex and involves a lot of different factors. Let's say I have several similar queries running at the same time. These queries involves only read operation and several ...
3
votes
0answers
29 views
How to trick postgres query planner?
I have a following function:
create or replace function fast_companies(
target_size int4range,
target_turnover int4range,
target_markets integer[],
target_countries integer[]
)
returns ...
0
votes
0answers
20 views
Postgres schema owner implicit permissions
I am confused about schema owners in postgres. When I create schema with schema owner defined with AUTHORIZATION keyword, owner can create objects in that schema (no explicit privileges are shown on ...
2
votes
0answers
165 views
Why does VALUES provide a different DEFAULT column name than SELECT?
SQL Columns can get different default names depending on if the columns come from subqueries (where they all get ?column?)
# SELECT 1,2,3,(SELECT 1),(SELECT 2),(SELECT 3),* FROM (SELECT 1,2,3) AS t;
...
5
votes
1answer
264 views
When are COLUMN aliases in FROM clauses needed?
When are COLUMN aliases in FROM clauses needed? This is what the postgres docs say about them,
A substitute name for the FROM item containing the alias. An alias is used for brevity or to eliminate ...
0
votes
0answers
17 views
Create Tablespace in a specific location with PostgreSQL
I want to create a database in a specific location.
I know that every database belong to a tablespace, so I tried to create a tablespace with this command:
CREATE TABLESPACE myTablespace LOCATION 'C:...
0
votes
1answer
15 views
How to increase max_locks_per_transaction in MacOSX
I've been performing kind of intensive schema dropping and creating over a PostgreSQL server,
ERROR: out of shared memory
HINT: You might need to increase max_locks_per_transaction.
I need to ...
0
votes
0answers
16 views
update with regexp_matches regular expression [on hold]
I want to lower values of myfield only for those with [A-Z]+
I can select them :
select myfield, regexp_matches(myfield,'[A-Z]+') from mytable
And now I would like to update those values only, ...
1
vote
2answers
53 views
Large batch INSERTs / UPDATEs and DELETEs Performance problem
I'm having a problem in finding the best option to approach the following problem (postgres 9.5):
I get update batches of about 100.000 rows at a time from another system. This happens every 10-15 ...
0
votes
1answer
25 views
How to print text in select query instead of NULL value in DATE field in postgresql?
I have a table test as follows :
create table test (id int, enroll date)
I have inserted some rows as follows :
insert into test values (1,'2012-01-02');
insert into test(id) values (2);
...
0
votes
1answer
28 views
Check integrity between servers
I have three Database servers, running PostgreSQL 9.2:
master01 (Own data center USA)
slave01 (Own data center USA)
slave02 (AWS Australia)
Both slaves use streaming replication + ...
0
votes
0answers
15 views
Table update in a lost past no log
I am trying to figure out how to find some information about a table that received a update. The database has logs, but it is a old operation (more than 5 months, and we store only 3 months of log).
...
1
vote
4answers
77 views
Building where clause based upon parameters
In PostgreSQL I am trying to build a where clause in a function that uses an inbound parameter to determine the contents of the IN
For example:
select fld1, count(fld1)
from xyz
where fld1 in (
...
0
votes
0answers
13 views
Recover postgres database from PC who only works under command prompt
I have a postgres database running in a laptop with Windows 10.
Today the PC can't start up, and he only works in command prompt (using the Advanced options in init)
I can go to D:/Program Files/...
2
votes
2answers
32 views
Get all rows from table where JSON column contains a certain value
I'm struggling to get data from a JSON column in my PostgreSQL database.
In our users table, we have a known_ips column, which is a JSON column, holding a flat array of IP addresses known for the ...
2
votes
0answers
29 views
How to optimize an ordered date range query with several OR filters (for streaming)?
I have a table of data which is be outputted into a stream, so the upper bound of the cost doesn't matter as much as the lower bound. The table contains about 200M rows. The question is how to ...
0
votes
1answer
24 views
When will autovacuum work in PostgreSQL?
I tested the autovacuum in PostgreSQL. So I create a table company and insert 100 records. Then I checked the table size like,
ganapathy=> \dt+ company
List of relations
...
-3
votes
0answers
11 views
oracle db 12c and java db and postgreSQL are they the same? [on hold]
This a question for the sake of my personal knowledge. In order to utilize database connectivity for java EE applications which of the 3 three databases are useful and more popular or are 3 of them ...
0
votes
0answers
15 views
Is there any way to set the alias for command permanently in PostgreSQL?
I'm trying to set the alias for command permanently in PostgreSQL.
I've set the alias for a command like given below.
Test=> \set x '\\! reset'
So, the alias has set for reset the terminal.
While ...
0
votes
0answers
11 views
Data Replication From Master 9.3 to Slave 9.4
We have live setup like Master 9.3 & Slave 9.3 (Read Only) in the same network.
Now I want to configure new Slave 9.4 on a different server/network with Continuous Replication. I tried pgbase ...
-1
votes
1answer
15 views
Postgres (auto) vacuum vs MySQL REPAIR TABLE
What are the differences between them? They have the same purposes?
http://dev.mysql.com/doc/refman/5.7/en/optimize-table.html
https://www.postgresql.org/docs/current/static/routine-vacuuming.html
1
vote
0answers
9 views
Why does pg_last_xact_replay_timestamp() sometimes return extremely old timestamps?
In PostgreSQL 9.5, to monitor streaming replication lag, I use a combination of:
pg_current_xlog_location()
pg_last_xlog_replay_location()
pg_last_xact_replay_timestamp()
The first two functions are ...
1
vote
0answers
41 views
Sorting killing my postgresql query
I have a PostgreSQL query:
SELECT COUNT(*)
FROM "issues"
INNER JOIN "assets" ON "issues"."asset_id" = "assets"."id"
WHERE "assets"."project_id" = 1
AND "issues"."active" = 't'
AND "issues"."...
3
votes
2answers
25 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
1answer
33 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
19 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
14 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'...
4
votes
1answer
208 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
16 views
PostgreSQL Changing Varchar Values While Copying From CSV File [on hold]
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
37 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 ...
3
votes
2answers
30 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
27 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
33 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? [closed]
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
29 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
25 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
16 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 [closed]
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
36 views
What is the optimal data type for columns that retain dates, DATE or INTEGER? [closed]
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 ...
