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
48 views

Best index for a join table?

I have two tables that are joined using a join table. I tried to found the best index(es). DROP TABLE IF EXISTS jobs CASCADE; DROP TABLE IF EXISTS devices CASCADE; DROP TABLE IF EXISTS jobs_devices ...
0
votes
1answer
485 views

How can I maintain a “virtual column” which is composed of data from 2 tables?

I have these two tables: CREATE TABLE events ( id integer NOT NULL, user_id integer NOT NULL, start_time time without time zone, end_time time without time zone ); CREATE TABLE users ...
0
votes
1answer
39 views

New query taking over 60% of running time

As part of a daily automated reporting system, we run a series of queries to check different operating parameters from our customers' assets tables. Last Tuesday I added a new check that works, but ...
0
votes
1answer
47 views

PostgreSQL recovery file syntax, ver 8 vs ver 9

In postgreSQL version 8.4 it seems that you can place the db server into warm standby mode with the following setting in recovery.conf restore_command = 'pg_standby archiveDir %f %p %r' In ...
0
votes
1answer
93 views

Efficient partial DISTINCT ON

I would like to get the most efficient query to give me all the rows that are distinct on one field (ID) but are identical on another field (validity). Let's set a concrete example: you have a table ...
0
votes
2answers
758 views

How do I duplicate constraints from one existing table to another existing table with an identical structure?

I'm using PostgreSQL. I have two tables, table_a and table_b. table_b is a structural copy of table_a but it was created without the constraints of table_a. At a later date, I want to add the ...
0
votes
1answer
768 views

Error connecting to the server:FATAL:couldnot open file“base\12135\11946”:No such file or directory

I have a issue from PostgreSQL 9.4 .The issue is given below: An Error has occurred: Error connecting to the server:FATAL:could not open file"base\12135\11946":No such file or directory. This ...
0
votes
1answer
279 views

Update a view with daily tables in Postgres

I have a view like this: CREATE OR REPLACE VIEW some_table AS select * from h20180101 union all select * from h20180102 union all select * from h20180103 etc... It's a union daily tables. The thing ...
0
votes
1answer
635 views

PostgreSQL warm standby: sync WAL files

I'm new to PostgreSQL (using ver 8.4.20) and need to setup a master and standby SQL server setup. I am planning to use warm standby setup. I have read the docs on PG and understand the basics, but a ...
0
votes
1answer
1k views

Install Only Pgbench and Not PostgreSQL Server

I'm looking to benchmark some custom PostgreSQL statements using pgbench but my problem with this is that upon trying to install pgbench it tries to install PostgreSQL server. The pgbench utility ...
0
votes
1answer
140 views

Converting jsonb values to array

I have a table with a column of type jsonb called attributes. I have rows with values like - { "id1": "something1", "id2": "something2", "id3": "something3", } and I want to convert ...
0
votes
1answer
92 views

PgPool: distro vs upstream?

I'm running PostgreSQL 9.6 and would like to trial pgpool. The yum repository shows the following options: pgpool-II-96 postgresql-pgpool-II Which version should i use ? The documentation on PgPool ...
0
votes
1answer
5k views

How to update JSONB array in postgres

