The Stack Overflow podcast is back! Listen to an interview with our new CEO.

Questions tagged [postgresql]

All versions of PostgreSQL. Add an additional version-specific tag like postgresql-11 if that context is important.

Filter by
Sorted by
Tagged with
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
22 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 [on hold]

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
17 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
276 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
101 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
420 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
312 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
48 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
311 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
438k 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
869 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
314 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 ...
1
vote
1answer
46 views

Should I not use camelCase in my column names?

I am creating a GraphQL API using Node.js, which foces me to return all the field names in camelCase. In my PostgreSQL database, I currently have all my columns named following a camelCase convention,...
0
votes
1answer
20 views

Inline expansion of inline composite expression

I want something like this: SELECT 1, (CASE WHEN TRUE THEN (2, 3) ELSE (4, 5) END).*; ...hoping to get 1 | 2 | 3. But in PostgreSQL 11 this query throws: ERROR: record type has not been ...
0
votes
1answer
21 views

How to optimize query for text array in PostgreSQL using `@>` operator

I have a column of type text[] and I want to search over this column using this SQL operator @> Note: @> operator, it filters the data as such ['a','b'] in ['a','b','c'] The ...
1
vote
1answer
315 views

Postgresql 11.5 installation problem

While installing PostgreSQL I keep getting the message: "Problem running post-install step. Installation may not complete correctly The database cluster initialisation failed." This seems to ...