Questions tagged [postgresql-9.6]

PostgreSQL version 9.6

Filter by
Sorted by
Tagged with
1
vote
2answers
404 views

Grant CREATE SCHEMA (only) to users

Imagine I have one shared database set up for my class that each student has the potential to access (access to the database is open and only requires kerberos auth). The public space is where I ...
0
votes
0answers
33 views

Combine different index types in postgres

we have query for fulltext where we have also another condition. Can you tell me what is correct usage of multiple indexes of different type? for instance: select * from foo_tbl foo where foo....
0
votes
1answer
974 views

postgresql - trouble to order a list obtained by string_agg

I obtained a list of numbers (and text) by a string_agg. For instance, that : "09,12,20,200,217,400,500,90,91,92,Exp3" This is the string_agg: string_agg(DISTINCT t_ligne.ligne_code::text, ','::...
1
vote
1answer
202 views

How to capture client host name in postgresql?

pg_stat_activity system view has client_address and client_hostname, but it always empty or null. Do I need to tweak anything to update the view?
1
vote
1answer
317 views

Index Optimization for Datetime comparing day of week and hour

I have a table of sensor observations with obs_ts timestamp, sensor_id text, sensor_val int, and to fill in gaps with data we have models by day of week and hour of day: model_id int, hour_of_day int, ...
1
vote
0answers
52 views

Validation within a transaction with all violations in response

I'm trying to solve a problem with some sort of offline integration where data occasionally (once in several days) comes in a form of csv files. It can be new rows or updates for existing ones. Total ...
0
votes
1answer
632 views

How to store LOB-image or PDF in Postgresql and what is the impact of having in Postgresql?

Please guide me to store LOB-image or PDF in Postgresql and let me know what is the impact of storing these types of objects on Postgresql database. Any help will be appreciated.
1
vote
1answer
130 views

Optimizing query performance with aggregation tables and partitioning

Our setup is a bit complex, so I will try to simplify what our tables look like but still try to describe the real problem: We have a large table (items) with about 500 million rows (grows with ~1....
0
votes
2answers
40 views

Most efficient way of updating table from another - Postgres

I need to populate a foreign key column based on the contents of another table, and both methods I've tried are prohibitively slow. I have the below tables (some columns omitted): ...
1
vote
1answer
20 views

Wraparound Transaction ID

Once the txid has passed the the xmin/xmax 32 bit limit, and assuming you've not suffered any issues with wraparound because you've been correctly maintaining your tables (plenty of vacuums, vacuum ...
9
votes
1answer
532 views

Best index for column where values are mostly the same

We have an integer column that currently consists only of 0 or 1 values. This column has now been used by a developer to store a unique 32-bit identifier on some occasions, and we need to be able to ...
0
votes
1answer
256 views

Generate data delta between two databases PostgreSQL

I have a big database with a lot of data ~50GB that spans around 40 tables - running PostgreSQL 9.6. This database is copied and sits in number of client sites without an Internet connection. ...
0
votes
0answers
45 views

Match and update records in a table

I have two tables table1: | id | A | B | C | date1 | date2 | |-----|------|-----|------|--------------|--------------| | 1 | 100 | 100 | 100 | 10-Jan-2019 | 11-Jan-2019 ...
1
vote
1answer
1k views

Relation does not exist while scanning schema

My Postgres database is getting slow while executing some function. But another database of identical design and with more data is much faster than the first one. I have recently migrated data from MS ...
0
votes
0answers
71 views

Is it possible to make thosunds of concurrent transactions even if the max_connections is set to 100 in Postgres?

This is maybe a dummy queestion but I couldn't understand to have thousands of concurrent transactions in a database even if the max_connections=100. Definition of max_connections from postgres ...
0
votes
1answer
716 views

Dump roles on AWS without pg_dumpall -g access (postgresql 9.6.8)?

We have a system on AWS RDS Aurora, PostgreSQL v 9.6.8 option. We are trying to move its roles to a new encrypted 10.5 instance, but we don't have superuser access that's required to do pg_dumpall -g ...
1
vote
1answer
59 views

Sort output of plpgsql function

I'm creating a function in Postgres 9.6 that compares two schemas with the same tables. I want to find tables which are empty in one schema but populated in the other. I'm avoiding the reltuples ...
1
vote
1answer
293 views

Replicate Postgres to Cockroach

We are planning to replicate our Postgres database to Cockroach. We are looking for a relatively simpler solution to achieve that. Currently, we are working on a Stream based replication (something ...
3
votes
2answers
556 views

Postgres CTE optimization with nested json_build_object

I'm trying to write a query that returns data from multiple tables and aggregates it in one nested JSON field. I feel like this would perform great on SqlServer but, as Brent Ozar wrote in this post, ...
0
votes
1answer
61 views

generate_series not working in single user mode

A table is not being populated with generate_series for below. backend> create table t1 (i int, j int); backend> insert into t1 values (generate_series(1,200000), generate_series(1,200000)); ...
1
vote
1answer
69 views

Combining separate columns into smallest possible related ranges

I'm trying to discard multiple records that may or may not overlap based on the smallest possible contiguous ranges. I thought of doing something similar to This, however the ranges are numeric ...
0
votes
1answer
125 views

Error with pg_upgrade and PostGIS

I have a PostgreSQL 9.2 setup (with Postgis) and am trying to upgrade it to 9.6. When performing the pg_upgrade, I get the following error: ERROR: could not find function "geography_analyze" in ...
1
vote
1answer
39 views

Optimizing SELECT query consistently showing up in slow-log

In a app called 'Links', users post links and photos of interesting content they've discovered recently (and others comment on the said postings). These posted comments under the photos are saved in ...
0
votes
0answers
2k 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 ...
1
vote
1answer
292 views

Postgres 9.6 (pgAdmin 4) - How to query daily statistics

Is there a way I can query through pgAdmin in a way I will get statistics for daily query usage? I need to find what queries are run against my api as I get huge network traffic. I'm looking for a ...
2
votes
1answer
102 views

How to get distinct day number with window function (Postgres 9.6)

I am trying to get the distinct day number with a set of data that contains different dates that are not always consecutive. For example, if I have this data: date | id 2019-01-01 | 1 2019-01-...
0
votes
0answers
29 views

Where each partition has only one value in the constraint column, can I avoid filtering?

I have partitioned a table in postgres 9.6 by date. Every date within a parition is identical. When I select data, a relatively expensive Seq Scan filter operation happens against every row in the ...
0
votes
0answers
585 views

PostgreSQL Optimize bulk values CTE and SELECT INTO TEMP TABLE to prevent OOM kills

Recently we moved some code from Rails way to raw SQL for performance reasons. We have some jobs which fetch some data from APIs, data can be in 100K+ rows of count sometimes. This data needs to be ...
1
vote
0answers
39 views

PostgreSQL database for GITLAB - Grant privileges only to user PUBLIC?

I manage a Postgresql database for a GITLAB application. The developer provided me with a backup file for migrating his database to a new server that contains the following statements: ALTER DEFAULT ...
3
votes
2answers
3k views

GRANT SELECT ON SEQUENCE succeeds, but to no effect?

I am trying to grant select access to sequences from one user/role to another. There are no errors when I run the command but once run, the second role is unable to view the sequences. I have run the ...
0
votes
1answer
134 views

Getting error on a particular query on a table in Postgres

I am getting the following error on one query in Postgres: select * from sampletable where sample_id = 1234; Error is : ERROR: could not access status of transaction <some-id> Detail: CLOG ...
0
votes
0answers
346 views

What makes ALTER TABLE X SET UNLOGGED; to be slow?

We develop and maintain a JEE Application with Postgres (multiple version of it as we've got different customers using different versions). The Code evolves over time; database changes are organized ...
0
votes
2answers
183 views

sum only different id's values with any aggreagte function in postgresql

I need your help. Let's assume that we have a table like below +---------------+-------+--------+----------+ | barcode | shop | id | entrance | +---------------+-------+--------+----------+...
0
votes
1answer
74 views

Will Postgresql statements execute synchronously if autocommit=true?

If a single application thread sends a series of INSERT statements and autocommit=true, will the statements be executed synchronously on the server? I read that on the postgres server side, "there ...
0
votes
0answers
108 views

Occasional slow insertion speed because of FK trigger

I have two tables in master-child relationship (several children per master). In one use-case a master with all of its children needs to be copied as new master. For this I'm using two select+insert ...
1
vote
3answers
1k views

Cannot revoke permissions or drop user in PgSQL AWS RDS

I want to drop the user asd from the DB. So, I've got two DBs: foo and bar and the user apparently has dependencies in both. The DBs have been created from the root user postgres, but all tables and ...
0
votes
0answers
2k views

PostgreSQL 9.6 won't start: “FATAL: could not write lock file ”postmaster.pid“: Input/output error”

I am using PostgreSQL 9.6 running on Debian 9.2. I cannot get my PostgreSQL up and running after the disk it was writing to became read only (it's on a separate disk from the OS). Here is the log ...
2
votes
1answer
186 views

How to insert into table multiple geometry rows from function JSON param?

My table: CREATE TABLE Points ( id integer NOT NULL DEFAULT nextval('point_id_seq'::regclass), info character varying(20) NOT NULL, my_point geometry(Point,4326), CONSTRAINT ...
4
votes
2answers
5k views

How to verify SSL always being used on Postgresql 9.6

I have a sensitive application with app server and db on separate machines, and in the case of the slave db, in separate data-centers. Although I believe my postgresqls are configured to always use ...
0
votes
0answers
391 views

Insert max numeric value in Postgres column

Suppose I have a table like CREATE TABLE public.products ( id INTEGER NOT NULL article CHARACTER VARYING(255) name CHARACTER VARYING(255) quantity NUMERIC(10, 3) ) As you ...
0
votes
1answer
449 views

Postgresql Replica is in sync but repmgr says node unattached

I've set up a replica of a Postgresql instance using repmgr, and the replica is totally in sync with the master instance and have been in sync since some days, but repmgr tells me replica isn't ...
-1
votes
1answer
175 views

Database volume completely full

/dev/mapper/pgdatavg-base 138304552 131262788 16384 100% /var/lib/pgsql/data/base pg_xlog is on a separate volume, and it's got plenty of space. [postgres@fpslbxmbstgl05 ...
1
vote
1answer
74 views

Equivalent Join Conditions Yield Vastly Different Results

The following query aligns test data with the closest truth data equal to or earlier than the test data for all possible truth_id's. There are very few truth_ids and very few test ids, but each id has ...
3
votes
1answer
2k views

PostgreSQL most efficient way to reference multiple tables

I am looking for the most efficient way to reference multiple tables in one table, when there is only one reference possible at a time. Which means tables A and B are referenced by table C, but both A ...
0
votes
1answer
59 views

Increase in number of temp files being generated after increasing checkpoint_timeout

I have pgbadger set up to analyze the log files from my postgres db. I recently changed a couple of parameters in my postgres config. I changed the checkpoint_timeout from the default value of 300 ...
1
vote
1answer
305 views

How to prepend an element to a JSON array?

I have a plpgsql function in PostgreSQL 9.6, which at some point has a text variable _x (guaranteed to not be NULL) and another variable _y which is a JSONB array of text (which might be empty). How ...
0
votes
1answer
83 views

Counting and grouping over multiple OUTER JOINs

I'm creating a view of advertisement logs (impressions, clicks, and clicks per impression). I have a simple table structure and some working queries but I'm having some trouble composing these into a ...
0
votes
1answer
52 views

Why is searching by one varchar column not working correctly on replica?

PostgreSQL: 9.6, master, replica. Async replication, wal archiving. We detected that if we execute a select query on a varchar column against a replica, then the results return no rows, but if we ...
1
vote
1answer
21 views

query on 2 fields but 1 is a unique value

postgresql 9.6, windows 7 Here is an example of a database : fld1 fld2 abc D123 xyz D123 abc D789 how can I select rows with fld1='abc' if/only for fld2 value exists just one time in the ...
0
votes
1answer
431 views

Postgresql schema only works when wrapped in quotation marks

I'm seeing an odd behaviour - if I don't wrap my schema name in quotes, the server returns [42601] ERROR: syntax error at or near "." The query is simply: select count(*) from "authorization"....

1 2 3
4
5
10