Questions tagged [postgresql-9.5]
Specifically for PostgreSQL version 9.5
306
questions
0
votes
0answers
28 views
Select query taking too much time in postgresql
I have the following table in postgresql 9.5 database on ubuntu 16 (4 gb ram, 2vCPU):
create table stat_data (
name varchar NOT NULL,
the_date date NOT NULL,
uopen numeric,
uhigh ...
0
votes
1answer
13 views
argument 'and' incorrect, sum values of attributes
excuse the disturbance, but a comparison does not give me a boolean value, because the sum of the attributes does not return an integer value, but bigint. How do you add up the values of three queries?...
0
votes
0answers
31 views
how to insert an if check in an insert
I have an after insert at the statment level, which must insert the positive or negative result in an attribute based on the count of the values of other attributes. But control structures cannot be ...
0
votes
1answer
21 views
how to get column count in postgreSQL
i have a query below
select emp_id,
sum(case when leave_type = 'Planned' then 1 else 0 end) Planned,
sum(case when leave_type = 'Not Informed' then 1 else 0 end) NotInformed,
sum(case when ...
0
votes
0answers
3 views
Postgres break table columns into multiple tables having unique rows
I have this table called transactions. It has two columns id and report_id. id is primary key.
I want to create two new tables transaction_ids and report_ids both with just one column id.
Both these ...
0
votes
1answer
14 views
How to calculate quantity of WAL in db without archiving?
I would like to find out a quantity of WAL files which are being used in PostgreSQL database in last 24h. I was assigned to do Log-shipping but before I do it I want to know how many WAL files are ...
0
votes
1answer
15 views
function postgresql
I am analyzing a function in PostgreSQL and I come across a function that takes two parameters but I did not understand how they called, on the second parameter
the function is the following:
...
2
votes
0answers
116 views
Restoring from PostgreSQL 9.5 dump to 10 is super slow
I am trying to restore database dump created on PostgreSQL 9.5.16 to PostgreSQL 10.7 and it is taking a lot of time to restore. Restoring the same dump to 9.5 doesn't take that much. So I am wondering ...
0
votes
0answers
21 views
DROP DEFAULT on psql restore fails because column not existing anymore
A dump creates a command to remove default values like:
ALTER TABLE IF EXISTS test.simulation ALTER COLUMN gid DROP DEFAULT;
When on restore the column with the default value do not exist anymore, ...
-3
votes
1answer
190 views
Postgres is Throwing a False Foreign Key Constraint Violation? [closed]
I am trying to delete a LocalTax record from Postgres. Currently, I don't think there are any Employee records that reference the LocalTax in question (8).
=> SELECT COUNT(*) FROM employees WHERE ...
0
votes
0answers
19 views
Postgis KNN in Postgres 9.5
I'm trying to use the Postgis KNN functionality as described here:
https://postgis.net/docs/geometry_distance_knn.html
I have one table with all point geometries. The table has many samples, and in ...
1
vote
1answer
49 views
Query performance depends on parameter value
In the system I'm working with there are many type hierarchies, where each type corresponds to a table. For example f_dp_rkkbase -> f_dp_rkk -> f_dp_inputrkk. To distinguish between different ...
0
votes
0answers
16 views
Connection lockup on syntax error Postgresql 9.5 on Ubuntu 16.04 server
I have postgresql 9.5 running on my server with mostly default configuration.
Whenever I'm connected to the server through localhost or 127.0.0.1 postgres fails to return the error, be it a simple ...
1
vote
1answer
161 views
max_stack_depth not changed even stack changed in limits.conf
The attempt of making changes to max_stack_depth using select set_config('max_stack_depth', '512MB', false); because of the error shown in postgresql log:
DETAIL: "max_stack_depth" must not exceed ...
0
votes
1answer
14 views
Why doesn't `vacuum full` release space immediately?
On Postgres 9.5 I had a table that grew to 30GB, but it was ultimately mostly dead rows. After cleaning it up with a vacuum full, postgres reports that the table size is now around 500MB. However, I ...
1
vote
1answer
47 views
How to (a) rewrite a query that JOINs three tables and (b) make its execution more efficient
I have three tables (A, B and C) with various data regarding mining facilities in them. But only one table (C) has two columns with coordinates. My final goal is to produce a table which pulls similar ...
0
votes
0answers
46 views
Slow query with order by join table
I have a SQL with multiple join left, because some data is empty.
However, I need to sort the data using a string column from a join table.
I thought about creating an index for this table, but this ...
0
votes
0answers
7 views
Incrementing copy of pg_stat_all_tables
This feels like a 'how long is my piece of string?' question.
I have just realised I have an unexpected table 'stats' in my 'logs' schema. This table is a copy of pg_stat_all_tables with an extra ...
3
votes
1answer
90 views
Issue with Aggregate Function
I want to insert a tuple in my leggeapprovata table, but this table takes the attributes from 4 other tables. One of these 4 tables is the votazione table, from which it takes 6 attributes. Two do not ...
0
votes
0answers
21 views
Performance issue of scheduled Java app after migration from Oracle to Postgresql 9.5
I have a strange issue, that I hope I can get some help on. I'm not too advanced in Postgresql so maybe I'm missing obvious things as well. Our company is planning to move from Oracle to Postgresql, ...
0
votes
0answers
37 views
What determine how much time the query will cost?
I'm developer a system, and the database was not made for me. In the beginning all query of the database are super fast, because i don't have any data. Along the project the database grown, but the ...
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 ...
-2
votes
1answer
28 views
postgres query to insert data in a table from other table using combination of two common entries of thecolumns in both tables [closed]
I have 2 tables t1 & t2 both containing 3 columns a,b,c the entries in column 'a' and 'b' are same but ordered differently in t1 & t2. I want to use combination of 'a'&'b' entries of t1, ...
3
votes
4answers
174 views
Postgres LIKE query using a GiST index is just as slow as a full scan
What I have is a very simple database that stores paths, extensions and names of files from UNC shares. For testing, I inserted about 1,5 mio rows and the below query uses a GiST index as you see, but ...
0
votes
0answers
53 views
Slow PostgreSQL query - how to improve it?
[Beginners question :-) hopefully on-topic here.]
In one of the customized reports of our system there is a query which is toooo slow (lasting 20+ minutes to return a result). Please help me how to ...
0
votes
1answer
509 views
How to repair corrupted PostgreSQL pg_toast error
The hard drive on my PostgreSQL 9.5 server died, but I managed to recover most of the data. I've restored the database to a solid state hard drive, and I can access most of the data. However, when I ...
2
votes
2answers
60 views
How to select blocks with sequential data and aggregate the ids
I have the following table:
id(int) startDate(timestamp) endDate(timestamp) plan_id(int) planned(bool) machine(int)
--------------------------------------------------------------...
0
votes
1answer
28 views
postgresql has more DB connection in staging servers than production server
We have postgresql 9.5 where we use it for an application confluence.
in staging and in production both max connection is same.
But we see staging uses more connection than production e.g staging has ...
2
votes
0answers
225 views
Does VACUUM FULL ANALYZE a partition locks the main table in postgresql?
I'm using postgresql 9.5 and partman for partitioning tables.
there is this table that has 30 partitions and volume of one of them are too higher than others. so I need to full vacuum this partition ...
0
votes
1answer
21 views
Update column value divisible by 100
Hello experts I need to update next column value with divisible by 100
2
votes
1answer
83 views
How can it be possible that sum operation is so much faster than count?
When I make a simple benchmark test in a table that has 11.065.763 records. I get the results below:
select sum(t.amount * t.exchange) from table t;
Finalize Aggregate (cost=658027.39..658027.40 ...
0
votes
1answer
212 views
SQL update multiple rows in destination table with same id but different values from other table
creditNote table
id invoice_id generated_at(timestamp) status
10101 111 2018-02-28 14:42:39.247 generated
23982 111 2018-03-30 11:11:11.247 ...
2
votes
1answer
100 views
Cannot run psql in postgresql 9.5
I am using Postgresql 9.5 on Ubuntu 16.04 LTS.
I receive the below error when I type psql:
a
psql: could not connect to server: No such file or directory
Is the server running locally and accepting
...
1
vote
1answer
145 views
Truncation vs Deletion When `session_replication_role = replica`
When I first run
SET session_replication_role = DEFAULT;
CREATE TABLE users (
id serial PRIMARY KEY
);
CREATE TABLE posts(
id serial PRIMARY KEY,
user_id INTEGER REFERENCES users(id)
);
...
1
vote
0answers
15 views
After a PC freeze (Windows 7) and disk integrity check, most data from PostgreSQL table was deleted
My development server is on a PC, using 64-bit Windows 7. My PC has been randomly freezing on me lately, requiring a hard reboot (holding down power button until PC turns off)
Yesterday, as I was ...
1
vote
1answer
44 views
Update “oldest” row
I need to update the "oldest" row in the database (for now, its limit 1, but I need to also be able to set it to n).
I'm essentially doing a constant stream of "update the oldest rows and retrieve ...
0
votes
0answers
34 views
Method\Function encryption in PostgreSQL
I'm primarily MS SQL Server user.
Designing a PostgreSQL database, I wanted to encrypt methods\functions.
Is there any way to do this?
Thanks!
1
vote
0answers
385 views
ERROR: invalid input syntax for type oid: “english”
While creating an index in Postgres I am getting an error:
invalid input syntax for type oid: "english"
LINE 1: ...s ON entity_attributes2 USING gist ( (to_tsvector('english',...
The query ...
1
vote
1answer
57 views
Should I stop my PostgreSQL server before reboot?
Every week I do an apt-get upgrade and dist-upgrade on the server where my PostgreSQL is running. After that, I do a sudo reboot now.
Should I stop the PostgreSQL service before this reboot?
0
votes
0answers
136 views
Running multiple postgres database clusters on the same server
We are currently running postgres 9.5 on rhel7 and have a one database cluster per server. I'm looking at adding multiple database clusters per host, and am wondering whether there are any ...
1
vote
0answers
20 views
Query that fetchs average values over timestamp range of time, on a certain value of another pair of fields
This is the situation, over the time our table reached around 800 millions rows. And it is going to get bigger and bigger. It is a service that collects metrics of energy (voltage, power, current, etc)...
1
vote
1answer
289 views
WAL files not being deleted and pg_drop_replication_slot('barman') causes [55000] ERROR: replication slots can only be used if wal_level > = archive
My database has stopped deleting WAL files and pg_xlog grows to eventually take all my disk space. I have to use pg_controldata and pg_archivecleanup to delete them manually.
I'm almost certain it'...
0
votes
1answer
184 views
Is it safe to move big table to another tablespace while working with it?
I have one big bloated table (real data size approx 65 Gb, 140 Gb on disk). Vacuum does not work cause of low space on default tablespace disk. So I want to clean the database from trash by moving fat ...
0
votes
1answer
1k views
Cannot login to postgres with newly created users
I have created a number of users in postgres using createuser --interactive --password but whenever I run sudo -i -u newuser I receive the following error:
sudo: unknown user: staging
sudo: unable to ...
3
votes
0answers
521 views
Setup a PostgreSQL HA with synchronous replication in windows
I am fairly new to postgres ans would like recommendations for setting up HA system in windows Server machines. My current plan is to run 2 server Primary (Data center) with postgres 9.6 and same ...
3
votes
0answers
260 views
AWS RDS postrgres massive disk use, small tables
I can't figure out why our AWS postgres server has consumed all of it's space. We just had to up the storage space allocated to it, but can't find any hint from postgres that it's using that much ...
0
votes
1answer
128 views
How to swap array values in PostgreSQL.?
How to swap array values in PostgreSQL. For example i have a table that has a column whose data type is bigint[]. It has following value in it.
value: {111, 222, 333}
My desired output is {333, 111, ...
0
votes
1answer
84 views
Postgres - Revoke database acess from user, but allow replication acess
The DB have a replication server, i need to revoke the permission on the user "user_bi" but allow the acess of this same user on the replication server!
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM ...
-2
votes
2answers
173 views
pg_ctl still pointing to 9.5.4 after upgrade to postgres 10 [closed]
I have updated postgres from 9.5.4 to the latest 10.4 on Ubuntu 14.
But after installing postgres 10 I am seeing
support@platform1:/usr/bin$ ls -lah pg_ctl
lrwxrwxrwx 1 root root 34 Jun 13 08:59 ...
2
votes
1answer
647 views
PostgreSQL - can I use logical replication from a replica's WAL?
I would like to use logical replication. However, as PostgreSQL works now, it can eat up the whole disk and then block the database. Especially in Amazon Web Services RDS, this is a problem because ...
