Specifically for PostgreSQL version 9.5
0
votes
0answers
5 views
Convert jsonb column to return table where keys can dynamically change[not fix can vary]
If I have a jsonb column which can save different key-value pair of data. And I want it to be represent in table form is there any way?
For ex.
table - Document(id bigserial, documentData jsonb)
id |...
0
votes
0answers
15 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 ...
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
20 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
44 views
Efficient pagination for big amount of data [closed]
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 '...
5
votes
2answers
57 views
Help with Recursive CTE query with INSERT, ON CONFLICT and RETURNING
I'm trying to write a query to insert filenames to the following table table path
CREATE SEQUENCE path_id_seq;
CREATE TABLE path (
id INTEGER PRIMARY KEY DEFAULT NEXTVAL('path_id_seq'),
...
0
votes
2answers
86 views
Delete all data in Postgres database
I have created a fresh db dump from a production server with the --data-only and --column-inserts flags, so I only have a bunch of insert statements to insert data when performing a restore on a ...
1
vote
2answers
21 views
How To Remove Spaces Between Characters In PostgreSQL?
I want to match two tables based on a specific column which is a card number, there are spaces between characters in some of card numbers in the second table like below:
'NES 123_4_5'
'MTS 678_9_0'
...
0
votes
0answers
18 views
Faild to Connect To a Secondary Cluster of PostgreSQL From Remote Host
I have a problem during connecting to a cluster that I created in a PostgreSQL server. I can connect to the default cluster which has the port number 5432. when I create another cluster and give it a ...
1
vote
1answer
19 views
Count number of writes to different tables
I have recently setup Continuous Archiving for Point-in-Time Recovery in a postgresql DB, but the archive is growing at an unexpectedly high pace (tens of gigabytes per day). The DB is a couple of GB, ...
0
votes
0answers
15 views
Postgres Foreign Data Wrapper external vs internal server performance
Setup
several database clusters on different servers - not related
Postgres 9.5
postgres_fdw extension
Case
I have several database clusters stored on different servers which are not dependable ...
0
votes
1answer
13 views
Postgresql Update Table row values by Grouped Column
First of all a little bit of background:
I currently have a table which contains call data both inbound and outbound from my business, up until recently there was no api_id data available, however ...
0
votes
0answers
51 views
Advice for very large tables
I have a machine with a VM running postgresql 9.5.
I use postgresql for datawarehouse jobs (OLAP) , and it contains very large tables (like 400 millions of rows).
I'm noticing a terrible performance ...
0
votes
1answer
23 views
How to Insert into with postgresql and JSONB
I am using Postgresql 9.5
I have two tables(product and product_temp) with jsonb colums
I would like to insert the values from product_temp to product
but the name of the data inside the json is ...
-2
votes
1answer
42 views
Need to Delete old row from my table on conflict of the particular column
Spec: Postgresql 9.5
Python3.5
psycopg2
I have table(dummy) column1, column2, column3, column4 & column5. if i have on conflict with column2 which contains phone number i need to ...
0
votes
1answer
34 views
Types of ways to achieve Many-to-Many relationship between tables in DB?
I have two tables namely 'City' and 'Travels'. City Table will have list of cities in each row with city_id as the Primary Key and Travels Table will have list of travels in each row with travels_id ...
1
vote
1answer
61 views
Postgres ignoring wal_level setting in conf
I am trying to configure replication in Postgres, and running into an issue where the server is ignoring the setting for wal_level in the configuration file.
I have set in the config file:
wal_level ...
0
votes
1answer
26 views
Postgresql json column to view
I have a table that looks like this
id | json_column
---+------------
1 | {"text1":"a", "text2":"b", "text3":"c", ....}
2 | {"text1":"b", "text2":"c", ....}
and I want it to be like this
text1 |...
4
votes
2answers
102 views
PostgreSQL UPSERT issue with NULL values
I'm having an issue with using the new UPSERT feature in Postgres 9.5
I have a table that is used for aggregating data from another table. The composite key is made up of 20 columns, 10 of which can ...
2
votes
1answer
139 views
Understanding “max_wal_size” and “min_wal_size” parameters default values from postgresql.conf file
According to documentation, for "min_wal_size" and "max_wal_size" parameters
default values are:
For max_wal_size: The default is 1 GB
For min_wal_size: The default is 80 MB
Then I look this ...
1
vote
0answers
23 views
Why is this dockerized Postgres not able to authenticate with a password?
I am working on changes to an Alpine fork of the official Docker postgres image ( https://hub.docker.com/r/deinspanjer/docker-postgres95-alpine34/ ). It has been working fine for a while, but I ...
0
votes
0answers
17 views
Postgres multiple inherited tables
If I have a base table and 2 or more tables that inherit from it, child1 and child2, I can insert a record into a child table and it shows up in base as you would expect.
However what I want to know ...
1
vote
1answer
44 views
how to convert my PostgreSQL database table into CSV? [closed]
i need to convert my PostgreSQL table into csv, how to do this?
i have visited many sites they suggest the PostgreSQL command to convert the table into CSV but i always get the error message.
the ...
1
vote
0answers
14 views
Path extraction SQL
I've a table with some ID's of a tree structured like this:
term1_id term2_id
1507 ;2
107 ;3
1 ;5
5 ;6
5 ;7
812 ;8
14 ;9
2719 ;10
9 ...
2
votes
1answer
28 views
Create range from column
I have a list of positions:
pos
123
100024
1025
5426
I want to define a range of these positions
E.g.
100-1000
1001-2500
etc...
So when i do a Select of how many positions there are in that ...
9
votes
1answer
156 views
postgres_fdw performance is slow
The following query on a foreign takes about 5 seconds to execute on 3.2 million rows:
SELECT x."IncidentTypeCode", COUNT(x."IncidentTypeCode")
FROM "IntterraNearRealTimeUnitReflexes300sForeign" x
...
0
votes
1answer
27 views
Division by couples
How can it be possible to obtain a division for all the couples in my table?
I have:
ref alt
A T
A T
C G
A A
The frequency is obtained by:
number_couple / total_number
Where ...
3
votes
1answer
59 views
Hint on some possible query improvement
I am currently learning SQL on my own and I am looking for a review of one of my query.
I have the following problem.
"Given a table of players and a table of played match output the ranking of the ...
3
votes
2answers
51 views
Uniqueness constraint in union of two columns' values
Using postgresql 9.5, assume I have a table:
CREATE TABLE t (
id INT,
primary TEXT,
secondary TEXT
)
I want to guarantee that the union of values in primary and secondary does not contain ...
0
votes
1answer
8 views
PostgreSQL 9.5 and PostGIS on OSX
I have installed PostgreSQL 9.5 on OSX using the EnterpriseDb package.
How do I install PostGIS on it? The official PostGIS OSX Page says that:
The OSX PostgreSQL/Stackbuilder combination from ...
1
vote
2answers
51 views
Uninstalling / upgrading PostgreSQL on OSX
The problem
I have PostgreSQL 9.3 installed on my MacBook Pro. I don't remember how I installed it (there are numerous ways), and now I want to upgrade to PostgreSQL 9.5.
Foresnsics
The running ...
0
votes
1answer
23 views
Limit of psql_history
I can't find (using ctrl-r within psql and less ~/.psql_history) a particular query that I executed a couple months back. I also recently upgraded from 9.3 to 9.5 so I'm not sure if there is a limit ...
0
votes
1answer
24 views
PostgreSQL: Multiple materializations in same query plan?
I have the following query:
Query:
SELECT
a1.id id,
((SELECT SUM(SFF.pnl)
FROM positions SFF
JOIN ...
0
votes
2answers
60 views
Combining Postgres jsonb query statements
I have test table with votes column and I want to upsert nested jsonb object into it. My current query:
INSERT INTO test (post_id, username, votes)
VALUES (12345, 'testuser', '{"commentid" : {"vote":...
0
votes
2answers
64 views
Comparing two rows in PostgreSQL
I have an employee table in two different servers with the same structure. I want to shift all the data to main server but there are some records already exists in main server that are duplicate of ...
1
vote
0answers
32 views
Postgresql row-level security generates different query plan from manually inserted WHERE clause
(Copied from my post to the postgresql mailing list https://www.postgresql.org/message-id/CADBa3wZpuYXwdry2g68NxYmAXOPvx0DLvdAU4kdo2GoPtxKu+Q@mail.gmail.com. Unfortunately I need at least 10 ...
0
votes
1answer
17 views
Postgis distance filter does not work as expected
I have a issue with simple PostGIS query.
Here is a query:
SELECT
id,
location,
ST_Distance_Sphere(location, ST_MakePoint(21.012228699999998, 52.2296756)) AS distance
FROM pins
WHERE
...
2
votes
1answer
36 views
Postgres cascades truncates to unrelated tables
I've created a number of tables in my public schema.
Occasionally, I need to run a seeder to refresh the users table when I perform tests. To do this, I'm executing TRUNCATE TABLE users RESTART ...
1
vote
1answer
88 views
INSERT into three tables with many-to-many from one table
With Postgres 9.5 using a trivial example.
Given I have this data:
Table everything:
| fruit | country |
|--------|---------|
| Banana | USA |
| Banana | Panama |
How would I create these ...
1
vote
1answer
43 views
Can we use same PostgreSQL cluster by multiple hosts over Ubuntu servers?
I created a PostgreSQL cluster in one host and shared it over NFS share to another host. Even though i can access the cluster using second host but can not use or log in to databases. Is it possible ...
0
votes
1answer
66 views
How to update complex jsonb column?
I have a table with following definition:
create table json_test (
filter_data jsonb);
and i insert to it values like this:
'{"task_packets": [
{
"state": "PROCEEDING",
"...
0
votes
1answer
222 views
Postgresql one master multiple slaves failover
My problem is same which asked in this topic
Streaming Replication Failover - how to point second slave at new master?
Currently I am working on Postgresql 9.5, I have one master and two slave ...
5
votes
1answer
90 views
How to compare xmin and txid_current() after transactions ID wraparound?
Besides its regular columns, Postgres tables also have various system columns available. One of them, xmin, stores the transaction ID used to create a row. Its data type is xid, a four byte integer ...
0
votes
1answer
115 views
psql 9.5: gen_random_uuid() not working
SELECT gen_random_uuid()
produces output
ERROR: function gen_random_uuid() does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You might need to add ...
0
votes
0answers
62 views
How do I speed up this query in Postgres?
I've added indexes and the query plan shows all 0.0, yet the query takes 7s to run. I have 3 similar queries on the same page and they often exceed the 30s time limit. I've already upgraded the DB to ...
1
vote
1answer
97 views
Does a COMMIT work within an anonymous plgpsql function in PostgreSQL 9.5?
I am importing a large number of large files into a number of tables to be partitioned using loops within an anonymous plpgsql code block $do$.
$do$
BEGIN
FOR yyyy in 2012..2016 THEN
...
0
votes
1answer
13 views
Revoke grant option from table owner
I have a role engineer1 | No inheritance, Cannot login | member of {priv_app}. The priv_app user ownes the tables in the schema. Users are members of engineer1 by default which only grants them select ...
0
votes
1answer
50 views
PostgreSQL on Desktop vs. Server
How does running PostgreSQL on a Windows desktop with pgAdmin III compare to running it on a dedicated server? I am trying to develop a database with very large tables (~60 million rows) with the ...
0
votes
0answers
88 views
Recommended maximum memory setting for PostgreSQL 9.4+ with Huge Pages
Since PostgreSQL 9.4+ supports Linux Huge Pages, I have configured a Ubuntu 16.04 server with 16GB of RAM and 8 CPU cores to use Huge Pages with a dedicated PostgreSQL 9.5 instance.
The estimate in ...
1
vote
0answers
40 views
How to select random groups of similar records?
I have following table:
create table users (
id serial primary key,
gender text not null,
age integer not null
);
insert into users (gender, age)
select (case when i % 2 = 0 then 'male' else '...
