Questions tagged [postgresql]
All versions of PostgreSQL. Add an additional version-specific tag like postgresql-11 if that context is important.
10,516
questions
0
votes
0answers
16 views
Creating a custom thesaurus dictionary on RDS
We're deployed on RDS using Postgres 11.4, and I'm hoping that there is some way to define a custom thesaurus dictionary. I think the answer is a hard no, based on this StackOverflow thread
and this ...
3
votes
3answers
496 views
Does a query with a primary key and foreign keys run faster than a query with just primary keys?
SELECT something FROM table WHERE primary_key = ?
vs.
SELECT something FROM table WHERE primary_key = ? AND other_key = ?
Say that this is a scenario where the inclusion of other_key does NOT ...
1
vote
1answer
14 views
Build a single JSON value from two jsonb columns in two different tables
I have two tables. Both have a jsonb column with an array of objects, each containing the key student_id.
I am trying to build one aggregated JSON value from these jsonb columns.
Lets say table A ...
1
vote
1answer
17 views
Is it safe to restart PostgreSQL if process terminated for some reason?
I am writing a docker-compose config with a container with PostgreSQL in it. docker-compose has a configuration options called restart. If I enable it then whenever the Postgres process stops it will ...
0
votes
1answer
33 views
Is DEFAULT NULL and 'nullable value without default' completely the same?
ALTER TABLE test_table ADD COLUMN a DEFAULT NULL;
vs.
ALTER TABLE test_table ADD COLUMN a;
Both columns will set NULL if column a is not specified.
As far as I know, if I add a column into a table ...
0
votes
2answers
23 views
How to restore a .backup file from a single table on postgres?
I have a .backup dump from a single table, but it didn't work restore like a normal backup from an entire db. I'm creating this backup file with the following batch script bin\pg_dump -h localhost -p ...
4
votes
2answers
1k views
ALTER DEFAULT PRIVILEGES - permission denied
I have a problem with ALTER DEFAULT PRIVILEGES. I grant the permission:
ALTER DEFAULT PRIVILEGES
FOR USER user_name
IN SCHEMA schema_name
GRANT SELECT ON TABLES TO user_name;
in next ...
-1
votes
0answers
17 views
PostgreSQL : double entry board [closed]
I have a table of the type:
col1 col2 col3
N1 A C1
N2 B C1
N3 A C3`
How can we make a double entry array of the type under PostgreSQL ?
col3 ( C1 C3`...
(col2)
...
0
votes
1answer
207 views
PostgreSQL exits the shell when importing large data
I'm having this totally weird scenario: We have an archiving server based on PostgreSQL inside a Scientific Linux virtual server. I want to migrate the data (20+ GB) to a physical Scientific Linux ...
1
vote
2answers
46 views
How to filter hstore where values are not null in a trigger?
The easiest way to explain the question is in the usage of an audit trigger.
However, on insert it saves all values, including null values. I want to filter out the null values in the hstore(NEW.*). ...
0
votes
0answers
18 views
How to run chain_job in Postgres using PGagent [on hold]
Please help to find out on how to run chain_jobs in PGAdmin using PGagent. I am finding it most difficult and this is most required for my job.
(sys.dbms_scheduler.create_chain) in oracle sql
0
votes
2answers
19 views
Explain plan changes for insert vs select vs CTAS in Postgres11
We have a postgres DB on version 11.2. We are running the below query and the objective is to insert into a staging table. The number of records inserted are only 82. But the performance varies ...
0
votes
1answer
100 views
Filtering out duplicate domains from URL column using Postgres full-text search parsers
I have a PostgreSQL database containing pages and links downloaded by a web crawler, with the following tables:
pages
----------
id: Integer (primary key)
url: String (unique)
title:...
0
votes
0answers
14 views
Partioning a big table, or splitting it into seperate tables for speeding up queries (PostgresSQL 9) [on hold]
At work we are using Postgres 9, and I currently have to optimise a page, which loads very slowly (like 70 seconds). This happens mainly due to the queries executed against the database and that ...
0
votes
1answer
19 views
Function does not exist, but im pretty sure it does
So, i have a procedure..
create or replace procedure pro_update_last_read(in input_sensor_id integer, in read_time timestamp)
as
$$
begin
update sensor
set last_read = read_time
where ...
1
vote
1answer
24 views
Postgres full text search on words, not lexemes
I've got a table with columns of text that I'd like to search by word, not lexeme. More to the point, that I'd like to have indexed by word rather than lexeme. We've got error dumps with a lot of code ...
1
vote
2answers
34 views
Update all values for given key nested in JSON array of objects
I have a jsonb column data in a Postgres table my_table. It contains the following data:
[
{"id":"1","status":"test status1","updatedAt":"1571145003"},
{"id":"2","status":"test status2","...
0
votes
1answer
8 views
COPY command only works as command line argument, not as part of script
No rows are being copied when I try to copy a CSV from stdin. To be more specific, this works when writing the command on the cli, but not when read as part of a file. Why is reading SQL commands from ...
1
vote
1answer
449 views
Social network comments and posts model in PostgreSQL 9.6
I'm trying to figure out a data structure for migrating from MongoDB to PostgreSQL. Within my posts table in Postgres, I have a JSONB[] array holding comments for the post.
The thing is that each ...
2
votes
1answer
277 views
improve postgres query perfromance on windows with billions of records, TB data size
using Postgres 9.6, pgAdmin 4 (for running the queries) on Windows 10 64-bit with Intel Core i7-6700 CPU @ 3.40GHz, 16GB RAM, 1 TB C drive HDD , 2 TB D drive HDD. Postgres data is stored in D drive
I'...
0
votes
1answer
31 views
How to have CTE query recurse parent query result one by one?
I have this query used on Postgres to return place administrative levels.
WITH RECURSIVE hierarchy(name, pinyin, level) AS (
SELECT p.name, p.pinyin, p.level, p.upper_place_object_id_fk
from ...
1
vote
2answers
102 views
How to find resource intensive SQL operations in Postgresql? High CPU usage spike
I am getting frequent high CPU usage alerts from production DB server. As I want to investigate more into this by my own, I have collected the running DB queries for that high CPU usage time period.
...
0
votes
1answer
10 views
Interpolate values on Sorting in PSQL query
In the database, I have a field as status which can have a value of
1, 2, 11, 12, 21, 22
1, 11, 21 Stands for processing
2, 12, 22 Stands for completed.
I am doing this query to get all data with ...
3
votes
1answer
98 views
Conditional selection in FROM clause
I have two tables:
parent with columns identifier (pkey) and period;
child with columns identifier (pkey), parent_identifier and period.
There are additional columns, but I did not list them here as ...
0
votes
2answers
16 views
Logging user's id with the query he executed
So, I am trying to log not just the postgres user but his ID from my table (for example) Person (person_id) when he calls functions.
So far, from what I can tell I can get almost every information I ...
0
votes
1answer
7 views
Join relation on itself and pull distinct rows
I have relation A (reviewer_id, book_id, trivalFields)
Suppose at only 1 or 2 reviews exist for the book_id.
I would like a result set that
review_id (first), review_id (second), book_id.
Example:
...
2
votes
1answer
421 views
Repmgr: Error to promote Standby Cluster to Master Cluster in failover
I maked the configuration of the failover system based in Repmgr, but i don't haved success to promote the Standby Cluster to Master Cluster in case of failover.
Following my configurations, this ...
0
votes
1answer
23 views
Performance issues with CAST function
Observing this performance issue that can't be explained with EXPLAIN in a PostgreSQL 9.6 database.
There is currently a table public.users, which has a cast on it to output a record to JSONb. (e.g. ...
0
votes
0answers
12 views
Is my usage of triggers in postgresql json parsing causing a cpu bottlekneck?
I have recently started working with the Path of Exile API (https://api.pathofexile.com/public-stash-tabs) to track Items for sale by users
The API json is 4 layers deep before I get to the items ...
0
votes
1answer
314 views
Postgres - autovacuum on several big tables
I have an issue with autovacuum - I have several big tables (over 10 million records) which are updated very frequently (500 updates per second). Everything works OK until bloat happen :)
As I can ...
0
votes
1answer
18 views
Search one array element with another
I have got tables:
children_tab
id
name
lastname
parent_tab
id
name
lastname
children_parent_tab
children_id
parent_id
There is also parent view that return in field children_ids elements from ...
0
votes
2answers
30 views
How to select values side-by-side
I have a table like this
id_project id_field value
1 1 abc
1 2 xfe
2 3 ufj
2 4 osj
And I have to export ...
0
votes
0answers
5 views
Barman + repmgr failover process
I am about to build a PostgreSQL 11 cluster with Repmgr and Pgpool II, and I want to use Barman for backups.
The result will be a PostgreSQL cluster with one master node and two standby nodes:
...
0
votes
2answers
49 views
What is the last version of postgres with pgadmin 3?
For some reason I cant get used to the pgadmin 4 in web version. I found it very slow. For thing I want do fast I rather have pgadmin 3 even if that mean work with previous version of postgres.
So ...
70
votes
4answers
134k views
How to use psql with no password prompt?
I wrote a script to REINDEX indexes in a database. Here is one of them:
echo -e "\nreindex for unq_vbvdata_vehicle started at: `date "+%F %T"`" >> ${LOG_FILE}
psql -U ${USERNAME} -h ${HOSTNAME} ...
0
votes
1answer
20 views
Nothing is written to log in log_directory() for a PostgreSQL cluster
Running PostgresSQL 11.4 on Windows 10
I made a DB cluster in C:\Users\WindowsUser\AppData\Roaming\postgresql\Db_1
I registered that cluster to start on as a windows service on startup:
pg_ctl ...
2
votes
1answer
16 views
Time Difference Between actual total time and execution time in EXPLAIN ANALYZE
I have the following output of EXPLAIN ANALYZE query on PostgreSQL 11.5
EXPLAIN (ANALYZE, VERBOSE, BUFFERS)
SELECT actor.actor_id, actor.first_name, act.num
FROM actor
INNER JOIN (
SELECT ...
1
vote
3answers
316 views
How do I move all tables from one Postgres schema to another
I want to move all tables from schema xyz to the default schema public.
I can move individual tables with
ALTER TABLE table_name
SET SCHEMA public;
And I can get all tables with
SELECT ...
178
votes
13answers
439k views
connect to PostgreSQL server: FATAL: no pg_hba.conf entry for host
Hello I am trying to run a website sent to me but after doing so this error appeared
connect to PostgreSQL server: FATAL: no pg_hba.conf entry for host "4X.XXX.XX.XXX", user "userXXX", database "...
0
votes
1answer
36 views
How Does LIMIT Work in PostgreSQL? [closed]
In my query, I just want to "call" the first 20 items without any ordering and grouping. My query looks like shown below:
Select
r.spend,
r.date,
...
from metadata m
inner join
report r
on m....
0
votes
1answer
95 views
Can plpython3u open files on the file system?
I am trying to write a plpython3u function that should open a file in the file system and read some values out of it that get returned by a query. But I am getting a permission denied error when doing ...
0
votes
1answer
32 views
How to output string values in single quotes?
I'm trying to convert values stored in a json into its string representation.
The #>> operator works for this purpose for numbers, e.g.:
SELECT '{"a":[1,2,3],"b":["4","5","6"]}'::json#>>'...
0
votes
1answer
12 views
How to optimize count(*) using only foreign key index on PostgreSQL
I have a web app that extensively uses data tables that connect to the database to fetch data.
The problem though, it always needs first to count the total results before it could paginate the data by ...
1
vote
1answer
25 views
composite index on bigints
I have a User Relations table, which basically stores all the one-directional relations of users.
For example , User A follows user B ; User X blocks User Y
User ids:
the id of users are bigints.
It'...
0
votes
1answer
174 views
Restoring postgres backup with WAL-E fills up disk
I am running Postgres and WAL-E in a Docker container built from a custom image based on postgres:9.4. WAL-E works great and I have restored from backups a number of times. However, the backups take a ...
-2
votes
3answers
29 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 ...
0
votes
1answer
875 views
pgAdmin 4 Not connected to the server
I've installed Postgresql 11 and pgAdmin4 (version 4.5) and while I'm able to create tables in a new database, if I attempt to SELECT or INSERT into those tables I always get a message 'Not connected ...
0
votes
1answer
316 views
HA PostgreSQL installation using patroni and pgbouncer
I'm not having a problem yet, just searching for advise
I want to build installation of PostgreSQL on 3 nodes using patroni: master and 2 standbys (for failover only). There are PostgreSQL and ...
3
votes
2answers
3k views
open/execute huge sql file
I have SQL file using PostgreSQL with size about 7GB. How can I open/execute the SQL file? I have researched and tried using Navicat Premium, Razor SQL, HeidiSQL, Workbench. In all I get out of memory....
2
votes
3answers
235 views
Execution Performance Issue with Similar Query
I have been having some performance issues running a simple query on PostgreSQL on a table that has about 3 million rows with a join on a table that has about 120 rows.
If I run a query only on the ...
