Questions tagged [postgres-10]
50 questions
52
votes
4 answers
ERROR: function round(double precision, integer) does not exist
I am in the middle of migrating some queries which have been running for ages with MySQL database which is now in Postgres having the same structure. I got stuck with a simple round function which ends with the following error message.
ERROR:…
radek lopatecki
- 623
- 1
- 5
- 5
17
votes
12 answers
Heroku Postgres: "psql: FATAL: no pg_hba.conf entry for host"
There are a number of Heroku CLI Postgres commands that all return the same error. For example:
$ heroku pg:bloat
psql: FATAL: no pg_hba.conf entry for host "...", user "...", database "...", SSL off
At least one of these commands has worked in…
steel
- 11,883
- 7
- 72
- 109
3
votes
1 answer
Join performance in postgres
I have 2 tables scheduling_flownode and xact_message with weak relation between them. I am trying to execute the following query
set search_path='ad_96d5be';
explain analyze
SELECT f.id, f.target_object_id
FROM "scheduling_flownode" f,
…
Varsha Teckchandani
- 33
- 4
2
votes
1 answer
How to create a conditional transaction in postgres?
I am trying to add new roles and modify existing ones inside a transaction like below:
BEGIN;
-- readonly role
CREATE ROLE readonly;
REVOKE CREATE ON SCHEMA public FROM readonly;
REVOKE ALL PRIVILEGES ON DATABASE some_database FROM…
Monku
- 2,440
- 4
- 33
- 57
1
vote
0 answers
Postgres autocomplete not working in version 10
Whenever i press up/down arrows, the command history is not getting displayed and whenever i press tab, autocomplete is not happening.
Aravind Ram
- 87
- 1
- 1
- 5
1
vote
1 answer
Return Array for each column (one query command)
This is my People table, and I'm looking to get one array for each column as output.
name
surname
Cell 1
Cell 4
Cell 2
Cell 5
Cell 3
Null
SELECT array_agg(name) AS names FROM people
I only understand how to return one array from the…
backed by
- 13
- 5
1
vote
2 answers
Query to search over array elements inside jsonb PSQL
I have a JSON node on which I have to write a PSQL query, My table schema name(String),tagValues(jsonb). Example tagValue data is given below
Name_TagsTable
uid | name(String)| …
Tarun Annapareddy
- 33
- 4
1
vote
1 answer
How to downgrade a postgres database from version 10 to 9.1?
I have a system that only supports version 9.1 of postgres and a client (company) has been managing their data with version 10. There are many tables and I need to know if there is a script to downgrade the database from version 10 to 9.1 .
Thanks…
Elermino
- 11
- 1
- 5
1
vote
1 answer
Error: Consult the last few lines of "pg_upgrade_dump_1.log"
I wanted to ugrade my postgresql database from v10 to v14
I have run these commands in windows command prompt:
C:\Users\mahajan>CD C:\Program Files\PostgreSQL\14\bin
C:\Program Files\PostgreSQL\14\bin>SET PGPASSWORD =1234
pg_upgrade -d "c:\Program…
mahajan
- 11
- 2
1
vote
1 answer
How to create user(read-write & readonly) and admin roles for an existing postgres database?
We have an existing postgres database in production with a superuser adm that is being used to do everything. Our web application connects to the database using the same user and also the administrators(for patching/updating etc.) use the same…
Monku
- 2,440
- 4
- 33
- 57
1
vote
0 answers
Separate PostgreSQL partitions join
I'm using PostgreSQL 10.6. I have several tables partitioned by day. Each day has its own data. I want to select rows from this tables within a day.
drop table IF EXISTS request;
drop table IF EXISTS request_identity;
CREATE TABLE IF NOT EXISTS…
Topper Harley
- 266
- 5
- 8
1
vote
0 answers
Hibernate error:Why Generation Type.SEQUENCE is not working with postgres10.6
I want to use custom sequences for my postgres DB but it is always using nextval of the default sequence "hibernate_sequence" while inserting the records into the table. I am using the below code while defining the entity.
@Id
…
user2942511
- 11
- 1
1
vote
1 answer
Same word giving true and false for same query in Postgresql
I'm having a same data in 2 different table columns. When I comparing it returns False.
select (case when 'Gurgaon / Delhi – NCR' = 'Gurgaon / Delhi - NCR' then true else false end)
But when copy and compare the same column text it returns…
Vanjith
- 520
- 4
- 23
1
vote
1 answer
Row security policy & partitions in PostgreSQL
A PostgreSQL 10.6 table has row security policy enabled on a field, say id. The table has multiple partitions based on id.
Before executing a select query, the value for the field was set using the SET statement.
But when the select query is run, I…
Athomas
- 533
- 1
- 6
- 19
1
vote
1 answer
Does updating a column with foregin key contraint can lock the referenced table?
I have one table, which is heavily updated in my system by process A. This is the simplified table:
db=# \d employee;
Table "public.employee"
Column | Type | Collation |…
Tomer
- 2,398
- 1
- 23
- 31