I have a table with following definition: create table json_test ( my_data jsonb); Inserted to it values like this: {"details": [ { "f1": "", "f2": "free text" }, { ...
0
votes
1answer
377 views

How to speed up group by

I am trying to speed up the following query, which currently takes 14 seconds: SELECT COUNT(*) AS employee_count, e.jurisdiction_id, e.year FROM employee_employee e GROUP BY e.jurisdiction_id, ...
0
votes
1answer
49 views

Assign object owner to a role other than the creator automatically on creation for a database

Background: I'll start by saying I'm fairly new to postgres, I generally work with web apps and I know my way around SQL but I'm new to both postgres and database administration. I have however read ...
0
votes
2answers
97 views

PostgreSQL insert not occurring serially [duplicate]

I have come across a problem where PostgreSQL does not insert records at the end of the table (according to id, which is the primary key) So if, for example I have ids like 1521, 1522, 1527, the id ...
0
votes
1answer
701 views

JSONB: Finding an element with a null attribute in array of objects

With the following table in psql 10: CREATE TABLE logs(id serial, data JSONB); INSERT INTO logs VALUES (1, '[{"name":"test1","date":"2018-01-09"},{"name":"test2","date":"2018-01-10"}]'), (2, '[{"...
0
votes
1answer
335 views

query behaviour of composite gin index

CREATE TABLE products( products_id serial, location_id integer, --(not unique) idle_products jsonb --has 1000 elements ('[13,45,8976.....]'::jsonb) ); CREATE EXTENSION btree_gin; ...
0
votes
1answer
567 views

Insert PBKDF2 hash into PostgreSQL container

I am using the PostgreSQL docker image with the following Dockerfile: FROM postgres:10.1 COPY init-db.sh /docker-entrypoint-initdb.d/init-db.sh and an init-db.sh script with the following content: ...
0
votes
2answers
71 views

similar word concatination (merge) [closed]

I have many records where 2 columns contain words which shall be uniquely concatenated. all words shall be distinct in the result. The order of both text arrays shall be kept. Col1: mr. spock eats 13 ...
0
votes
2answers
67 views

PostgreSQL requirements for multi workstations [closed]

Can PostgreSQL run on network environment? Does it required a dedicate machine to host the PostgreSQL engine, like MS SQL? What am I trying to do is having multiple workstations can access to the ...
0
votes
1answer
422 views

Design: Postgres, Parent to Child / Child to Parent / Child to Child | JSONB

I am very new to database design. I am hoping for suggestion or solutions for a feature I am working. Note: We are majorly using JSONB type in Postgres Feature: I have a orders table which would ...
0
votes
1answer
337 views

how to prevent multiple pg_dump to execute at the same time

I am exposing a REST API to backup a Postgres DB. I have few questions related to that. I have seen most of these questions floating around google but could not find any suitable answer Is there any ...
0
votes
1answer
1k views

macOS homebrew: database system was not properly shut down; automatic recovery in progress

I'm noticing some errors in my logs when restarting PG 9.6. I also tried upgrading to 10.1, and the problem continues. This is on macOS via a homebrew installation. First, I shut down with "brew ...
0
votes
1answer
813 views

Insert trigger function from select query, plus static values pulled from the new.<record>

We're having an issue writing a trigger function that involves 3 tables: CustomerProfile, DropdownStats and OrderDetails. A simplified schema of each is as follows... CustomerProfile table...this ...
0
votes
1answer
353 views

Return true if any value in an specific key inside a jsonb array exists

The rows in my table look like facility | events ----------+--------------------------------------------------------------- 1 | [{ "id" : "1", "count" : "4", "severity" : "bad" }, { "id" : "...
0
votes
2answers
67 views

Index to speed up query

I have this SQL query that I have to speed up: SELECT DISTINCT A.attribute1, A.attribute2 FROM table1 A, table2 B WHERE A.attribute1 = B.attribute3 AND B.attribute4 = 'String'; What I am currently ...
0
votes
1answer
474 views

barman creating log files with incorrect owner

I've got the following config snippet in my /etc/barman.conf: [barman] ; System user barman_user = ubuntu ; Log location log_file = /var/log/barman/barman.log However, whenever the log file is ...
0
votes
1answer
26 views

Get increasing records over time

I have a table of car ids - id and their battery level - battery, which is collected at ten-minute intervals. My goal is the following output: total count of unique car ids whose batteries ...
0
votes
1answer
527 views

How can I visualize the entire delete cascade of an existing database?

I have a PostgreSQL 9.6 database with 68 tables. Many of them have foreign key constraints with ON DELETE CASCADE clauses. I would like to see the complete graph of the entire cascade. Is there a ...
0
votes
1answer
642 views

storing file paths in table

I'm working on a project which requires millions of images to be stored for their relative rows. I have decided to store the images on the file system and store the image's path in a separate table ...
0
votes
1answer
890 views

Splitting a csv bulk import to postgres into smaller blocks using psycopg2

I have a number of csv-files that I would like to import into my postgres-database. Since each file is quite large (200m-2bn records/file) I believe it would be better to add each file in smaller ...
0
votes
1answer
327 views

Why PostGIS GIST index is not used? [closed]

I am using PostGIS 2.2.1 with PostgreSQL 9.5.10. I have a table with a geography field for coordinates and I am making a simple bounding box query. The table has 1029 rows. EXPLAIN says it does a ...
0
votes
1answer
39 views

Form groups computing max - min depending on number of peers

This is table: CREATE table point ( datetime timestamp, value float) I have data in some range of time 7:00 - 7:15: 0 5 : 5 7:15 - 7:30: - - : null 7:30 - 7:45: 7 - :...
0
votes
1answer
134 views

How to easily show very long “canonical” name for each column in all reports?

Related: Does the length of a table name affect performance? For query writing purposes I want to have nice short actual column names. However, from my actual data source, I have an extremely long "...
0
votes
1answer
4k views

Postgresql: invalid input syntax for integer: “false”

Below is my table structure CREATE TABLE configurations ( id serial NOT NULL, key text, description text, value text, CONSTRAINT configurations_pkey PRIMARY KEY (id) ) WITH ( OIDS=FALSE )...
0
votes
1answer
2k views

postgresql select error: could not read block, Invalid argument SQL state: XX000

Postgres 9.6 on Windows 10 I'm creating a table filtered_table1 via a simple select operation on another table table1 (around 1.8 billion rows) and filtering it by some field create table ...
0
votes
2answers
275 views

Does a table with an index ever slow down?

We've got a single table where we store Events. The table has an index on it's primary key Assuming we're only doing simple SELECTs or INSERTs on that table - would the retrieval or insertion time ...
0
votes
1answer
53 views

Can database backup and restore using pgdump resolve transaction wraparound in postgresql?

I know the solution advocated is VACUUM. However, I was interested in knowing whether old versions of XIDs are marked as frozen when restoring a database dump using pg_dump. Also, this blogpost ...
0
votes
2answers
180 views

Improve performance in slow query in PostgreSQL

How can I improve the following query, since (in my opinion) it takes more time than it should, and I think I'm missing something in terms of query optimization(the query takes around 1.0-1.4 seconds):...
0
votes
1answer
770 views

Cascade delete on foreign key

Imagine 2 tables : CREATE TABLE a (a_id serial primary key, a_text text); CREATE TABLE b ( b_id serial primary key, b_aid int not null REFERENCES a ON DELETE CASCADE); If I delete from b ...
0
votes
1answer
846 views

How can I reset the counters from pg_stat_database view?

I know I can reset some counters by running: select pg_stat_reset() but some counters like xact_commit aren't reset. How can I do it? I've tried also: pg_stat_reset_shared('database') but doesn'...
0
votes
1answer
1k views

Errors encountered while Dump/Restoring PostGIS DB from Postgres on EC2 into RDS

I am migrating from a manually managed database on Postgres 9.4.10 and PostGIS 2.1 to an RDS instance on Postgres 9.4.9 and PostGIS 2.1. The restore seems successful, but the output gave me 27 ...
0
votes
1answer
270 views

How to convert a Microsoft SQL Server CE file (.sdf) to Postgres 9.6 /pgAdmin 4?

I have a database file data.sdf that I want to import into PgAdmin 4. I've considered using pgloader but the file format does not seem to be accepted. Does anybody have any hints on how I might go ...
0
votes
1answer
284 views

Nested writeable CTEs in PostgreSQL 10?

Can I nest writeable CTEs? WITH "parent_cte" AS ( WITH "child_cte" AS ( INSERT INTO .... ) ... ) ... I know that I can refer to a CTE in the FROM clause of another CTE.
0
votes
2answers
38 views

Numeric variables in psql

I am running a number of queries in psql using for a time interval where a part of the query goes like where g2.datetime between g1.datetime+'19 minutes' and g1.datetime+'21 minutes' i.e. I want an ...
0
votes
1answer
2k views

PostgreSQLpgagent “Running at: is not runing”

I have a problem to run pgAgent job for PostgreSQL 9.6 under ubuntu 16.04. The job is never executed. I already configured .pgpass and added: localhost:5432:*:postgres:mypass Also modified the ...
0
votes
1answer
1k views

Postgresql Slow performance query after table partitioning

I have a query which was executing in 30 secs before one of its table parititioned and after partitioning the query is ever running. Table that is recently partitioned is ysm_yield_session_master. ...
0
votes
1answer
41 views

Return matches that have at least one message

I have two tables, the match table keeps track of matches and the message_log table keeps track of conversations for a match. Right now I'm getting all the matches by the following query SELECT * ...
0
votes
1answer
67 views

Speeding up query performance when joining to first row in a table that meets criteria?

I am optimizing a query that spends 66% of its runtime joining to a table from which I need the newest line meeting criteria. What is the most efficient way to find this line? I do this twice from ...

1
194 195
196
197 198
242