Questions tagged [postgresql]
All versions of PostgreSQL. Add an additional version-specific tag like postgresql-11 if that context is important.
2
votes
1answer
14 views
How to allow connection for edb database in Postgres
By mistake we have executed disallow connections for all databases after that we are not able to connect any of the database, see below for reference:
Executed Query:
edb=# update pg_database set ...
0
votes
0answers
32 views
Writing CSV directly to a BYTEA column from PostgreSQL tables
Assuming these two tables:
CREATE TABLE data (day DATE, kpi INTEGER);
CREATE TABLE reports (csv BYTEA);
Is it possible to (pseudocode):
INSERT INTO reports (csv) (SELECT to_csv(SELECT day, kpi FROM ...
0
votes
0answers
28 views
how to copy table data into different table structure in postgresql
My requirement is to copy data from one table of old database to new database, the problem is the new database have few different relation and structure compared to old database,
how can I copy data ...
0
votes
0answers
22 views
PostgreSQL 11: how to partition 1000 tenants
Setup
We have a multi-tenant app that has about 1000 customers more or less. When a customer churns we delete all their data after a period. We have a few tables that are pretty big and we're ...
1
vote
1answer
30 views
Why are some count queries so slow?
I have a table users with 2.6 millions of rows and SELECT COUNT(*) FROM users takes 2 seconds to succeed, but request for table stories with 5.2 millions of rows takes more than an hour.
Explains are ...
0
votes
1answer
20 views
How does to_tsquery and to_tsvector decide which characters to omit?
I was quite surprised to find that TO_TSVECTOR('english', '平仮名ひらがな'); results in a vector of '平仮名ひらがな':1. For a system that omitted all non-alphanumeric characters and emojis I threw at it, I expected ...
0
votes
0answers
27 views
Optimize query migrated from SQL Server
I have been working on SQL Server for more than 10 years, now I have a database which I want to confirm if it is viable to change it to PostgreSQL, this (simplified) query runs in about 100ms in SQL ...
0
votes
0answers
29 views
Parent/child table with include/exclude items
I have a table with parent-child relations. The relations can go n-level deep.
There is also a table with elements that belong to a group.
CREATE TABLE group_children(
id serial PRIMARY KEY,
...
0
votes
1answer
17 views
How to add a constraint to a table using a partial index?
I have a simple table:
create table item (
lan text not null,
disabled date
);
My aim is to ensure uniqueness for the lan column over all rows, where disabled is null.
I created a partial ...
-1
votes
1answer
10 views
Indices, Drop before Loading and Create after Loading
How do you drop all indices before loading new data into an existing table, and make new indices after loading the new data ?
COPY command is faster than INSERT INTO right ?
0
votes
0answers
9 views
Copy database from a server to local one with postgresql [on hold]
Please can someone help me to know how to copy a database from a server to a local one ?
I was following the instruction of this link http://www.postgresqltutorial.com/postgresql-copy-database/ but i ...
0
votes
0answers
18 views
“service postgresql stop” not working on Ubuntu 18.04
I have a build process that constructs a postgresql database server with some data and then needs to stop the service and create a tar file. The relevant snippet looks like this:
if [ $? = 0 ]
then
...
0
votes
0answers
41 views
Postgres bulk upsert performance
I am doing bulk UPSERT on a table with 30 million rows. The table has just two columns (varchar as a primary key and integer). The input data is imported to the temporary table first, and then bulk ...
1
vote
1answer
32 views
how to prevent UPDATE of existing rows/record but allow INSERT using PostgreSQL trigger?
SOLVED
Edit: Trigger function and trigger are working fine with no errors, problem was with my database itself, dropped the database, created new, it's working now.
How do I prevent UPDATE on any ...
1
vote
1answer
17 views
Force exact match from postgres ts_query
I've constructed a column of stem words called journal_tsv from the column journal_name. I've then created a GIN index on journal_tsv. Here's and example of the type of query I'm running:
SELECT ...
0
votes
0answers
12 views
Optimize query: Filter Values Contained in Multiple Ranges
Background
In the fiddle below, I have two tables. The first table contains a column with a numeric value (e.g. int). The second table contains rows of numeric ranges (stored in two columns that hold ...
0
votes
0answers
18 views
What are PostgreSQL Security Labels?
What are PostgreSQL Security Labels and how/where to use them compared to GRANT?
0
votes
1answer
22 views
Why is pg_stat_activity.query_start null for almost all queries? [on hold]
Why is pg_stat_activity.query_start null for almost all queries? What is the meaning of a NULL query_start field?
0
votes
0answers
19 views
Update column without dropping dependancies
I have a table with many dependancies such as: functions, views and materialised views. I'd like to update a column from an int to a bigint.
The issue I have is that doing so requires dropping a ...
1
vote
2answers
24 views
update older records in each group
There is a set of data, like
| id | serial | version | is_deleted |
--------------------------------------------------
| 10 | AAAAAA | 2019-04-14 15:28:08 | 0 |
| 22 | AAAAAA | ...
1
vote
2answers
22 views
Postgres - Where condition on nested joins table not working
I have 3 tables:
order_items, invoice_items and invoices
In order_items model:
has_many :invoice_items
P.S an order_item may or may not have any invoice items associated with it
In Invoices model:
...
0
votes
0answers
15 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 ...
0
votes
0answers
8 views
How to optimise PostgreSQL planning time
I have a small query using postgresql 9.6.11 and postgis 2.3.1+dfsg-2
select ST_Transform(ST_SetSRID(ST_Point(671699.9999988,6585300.5001232),32633),3006);
that is slower than I like for where it is ...
0
votes
0answers
19 views
Do UPDATE statement performance affect indexed column(s) in Postgres?
I have a table which is having millions of rows. While doing update I'm using primary key column in WHERE clause and it uses index scan as per the EXPLAIN plan (within BEGINand ROLLBACK block). I have ...
0
votes
0answers
22 views
Create trigger and check if value exists in a table, if exits raise exception [on hold]
I am trying to learn triggers in PostgreSQL. So Now I want to insert some records into a table. Before that, I need to check whether that records exist in table or not. If present raise exception, ...
0
votes
0answers
28 views
PostgreSQL 9.6 slow UPDATE on production server compared to local machine
I have a production vm with centos 7.6 under hyper-v and a windows 10 laptop as a dev machine and both are running postgresql 9.6, on my laptop 10k UPDATE queries only take 9 seconds while on the ...
0
votes
0answers
10 views
Is there a way to restore contents of pg_largeobject for BLOB data using pg_restore?
Product: PostgreSQL 10.3
We have a need to take a copy of our production database and restore that into a separate environment for testing and training purposes. The problem is that the database is ...
0
votes
1answer
27 views
PostgreSQL copy fails ERROR: missing data for column “column name”
We are importing bulk data i.e millions of records.
We observed that due to a null value for a column , the import is getting failed.
Is there a way we could skip the import error when a record is ...
0
votes
1answer
29 views
ON CONFLICT ON CONSTRAINT fail postgresql
INSERT INTO O_USER (ID, NAME, LASTNAME, EMAIL, USERNAME, PASSWD, DATE_REG, DATE_UPD, DATE_DEL, IS_DELETE, TIPO, FTP)
VALUES (%s,%s,%s,%s,%s,%s,DEFAULT,NULL,NULL,%s,%s,%s)
ON CONFLICT ON CONSTRAINT ...
0
votes
1answer
17 views
pg_xlog_archive folder size is very big
While doing DB operations on PostgreSQL pg_xlog_archive is consuming more than 150GB disk space with the option "archive_mode = on". How to cleanup/reduce pg_xlog_archive folder size.
What will ...
0
votes
0answers
16 views
Is there any global variable for PostgreSQL replication has configured or not
Is there any unique identifier or any global variable which can tell us if replication is configured in current PostgreSQL server. Other than this query
select pg_is_in_recovery();
The above ...
0
votes
1answer
21 views
Is there any unique identifier for PostgreSQL? How can the master identify its slave unqiuely?
Is there any way to have more than one PostgreSQL slave servers in the same system.
If yes, how come a master detect each server as different slaves from the below query
select * from ...
0
votes
0answers
8 views
ERROR: cannot update table “pg_enum” because it does not have a replica identity and publishes updates
I was trying to update the enum label using the following SQL query
UPDATE pg_enum SET enumlabel = 'Cash Recivied' WHERE enumtypid = '1203702' AND enumlabel = 'Cash';
but it throws an error
ERROR: ...
0
votes
1answer
17 views
How to remote connect with PostgreSQL 11 CentOS server?
My Server: CentOS 7 x64, PostgreSQL 11.2
My Client: Navicat, pgAdmin 4.5 . Error at client:
FATAL: no pg_hba.conf entry for host "14.248.254.146", user "postgres", database "postgres", SSL off
I ...
1
vote
0answers
18 views
How to measure durations (import, update) in Postgres DB?
we have a Postgres database which receives data by an extern web client. We would like to measure the duration of the UPDATE, INSERT and DELETE processes on our tables.
Until now, we used the ...
1
vote
1answer
20 views
PostgreSQL Index Only Scan can NOT return ctid
I have this table in PostgreSQL 11:
CREATE TABLE A (id bigint PRIMARY KEY, text text)
Now I want to fetch the ctid of rows meeting some condition like id = 123.
However, even using pg_hint_plan to ...
0
votes
0answers
13 views
Converting SalesForce IDs for Performance Reasons
I'm writing code for a data warehouse that extracts information from SalesForce. The format of the SalesForce IDs is like so:
001n000000UELLJAA5
Where each digit is a base-62 value denoted by:
a-z ...
1
vote
0answers
24 views
Schema for managing house rentals
I'm desiging a database schema that manages house rentals
Below is what I have so far
property table
CREATE TABLE public.property
(
id bigint NOT NULL DEFAULT nextval('property_id_seq'::...
0
votes
0answers
28 views
How to make a table invisible in PostgreSQL?
I'm using the declarative partitioning feature in PG10, so far so good. However, the partitions are also visible as separate tables and it's a bit visually distracting. For example:
Sales
is the ...
0
votes
1answer
55 views
SQL beginner trying to calculate total time in minutes; need help with WHERE syntax error
I'm trying to calculate the total minutes employees were on a system.
The data, stored as 'Action' looks something like below. Note that employees log on and off multiple times a day.
INSERT INTO ...
0
votes
2answers
31 views
Does adding a postgres index concurrently take up disk space gradually or all at once?
I'm adding a unique index (concurrently, over the course of hours or maybe even days, on a column of 5 billion bigints, all of them NULL) on a Postgres 9.6 server that's pretty low on disk space. I ...
0
votes
0answers
23 views
Query result not working with array type in PostgreSQL [on hold]
I have two tables which contains a column with data type array in PostgreSQL. The structure is like below:
tbl_tour_packages
tbl_header_images
I have a query which contains several joins. The query ...
0
votes
0answers
34 views
postgresql - Disallow getting list of databases
How can I disallow getting list of databases on the PostgreSQL server? That can be done (for example) using \l in psql.
1
vote
1answer
24 views
Syntax error when using exception inside for loop [on hold]
I am trying to create the following procedure on postgresql
CREATE PROCEDURE run_all_scheduled_tasks()
LANGUAGE plpgsql
AS
$$
DECLARE
rec record;
BEGIN
FOR rec IN SELECT * FROM some_table ...
0
votes
0answers
14 views
Postgres not pushing predicate down into union query parts
I'm trying to create a calendar app with repeating events which supports edits. I have a view that I'm trying to create a calendar app with repeating events which supports edits. I have a view that ...
0
votes
0answers
12 views
Restrict EDIT and DELETE actions on local database [on hold]
in my current project the user setups an application locally on his machine and interacts without any internet connection. All his actions in the application are stored in a database. The problem with ...
0
votes
0answers
33 views
Cleaner approach for Recursive CTE query
I have a table called entry_changes defined as follows:
Table "public.entry_changes"
Column | Type | ...
0
votes
0answers
18 views
PostgreSQL 11 Windows 10 Truncates All Tables on Shutdown
i have a new Windows 10 laptop with beefed up processors, 16GB RAM and 2TB SSD storage. I have installed PostgreSQL 11.2 on this laptop, and use it to compile large datasets from various sources and ...
-1
votes
1answer
20 views
ER diagram to Relational Schema (Many to Many relationship)
In this ER diagram, can anyone explain how we should convert the many to many relationships ('view', 'update' and 'diagnose') to Relational Schema and then SQL? What I think is that there shouldn't be ...
-1
votes
0answers
11 views
How to find by group of characters in a statement in Sequalize PostgreSQL? [closed]
I want to find data based on the group of characters from a statement
I have a filed called description whose data type is "string" which is present in a group module.
description value can be a ...
