Questions tagged [postgresql-9.4]

PostgreSQL version 9.4

Filter by
Sorted by
Tagged with
0
votes
0answers
26 views

In PostgreSQL how to create procedure to splits data into multiple table

input table name : DATA name ---- abc xyz rcm cmr uvw tuy required output: output table1 name: tab1 name ---- abc xyz output table1 name: tab2 name ---- rcm cmr output table1 name: tab3 name --...
0
votes
1answer
50 views

Postgres - Vacuum doesn't complete on large/busy table

We have a decently active PG database hosted on AWS. We recently started getting notifications like the following: Transaction ID age reached 750 million. Autovacuum parameter values for [...
0
votes
1answer
16 views

PostgreSQL Correlation Between “max_connections” and “numbackends”

I'm trying to figure out why the field numbackends in pg_stat_database never goes above 16 (and often stays the same even after a restart). I have max_connections set to 100 and ...
1
vote
1answer
62 views

move postgresql 9.4 cluster to new server

I have a redmine postgresql folder backup (no dump) from postgresql 9.4 and want to move it to a new server with debian 10 (buster) with postgres 11. On my debian buster server I tried to get this ...
0
votes
1answer
27 views

Comparing nullable column values using “IS NULL” versus “= NULL”

I'm dealing with nullable values in a column of a table, i'm wondering why this: with cte as ( select 'c1'::text as c1,null::text as c2 union select 'c2'::text as c1,null::text as c2 ) ...
0
votes
3answers
80 views

How to SELECT “closest” rows from another table?

I have two tables, map1 and map2, there are multiple possible combinations between the columns map1.id1 and map2.id2. I have tried the below query: SELECT map1.id1, map2.id2, MIN(...
0
votes
1answer
27 views

Allow transparently altering View's underlying table

How to create a view that does not prevent altering its underlying table? There's a certain reason why I can't drop and re-create the view every time I need to change a column type or add a new column....
0
votes
0answers
39 views

Quering Postgresql digest function inside procedure works on pgadmin but not works by calling via Laravel

recently I have created stored procedure in Postgres Sql which deals about with hashing passwords. When used via PgAdmin tool it works like a charm as stated below. I have also installed extension ...
0
votes
1answer
14 views

How to perform count(), count(distinct) in the same output using POSTGRESQL

I am trying to perform both count(distinct(keyword)) and count(keyword) from same tables. Here are the two queries. Query-1 SELECT t1.count(distinct(keyword)) from keyword t1, sent t2,tweets t3,...
1
vote
0answers
35 views

Changing Postmaster File Name in PostgreSQL

I have two instances of PostgreSQL with master-slave replication between them and want to use GlusterFS. Everything works fine until the second instance wants to store the data in the GlusterFS. ...
0
votes
1answer
34 views

FUNCTION BEFORE TRIGGER INEXISTENT OPERATOR date > = double precision

In the 'pubblicazione' table I want to insert some fields of the tuple contained in the 'legge_approvata' table, but to do this the promulgation date must be in the same month and year as the date of ...
0
votes
1answer
40 views

How to make use of WITH in PostgreSQL to reduce query cost

I am trying to get twitter data of narendramodi using below command. SELECT b.t_id,a.profile_image,b.tweet_text,e.media_media_url,b.retweet_count,b.favorite_count as like_count,count(...
0
votes
0answers
8 views

Performance considerations for a standalone server running Postgres 9.4 on 8 CPU core with 47GB RAM

I'm running: Postgres 9.4 8 CPU cores 47GB RAM shared_buffers = 13.8GB work_mem = 32MB The server gets its workload from 6 web application nodes which connect using JDBC connection pooling. Each web ...
0
votes
0answers
18 views

Is this a good way to move items on update to a different table?

So i have a table 'fruits', which has 2 stage deletion process with first being a soft deletion and the second being a hard deletion that deletes item at various times based on business rules. This ...
0
votes
0answers
16 views

Unexpected Chunk Number 1(expected 2) for toast value in postgresql

Kindly assist and let me know how to sort this issue out. It would be greatly appreciated as this is in production [24745]:ERROR: unexpected chunk number 1 (expected 2) for toast value 207293017 ...
0
votes
0answers
584 views

How to run multiple sql statements in Postgres using the tool PGADMIN?

I have 170 Alter Drop Constraint statement, 120 Truncate table, 120 Insert table and 170 Alter Add Constraint. All of these should be run in one in one script as batch script and I'm using PGADMIN IV ...
1
vote
1answer
409 views

Use NEW in FROM clause in Postgres trigger?

I am trying to write a Postgres trigger to unnest an array field before inserting or updating a new row. E.g. SELECT unnest(something) FROM NEW However, this seems to lead to an error: relation '...
1
vote
1answer
176 views

Streaming replication archive folder taking all space- Postgresql- 9.4

We had recently setup postgres streaming replication between master (physical, CentOS release 6.6) and slave(virtual, CentOS 7.6). We did the test and streaming replication is working fine. We ...
1
vote
1answer
33 views

Regarding GIN Index in Postgresql

I am facing an issue that Even after creating a GIN INDEX execution plan is taking much time. select email from customer where lower(email) like '%gmail'; CREATE INDEX idx1 ON customer USING gin (...
2
votes
3answers
69 views

Find all items where the 2 latest statuses meet condition

I have a table in a PostgreSQL database like this: ID|Item-FK |timestamp|status ============================= 1 | 123 | ... | EXPIRED 2 | 123 | ... | PENDING ... I want to query all ...
1
vote
0answers
353 views

postgres :: FATAL: could not access file “pglogical”: No such file or directory

We installed pglogical from source code, we are running PostgreSQL 9.4 make USE_PGXS=1 clean all make USE_PGXS=1 install Installation was successful after updating the parameters, we tried ...
0
votes
0answers
15 views

Streaming replication Failover

We are using postgres 9.4.15 in our master and slave server. We want to test the failover scenario where we will be manually turn off host 1(master) and see if failover happens to host 2(slave server)....
1
vote
1answer
347 views

Streaming replication version between different minor versions

I want to know few details about streaming replication. I want to setup streaming replication between version 9.4.4 and 9.4.20 ? I have the master server in centos6.6 with postgres version 9.4.4 ...
1
vote
0answers
72 views

ERROR: relation “codepool_codepoolseq_seq” does not exist SQL state: 42P01

Below is my script but I keep getting an error. -- Table: public.codepool -- DROP TABLE public.codepool; CREATE TABLE public.codepool ( codepoolseq bigint NOT NULL DEFAULT nextval('...
1
vote
1answer
1k views

Downgrading PostgreSQL 10 to 9.4

Are there any particular precautions I should take when downgrading PG 10 to 9.4? I have a database on Heroku (PG 9.4) which I'm going to upgrade to PG 10.6. In case something goes wrong, I want to ...
1
vote
2answers
48 views

How to dump multiple databases of the same cluster in single execution?

I have 100 databases in my cluster. I need to backup 30 databases (as single file or multiple file, anything is fine). I could do it for tables using pg_dump with -t. Is there any way to do ...
0
votes
1answer
33 views

Streaming replication in postgres question

We have recently setup streaming replication in our Postgres server (t01, t02). t01 is master and t02 is the slave. I want to understand the below two issues: Recently our /var directory of t01 ...
1
vote
1answer
32 views

Merge 2 columns and replace with specific output

The setup is a Debian 8 with Postgres 9.4 (64bit if that matters). Given following table I want to "search" for a combination of columns first and column sub and replace their output with another ...
1
vote
0answers
15 views

Calculate percentages by using JOINS result in postgresql

I am trying to calculate percentages by joining 3 tables data to get the percentages of positive_count, negative_count, neutral_count of each user's tweets. I have succeeded in getting positive, ...
0
votes
2answers
39 views

Different results for logically same, but textually different queries

Is there any reason why the following queries would return different results? select count(id) from mytable where status='active' and ((textfield1 ilike '%needle%') or (textfield2 ilike '%...
1
vote
1answer
50 views

Postgresql installation, avoid creating “postgres” user

I have a constraint, that I can use only one user on a SUSE Linux Enterprise Server 11. Now, I need to install PostgreSQL 9.4. Is it possible to somehow avoid the automatic creation of "postgres" ...
0
votes
1answer
771 views

PgBouncer “no more connections allowed (max_client_conn)”

I am using PostgreSQL with PgBouncer, and receive the following error in my application: 08P01: no more connections allowed (max_client_conn) pgbouncer.ini has the following configuration: ...
3
votes
1answer
1k views

Postgres 9.4 wal_level=logical increases disk space

I am trying to use the AWS Database Migration Service to migrate data from an RDS Postgres instance to an Aurora MySQL instance. Database Migration Service requires the wal_level to be set as logical ...
4
votes
2answers
81 views

drop database command is cancelled ^C

I want to know what happens when a drop database command is canceled using ^C. Does it partially removes the data or completely?
1
vote
1answer
57 views

How to get aggregate data from a dynamic number of related rows in adjacent table

EDIT: Unknowing of the rule that prohibits cross-posting, I also asked this on Stackoverflow and chose an answer over there. Since there's another (fully working) answer in this thread though, I won't ...
2
votes
1answer
130 views

How use cascade delete for array field in PostgreSQL?

How can i remove the value from array field? I need to delete the user id from the university table's user_id array field if exists when i delete any users. so can i do this by using delete cascade ...
1
vote
1answer
63 views

Query Conflicts in Streaming Replication in Postgres 9.4

When using Streaming Replication in Postgres 9.4, when a query makes the standby Postgres "start to count" the max_standby_streaming_delay, changes being made in the primary: Are dammed at the ...
3
votes
0answers
429 views

Why am I getting permission denied on an ownership change?

My current user is pronto mediapop_staging=> select current_user; current_user -------------- pronto (1 row) This user a standard super user created on AWS RDS. mediapop_staging=> \du ...
2
votes
1answer
48 views

How to use “WHERE” and also “WHERE NOT EXISTS” in the same query and table?

I am trying to add both WHERE and WHERE NOT EXISTS in same query. Below mentioned query1 is working fine, but it returns all the t1 data which does not belong to company1. So I tried query2, but no ...
-1
votes
1answer
2k views

Unpivot a table in PostgreSQL [duplicate]

I need to unpivot a table in PostgreSQL. The first dataset is what I have, the second is what I want: Below is my schema with sample dataset: http://www.sqlfiddle.com/#!17/3f45c/1 My understanding ...
0
votes
1answer
361 views

How to query inside jsonb column

I have a column type of jsonb named changes THis is the structure of my column. [["change","is_enabled",[false,true]]] I want to query within the column. I want to find all the rows that "...
1
vote
1answer
120 views

How to find the nearest lat and lang in postgresql

I am trying find the nearest lang and lat from the specified lat and lang i tried the below mentioned query, in my query i have mentioned the place muscat's lat and lang ,so i want to display the ...
10
votes
3answers
2k views

Fastest way to count how many dates' ranges cover each date from series

I have a table (in PostgreSQL 9.4) that looks like this: CREATE TABLE dates_ranges (kind int, start_date date, end_date date); INSERT INTO dates_ranges VALUES (1, '2018-01-01', '2018-01-31'), ...
0
votes
2answers
182 views

Restarting hot_standby replication when the *slave* fails

Context: master-slave replication on Postgres 9.4, slave died a couple of weeks ago: the slave copied a WAL-File from the master, told the master that the file is copied, the master deleted the ...
0
votes
1answer
32 views

Give particular privileges for a table to existing role

My database have some tables such as: Table A Table B Table C I'm using user monitoring with read-only permission. SELECT grantee, privilege_type FROM information_schema.role_table_grants WHERE ...
3
votes
4answers
2k views

How to count boolean values in PostgreSQL?

How to count, how may true and false for the field public in postgresql user table i have tried this query select sum(case when false then 1 else 0 end) as false, sum(case when true then 1 else 0 ...
2
votes
1answer
287 views

Lost data during PostgreSQL minor version upgrade

I had PostgreSQL 9.4.5 installed on MacOS which was done using Homebrew. I wanted to update it to the latest minor version of 9.4, so I did this (if I remember correctly): brew uninstall postgresql ...
0
votes
1answer
464 views

pg_restore: [archiver] could not find entry for ID 12

I'm trying to restore a dump (-Fd --data-only) from PostgreSQL v9.4.9, but I've come across the following error: pg_restore: [archiver] could not find entry for ID 12 Exactly the same happens if ...
3
votes
1answer
610 views

PostgreSQL ERROR: cannot freeze committed xmax

First of all, I am new to PostgreSQL database. In my PostgreSQL log I can see errors bellow and I don't know what is wrong or what I should change. 2018-03-29 10:54:38 CEST::@:[5059]: CONTEXT: ...
1
vote
0answers
219 views

Postgresql BDR - What causes replication_slots to go inactive?

I'm trying to hunt down what causes replication slots to go inactive. Might seem like a remedial question but I'm noticing that on my master database server, I have many inactive replication slots ...