Questions tagged [postgresql-9.4]

PostgreSQL version 9.4

Filter by
Sorted by
Tagged with
0
votes
1answer
179 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(...
4
votes
1answer
5k views

“ERROR: there is no parameter $1” in “EXECUTE .. USING ..;” statement in plpgsql

I have a plpgsql function to create child tables using table inheritance in PostgreSQL like this: CREATE TABLE parent_table ( value integer, end_time timestamp without time zone ); CREATE OR ...
1
vote
1answer
89 views

Optimizing query in PostgreSQL that tries to match a string and matches a timestamp range

I am building a database in PostgreSQL for financial data, where the table looks like this: create table fin_data( brokerid text, stock int, holding bigint, stake float, value ...
0
votes
2answers
185 views

Have DB users share tables

I want to have 2 users of the DB (user_app and user_migration), one for executing migrations (which will have more privileges, like creating/dropping tables + basic usages) and one that the overlaying ...
4
votes
2answers
3k views

How to deal with large offsets in select?

Table jtest with 200k rows, each row contains jsonb { id: "<uuid>", key: <index> } (<index> is integer 1-200k incremented per each row). There is also btree index on data->'key'. ...
1
vote
1answer
45 views

PostgreSQL - 1 billion rows table cannot get vacuumed despite no errors

I have a statistics table that should be processed in a scheduled task to remove rows and count them into a separate, smaller table. Unfortunately the cron task failed for some period of time and the ...
1
vote
1answer
295 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
72 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 ...
18
votes
2answers
20k views

Convert right side of join of many to many into array

When using join on many to many relationship the result is split on multiple rows. What I'd like to do is convert the right side of a join into an array so the result is one row. Example with 3 tables:...
5
votes
2answers
6k views

Get last modified date of table in postgresql

I want know the last modified date of table in postgresql. In SQL Server can get using SELECT modify_date FROM sys.objects How to get same thing in Postgres? I am using Postgres 9.4
2
votes
1answer
2k views

Automatic recovery of the failed postgresql master node is not working with pgpool II

I am new to Postgresql and Pgpool II setup. I have configured the Postgresql HA/Load balancing using Pgpool II and Repmgr. I have followed the link to do the setup. The setup consist of 3 nodes and ...
2
votes
1answer
620 views

PG_DUMP on Replica DB server Error

I have a master - slave configuration with 9.4 version, but there is no WAL streaming replication configured. Customer simply copy xlog files on network share and replica applies it. I need to run ...
0
votes
1answer
112 views

Having two data folder in my postgresql setup- Postgresql

I am using centos 6 and installed Postgresql_9.4 using the following commands yum install postgresql postgresql-contrib postgresql-client pgadmin3 yum install postgresql-server After that i ...
1
vote
2answers
300 views

Why isn't pg_restore --create working?

I backed up a database called app_data, on a 9.3 server, with this basic command: pg_dump -n public -F custom app_data > app_data.pg.dump Then I try to restore it on another server (running 9.4) ...
0
votes
1answer
89 views

Postgres 9.4.5 small table grows into huge size - periodic VACUUM FULL needed

We are running a PostgreSQL database on Windows Server. The database version is 9.4.5 (it's been running stable for a long time now so we aren't updating it). Some of our tables have millions of rows,...
1
vote
2answers
34 views

Unexpected behaviour for GROUP BY query

CREATE TABLE public.temp ( id integer, a integer, b integer, x integer ); select x, a, b from temp group by id, a, b; ends up with expected error: ERROR: column "temp.x&...
4
votes
3answers
9k views

Pushing or appending to JSON array in PostgreSQL 9.4

I have table where one of the fields is JSON array. I need to append received JSON array into that field without overriding existing values. Something like that: CREATE OR REPLACE FUNCTION add_array(...
-1
votes
1answer
25 views

Single query to replace a value in multiple tables with same column

I am using Postgres SQL. I am having multiple tables say table1, table2, table3, table4, ... with a common column say name in each of the tables. I want to replace a the value 'Jhon' with 'Jimmy' in ...
2
votes
1answer
6k views

How to create tablespace if it does not exist

When I create a new schema in Postgres through executing a script in psql I want to put it in a tablespace. If this tablespace does not exist then I want to create it first. Since the default SQL does ...
5
votes
1answer
2k 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 ...
8
votes
4answers
25k views

Select rows for which at least one row per set meets a condition

I have following table: create table test ( company_id integer not null, client_id integer not null, client_status text, unique (company_id, client_id) ); insert into test values (1, 1, '...
0
votes
1answer
120 views

Stored procedure for adding cascade

I have a DB with 180 tables, and I was doing a logical deletion, now I need to do some testing with physical deletion. Is there a way to alter all my tables and to drop their fk's and add then add ...
5
votes
3answers
4k views

How to reinstall PostgreSQL over an existing installation

I am trying to upgrade an installer created with NSIS that installs postgresql. It used to install postgresql 8.3 but we want to upgrade the DB to 9.4.4. The old version on of the DB used to be an ...
0
votes
1answer
169 views

PostgreSQL 9.4: Index Only Used For Lower INET Ranges

For some reason queries on the high range of are extremely slow and queries on the low range are extremely fast. My index is an adapted version of the answer in Optimizing queries on a range of ...
1
vote
3answers
72 views

Why is the database size larger than sum of table sizes

I wanted to know why database size is a way greater than thesum of table size in a database? How does it change?
2
votes
1answer
41 views

Caveats of OUTER JOIN on nested JSON value

I'm writing a query which is supposed to find the elements from a list which DO NOT exist in the DB. My first attempt at this was to use a nested query where the first query fetches the ids, then I ...
1
vote
1answer
102 views

Does the Postgres 9.4.9 update fix MultiXactId “does no longer exist” in addition to “has not been created yet” errors?

Postgres 9.4.9 has a fix in it's release notes which reads (emphasis mine): Prevent possible failure when vacuuming multixact IDs in an installation that has been pg_upgrade'd from pre-9.3 (Andrew ...
0
votes
1answer
177 views

Postgres Installed in Linux (RHEL) and trying to access / connect using pgadmin (Windows)

I've installed and configured postgresql in Linux successfully, 1. postgres user created 2. new db created successfully 3. I'm trying to connect using pgadmin III (Windows) 4. Following details are ...
2
votes
1answer
98 views

GROUP BY with subqueries for a Group?

Here's a simplified recreation of what I have: CREATE TABLE "DocumentStore" ( "ID" int NOT NULL, "Content" jsonb NOT NULL, CONSTRAINT documentstore_pk PRIMARY KEY ("ID") ); INSERT INTO "...
2
votes
1answer
156 views

Postgres - Querying Min, Max and getting associated columns data specific to those result of those rows

I have query in which returns averaged values by site and month. What I want to know is the min and max of those monthly averages by site and (the difficult part) the month when each occurred. Here ...
6
votes
1answer
2k views

Improving sort performance in GROUP BY clause

I have two tables in Postgres 9.4.1 events and event_refs with the following schemas: events table CREATE TABLE events ( id serial NOT NULL PRIMARY KEY, event_type text NOT NULL, event_path ...
5
votes
2answers
2k 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 ...
0
votes
0answers
18 views

create dynamic table in store procedure without using any paramaters

CREATE OR REPLACE procedure create_table_dateWise21() LANGUAGE plpgsql as $$ BEGIN EXECUTE format(' CREATE TABLE IF NOT EXISTS test21 ( nodeip text, interface text, nodeip_interface ...
0
votes
1answer
174 views

How to extract the grants on default roles of PostgreSQL 10: pg_read_all_settings, pg_read_all_stats, pg_monitor and pg_signal_backend?

I have a Postgres instance of version 10 running on AWS RDS. I have a user there with default roles granted to it. I want to create the same user on my sandbox where the instance is of version 9.4. ...
0
votes
1answer
931 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 ...
0
votes
0answers
41 views

How to take backup of PostgreSQL 9.4 database to remote server using pg_basebackup?

We want to take the logical and base backup on different server machine in the network. Currently both backups are running on same server machine causing space issues. We have the scripts in place ...
3
votes
1answer
9k views

How does unnest() treat NULL values and empty arrays?

I am trying to achieve something like LEFT JOIN with array unnest() function. I want the query to return a row with a null value if the array is empty. Therefore, by using a CASE construct, I wanted ...
0
votes
0answers
29 views

WAL Archival not working in PostgreSQL V9.4.9 DB server

Our DB servers are on VM, during planned server restart for system patch deployment our Master DB server crashed. We have taken the clone of Primary Slave VM and restore the Master DB using last ...
5
votes
2answers
12k views

How to create Postgres DB with case insensitive collation

I am using the postgres version 9.4.1 64-bit on windows 7 64-bit. I need to create DB with the setting "case sensitive = OFF" but couldn't make this work. I refereed already many question on SO and ...
1
vote
0answers
20 views

Postgres Major version upgrade 9.4 -> 9.6. PITR compatibility across versions?

Just dug into postgres documentation and couldn't get a clear answer on this. With Debian Jessie hitting EOL for Debian LTS in June, we are upgrading our database servers along with our other servers ...
1
vote
1answer
2k views

Batch for PostgreSQL query

1) The pgpass.conf (of PostgreSQL) is: localhost:5432:*:postgres:mypassword I have no experience with batch and I have spent the day searching in the net. I'm using Windows - if that matters. 2) I ...
2
votes
2answers
5k views

Postgres requested WAL segment has already been removed (however it is actually in the slave's directory)

I am using repmgr as my replication tool. On the slave I keep getting an error: requested WAL segment has already been removed When I check the Master indeed it is not there; however, it is in ...
8
votes
1answer
1k views
2
votes
1answer
796 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 ...
3
votes
2answers
17k views

After fresh install getting `psql: command not found`

I downloaded and installed PostgreSQL and PostGIS from KyngChaos for my OS X 10.10 install. When I run psql I get, bash: psql: command not found According to the site How to run psql on Mac OS X? it ...
0
votes
3answers
3k views

Get min and max row from table

From a table with the columns: id(pk), type_id, book_id, title_id, page_id, lines(int4), created_at(date) How can I select the row with min(lines) and max(lines) for (type_id, book_id, title_id, ...
37
votes
2answers
22k views

Refresh materalized view incrementally in PostgreSQL

Is it possible to refresh a materialized view incrementally in PostgreSQL i.e. only for the data that is new or has changed? Consider this table & materialized view: CREATE TABLE graph ( ...
24
votes
3answers
24k views

Query the definition of a materialized view in Postgres

I'm wondering how to query the definition of a materialized view in Postgres. For reference, what I hoped to do is very similar to what you can do for a regular view: SELECT * FROM information_schema....
6
votes
1answer
17k views

Postgres query to return JSON object keys as array

Is it possible to return a JSON object keys as an array of values in PostgreSQL? In JavaScript, this would simply be Object.keys(obj), which returns an array of strings. For example, if I have a ...
4
votes
2answers
2k views

Find unused indexes

I'm using the following query to find unused indexes: SELECT PSUI.indexrelid::regclass AS IndexName ,PSUI.relid::regclass AS TableName FROM pg_stat_user_indexes AS PSUI JOIN pg_index AS PI ON ...

1
2 3 4 5
11