Questions tagged [postgresql-9.3]

PostgreSQL version 9.3

Filter by
Sorted by
Tagged with
1 vote
0 answers
29 views

Postgres timezone issue when using postgres_fdw

I have a view stored on a foreign Postgres server (v 9.03) which pulls all of the data that was posted for the day. I have foreign tables (using postgres_fdw) stored on my local Postgres servers (...
user avatar
0 votes
0 answers
16 views

Restore backup of existing database configured for slony replication

How to restore a database backup (Postgres 9.3) in a existing database (slony replication activated)? I always delete the database and create a new one to restore, but in this case I can't delete the ...
user avatar
0 votes
1 answer
15 views

ERROR: column "FLAT" does not exit. Line 1. SELECT UPPER(FLAT). Kindly fix the trigger

CREATE OR REPLACE FUNCTION trigger_fct_tri_sv_tab_temp() RETURNS trigger AS $BODY$ BEGIN n.FLAT := UPPER(n.FLAT); RETURN NEW; end $BODY$ LANGUAGE plpgsql SECURITY DEFINER; CREATE TRIGGER ...
user avatar
2 votes
2 answers
297 views

calculate and return the ratings using sql

I have a table like this What I am trying to achieve is weighted rating. For e.g., in this table we have five star as 3, four star as 1, three star as 1, two star as 1 and one star as 0. So the ...
user avatar
0 votes
0 answers
87 views

Unable to load pg_hba.conf

I am having Centos 6.3. I have added IPs in the pg_hba file. I want to reload the pg_hba file but I am unable to reload. Following are the location of my directories /usr/pgsql-9.2/bin/pg_ctl /home/...
user avatar
0 votes
0 answers
25 views

Postgreslq, ¿How do I create a trigger that UPDATES, null values, under conditions?

I am doing a trigger, in which: You must first compare the "nro_persona" value of table "conductor" with table "event". If it exists, ask if the "nombre_completo&...
user avatar
0 votes
1 answer
165 views

PostgreSQL 9.6.17 COALESCE function error

I'm trying a SQL query with the function COALESCE in PostgreSQL 9.6.17, but it produces an error: invalid syntax for type double precision: "" Example query: SELECT COALESCE(date_part('...
user avatar
0 votes
1 answer
92 views

Unable to create view with temp table

Hi I am working on one project and want to create a view. I am using temp table in my query. can any one let me know how we can create such view ? Following is my query, I tried using WITH..AS , but ...
user avatar
2 votes
1 answer
109 views

Postgres table size is not growing after adding row

I couldn't see any growth in table size when I added a row in the relevant table. I see the same memory space even after adding 5 rows. Could anyone please help me and explain what I'm doing wrong?
user avatar
  • 33
0 votes
1 answer
273 views

Query fails sometimes on casting error

I have a query that can run on the same data set, and sometimes it fails and sometimes it succeeds The query is generated by hive metadata service, and I can't modify it. This is a simplified version ...
user avatar
  • 111
1 vote
1 answer
4k views

Could not fork new process for connection: Could not allocate memory

I need help to understand the cause of this error and how to fix it. I have a server running PostgreSQL 9.3. The cluster has around 1.000 databases. I use pgBouncer for pooling connections, and I set ...
user avatar
4 votes
2 answers
2k views

How to estimate the size of a PostgreSQL DB to be dumped through pg_dump

Similar to this question about MySQL -- I need to find out the size of a dump file prior dumping but for a PostgreSQL Database. I could not find any reference to this in the documentation
user avatar
0 votes
1 answer
50 views

Extract week number from character varying column where dates are in float values as well as in datetimestamp as well

I have a table in which the closure_date which is represented as a character varying column. Some of the dates are saved as "43684.5708564815" and also as "2019-05-24 18:51:17". Now I need to extract ...
user avatar
1 vote
2 answers
9k views

How to do a restore of a large postgresql database?

I'm working on restoring a backup from on-premise postgresql to another postgresql database hosted in AWS (rds-aurora postgresql, serverless). The data size is 170gb of compressed data. I earlier ...
user avatar
0 votes
0 answers
153 views

Single-tenant to multi-tenant: restore old database to schema in new database

I have a Django app which uses Postgres as a database w/ the django-tenants package providing multitenant functionality. This is a migration from what was originally a single tenant database with just ...
user avatar
-1 votes
1 answer
119 views

PostgreSQL Password Authentication Error...but cannot find account

I'm getting the following error within the PostgreSQL log file. When an external service is calling the DB for map routing, the following error appears. However, within “Login/Group Roles”,...
user avatar
1 vote
2 answers
642 views

How can I have an aggregate function in the WHERE clause?

Using PostgreSQL 9.3.24 Issue Just simple operation, but I don't know how to do that best simplest way. How can I display results only when sum of available_counts for same product_id is 0 and ...
user avatar
  • 153
0 votes
3 answers
104 views

Query takes too much time to execute

I add the Query Plan of my query. And this takes too much time to execute and give data. https://explain.depesz.com/s/4JtZ I want to reduce the execution time of this query. So require to optimize ...
user avatar
2 votes
1 answer
1k views

What to use instead of WITH ORDINALITY in PostgreSQL 9.3

Issue I have a query. Working with Postgres 11.5. copy ( select "HOBIS", "NAME", "URL", "IMG URL", "PRICE ORIGINAL" from ( select distinct on ("HOBIS") ...
user avatar
  • 153
0 votes
2 answers
230 views

Populate columns of table with other table that doesn't have relationship

Hello I have 2 tables in PostgreSQL: the 1st one "Products" id price factor1 factor2 the columns factor1 and factor2 they both are empty and I want to populate them with the data of another table ...
user avatar
-2 votes
3 answers
32 views

Finding ids where all the IDs are smaller than some date

Considering a simple table with id-datetime field. I want to remove all records, where datetime is smaller than some constant, but keep ids which have at least one record with bigger date. My first ...
user avatar
2 votes
2 answers
7k views

Pgbouncer getting auth failing when trying to connect using psql

I'm trying to configure the latest pgbouncer to work with postgres 9. I can connect to my db using psql with the right password but when I use psql -p 6432 I can't connect with the error message of ...
user avatar
0 votes
0 answers
2k views

Unit postgresql.service failed to load: No such file or directory

When i try to connect to my postgres database, i get the error below: could not connect to server: Connection timed out (0x0000274C/10060) Is the server running on host "10.2.1.96" and accepting ...
user avatar
  • 47
-1 votes
1 answer
3k views

pg_restore error while initializing. (Closed)

I am getting the error of: pg_restore: [archiver (db)] Error while INITIALIZING: pg_restore: [archiver (db)] could not execute query: ERROR: unrecognized configuration parameter "...
user avatar
0 votes
1 answer
220 views

PostgreSQL: relation would be inherited from more than once

Assume I have a schema which initially have several yearly sliced data like: schema: land_price - land_price_1990 - land_price_1991 ... - land_price_2010 Now I would like to create a parent ...
user avatar
  • 103
0 votes
1 answer
87 views

How is a table locked regarding UPDATE-SELECT queries in PostgreSQL?

Is it possible to combine these UPDATE queries? UPDATE myTable1 SET fld1 = myTable2.value FROM myTable2 WHERE myTable1.id = myTable2.id AND myTable2.style = 45; UPDATE myTable1 SET fld2 = myTable2....
user avatar
  • 3
0 votes
1 answer
87 views

Postgres record locking issue - could asynchronous UPDATES be a solution?

Is it possible (and sensible) to issue asynchronous UPDATEs to a postgres table? Here’s the issue. I have two process, both of which access the same table. Process “A” loads vast quantities of ...
user avatar
0 votes
2 answers
88 views

Speeding up Postgresql manual index reset?

I'm merging databases with each other. One problem I have is that both of the database tables have the same primary keys (starting from 1, but otherwise they are different records). To solve this, I ...
user avatar
  • 35
0 votes
1 answer
1k views

found xmin X from before relfrozenxid Y during vacuum

The vacuum of two pg_catalog tables runs into the following error and stops there. found xmin X from before relfrozenxid Y during vacuum I could find a lot of discussions about this problem but ...
user avatar
  • 908
2 votes
1 answer
530 views

pg_commit_ts growing in postgres instance

I have a customer who is trying to set up bidirectional replication with logical replication publication/subcription model across two instances with the help of last_update_wins and reported that with ...
user avatar
  • 425
1 vote
1 answer
3k views

Dynamic pivoting in PostgreSQL

I have a scenario where I have to pivot a list of rows to columns dynamically .For a particular order_number there might be several products. I want all those products to be in a separate column, ...
user avatar
  • 113
1 vote
1 answer
413 views

Can I do vacuumlo,vacuumdb and reindex in live production system without any downtime in postgres database

We are using a Postgres database (9.3) as our production database. It is generating huge lo objects. Due to this, the size is growing very fast. So we need to do database cleaning frequently. To ...
user avatar
2 votes
0 answers
50 views

the cost decreased but the time was practically the same, why?

Table definition: CREATE TABLE schema.mylogoperation ( id_mylogoperation serial, data DATE, myschema VARCHAR(255), column_var_2 VARCHAR(255), user VARCHAR(255), action TEXT, ...
user avatar
  • 133
0 votes
0 answers
304 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 ...
user avatar
  • 1
0 votes
1 answer
100 views

Huge DB (> 600M records/table) times out on query or hits memory limits?

Dearest DBA friends of Stackexchange, I am trying to write data from multiple sourcetables into a new table containing compound information that i wish to use as source for a later project. The ...
user avatar
1 vote
0 answers
4k 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 ...
user avatar
5 votes
2 answers
2k views

Performance of large transactions and concurrency?

If I have a multi-million row table and I run a transaction that updates 50k rows, what are the performance implications of this? Assuming it's indexed correctly, it shouldn't take long, but what ...
user avatar
0 votes
1 answer
215 views

Move a Postgres 9.3 database to a new server and update to 9.6 with minimum downtime

I have a PostgreSQL 9.3 database, which I have to move to a new server and update to 9.6 with minimum downtime. I thought I could : on the old Postgres, activate archive_command to copy WAL to a NFS ...
user avatar
0 votes
1 answer
488 views

Problem migrating PostGIS

I need to migrate Postgres 9.3 + PostGIS 2.1 to Postgres 10. I'm using CentOS 7.X Using pg_upgrade it should be quite straightforward, but the problem is PostGIS. Postgres 9.3 has PostGIS 2.1 ...
user avatar
2 votes
0 answers
2k views

There seems to be a postmaster servicing the old cluster. | Upgrade from postgres 9.3

I am working on upgrading postgres from version 9.3 to 10. I have encountered a lot of issues along the way since the install is way more custom done than it should be (People here LOVE complex and ...
user avatar
0 votes
1 answer
3k views

Postgres taking 3h to DELETE CASCADE

I have a Postgresql 9.3 with a "mother" table containing items, and a number of "child" tables containing parts of different kinds (e.g. item "AAA" is composed of 1000 parts "P1", 800 parts "P2", 40 ...
user avatar
  • 173
1 vote
0 answers
52 views

EDB Replication Server - Replicate from EDB Postgres Advanced Server 9.3 to EDB Postgres Advanced Server 9.6

I am new to EDB Replication Server and I was wondering if I can use this tool to upgrade from EDB Postgres Plus Advanced Server 9.3 to EDB Postgres Plus Advanced Server 9.6. Is this a bad idea? What ...
user avatar
2 votes
1 answer
55 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 ...
user avatar
  • 163
7 votes
1 answer
150 views

Postgres: query planner not respecting table inheritance constraint when querying for null

I'm having an issue with slow queries that is caused by the planner checking every inherited table instead of only the one with the constraint. I have a table with 0 rows called "search_result". This ...
user avatar
0 votes
1 answer
679 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 "...
user avatar
  • 123
1 vote
1 answer
1k 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 ...
user avatar
  • 413
0 votes
1 answer
52 views

Failover in Streaming Replication

I have one doubt on master to slave replication in streaming replication in postgresql. I confgured ONE Master and One Slave with streaming replication in my system If Master fails slave act as a ...
user avatar
1 vote
1 answer
10k views

If else statement on join query

i am working with a Postgresql database having two tables: professionals[professional_id,user_id,account_id] users[user_id email] accounts[account_id email] professionals [professional_id,...
user avatar
  • 37
0 votes
2 answers
2k views

Tracking database and table size growth

Is there way we can get the table size and DB size growth from last one month day-wise? We are able to give the current size of DB and table size.
user avatar
  • 41
3 votes
1 answer
6k views

Not a able to kill the DB session in Postgres RDS

We are trying to killing one of DB session but not able to do it. Tried with following command getting "TRUE" after issuing command. Still we can see the session is active. select ...
user avatar
  • 41

1
2 3 4 5
13