Questions tagged [postgresql]

All versions of PostgreSQL. Add an additional version-specific tag like [postgresql-13] if that context is relevant.

Filter by
Sorted by
Tagged with
2
votes
2answers
50 views

PostgreSQL not SELECTing by RETURNING value of INSERT

I have a query that's supposed to insert a new row, then return it. Normally a RETURNING would suffice, but there's a lot of other JOINed data must be added, so a SELECT seems to be necessary after ...
4
votes
2answers
376 views

In postgres is there any real drawback on using a json field over multiple tables and one to many relationships?

In our current application we are basically going to provide something called "translations" for each field. So a table would originally be like: CREATE TABLE organisation ( id SERIAL ...
0
votes
0answers
12 views

How can I manage newly created tables permissions in the PostgreSQL? [duplicate]

I want to create default permissions on the schema and automatically assign these permissions to newly created tables. For this purpose, I've created a role and assign a user. Afterward, I've created ...
6
votes
1answer
302 views

PostgreSQL connection URI fails, but parameters work

Running postgreSQL 13.2 with ssl on. I need to connect to a database from a third party application that requires a connection URI, but it is not working. So I tested connecting with psql using two ...
0
votes
1answer
31 views

Memory Optimized Azure Postgres

I am trying to make sense of the "memory optimized" setting for Azure Database for PostgreSQL. Main memory data management can be viewed as quite different from secondary storage data ...
1
vote
1answer
25 views

PostgreSQL keeps asking for user password with “trust” authentication enabled

I created a user john with createuser and didn't supply any passwords for it, i.e. no -P option specified. I then proceeded to add the following line above all other lines in the pg_hba.conf file: ...
4
votes
1answer
71 views

Non-deterministic performance of query on select, from 1s to 60s on table with 1 billion rows

I'm trying to investigate why the performance of this query is so non-deterministic. It can take anywhere from 1 seconds, to 60 seconds and above. The nature of the query is to select a "time ...
1
vote
1answer
29 views

Is it more efficient to create check constraints on PostgreSQL table before or after bulk load?

I am creating a table in PostgreSQL and bulk load with data first. PostgreSQL's documentation recommends to insert large volumes of data first and then create indexes and foreign key constraints. What'...
0
votes
1answer
27 views

pg_ctl says “no server running” when I'm connected to postgres and running queries

EDIT: the original question can be found below. Basically, this is a misconception on my part of how pg_ctl works and if you're getting this message too (from pg_ctl), then the TLDR; is, as @jjanes ...
1
vote
0answers
16 views

Why does a GIST index on a cube column in PostgreSQL actually make K-Nearest Neighbor (KNN) ORDER BY queries worse?

Adding a GIST index actually seems to make K-Nearest Neighbor (KNN) ORDER BY queries on cube columns worse in PostgreSQL. Why would that be, and what can be done about it? Here's what I mean. In a ...
0
votes
0answers
14 views

Postgres - how to put DB in Maintenance mode [duplicate]

Sometime I need to do bigger update on DB, hoping my local user will not be interfering But happens that sometime is connecting to DB causing my scripts deadlocks ... Only (easy) way to address that ...
0
votes
0answers
39 views

PostgreSQL remote connection failing

I've recently installed PostgreSQL on my PC as a requirement for my DB class, and as a practice, I decided to try and setup a remote connection for it so I can access it via online services. But after ...
0
votes
1answer
30 views

Cascading PostgreSQL triggers between three tables

I have a ‘test’ PostgreSQL 12 schema, defined with initial tables: begin; drop schema if exists test cascade; create schema test; set search_path = test,public; create table jobs ( job_id serial ...
0
votes
2answers
26 views

Weird tables with numbers and underscores in PostgreSQL execution plans

I'm debugging performance problems with my DB and see some unusual entries in the DB log: 2021-04-21 01:07:33.857 UTC [14823] user@db LOG: duration: 933645.469 ms plan: Query Text: select systemv0_....
0
votes
1answer
24 views

Parsing DATE while copying csv file into PostgreSQL table

I have a long series of .csv files, which I want to import into a local database. I believe my query is correct, but there are some problems in parsing DATE and TIMESTAMP columns. PostgreSQL reads ...
1
vote
1answer
31 views

Best index for case insensitive match on one field and ordering by another field

I want to run a query like this: SELECT * FROM "core_user" WHERE UPPER("core_user"."email"::text) = UPPER('emailsearch@example.com') ORDER BY "core_user"."...
2
votes
2answers
94 views

How to set statement timeout per user?

I have multiple users in Postgres. I would like to set up different statement timeouts for different users. Eg: Guest 5 minutes and Admin 10 minutes. Is it possible in Postgres 11.11?
0
votes
0answers
13 views

PostgreSQL for user management and another database for file management (Blob storage)

I am working on a project with video hosting and online editing platform with a microservice architecture in mind. One of my teammates uses PostgreSQL SQL for user management, but I need to handle ...
0
votes
2answers
33 views

PostgreSQL: Can i use tuple in where clause?

Actually might be a simple question: Given 2 tables: table1: A,B,C (A1,B1,28471), (A1,B2,01244), (A2,B1,1283a), (A2,B2,82r7e); table2: A,B,D,E,F,G (A1,B1,18,1,6,8), (A2,B2,18,2,3,0), (A3,B1,18,7,1,4),...
0
votes
2answers
26 views

Postgres - should I keep the log file on the fastest drive

I wonder your recommendations / experience re database log file (pg_log) placement We are debating internally if it is worth to keep that on fastest (and expensive) SSD volume, or can be kept on ...
1
vote
1answer
14 views

Clear PostgreSQL logs in AWS Ubuntu Server

I am trying to clear the logs of an postgresql under /var/log/postgresql/ but I am getting error message as below: /var/log/postgresql$ sudo > postgresql-11-main.log -bash: postgresql-11-main.log:...
0
votes
2answers
64 views

How to make a function in postgresql with two parameters to return count from column table?

I'm trying to do a function with two in parameters year and code from a table to return the count of the given code and year. The table is something like this: CREATE TABLE orders ( id INT NOT ...
0
votes
0answers
16 views

Django and multi tenancy, data sharing

I'm working on a small project (CRM) using Python / Django and I'm using, each user have his own Schema, I would like to know how can I share some data between users ( ex: share user contact list ...
0
votes
0answers
20 views

Possible to include the create schema command in pg_dump?

I'm using pg_dump to export only the DDL from a database. It's working fine, however the create schema command is not included in the output file. Here is what I'm using: pg_dump -s -c -t myschema....
0
votes
2answers
27 views

Limit rows in Correlated Subquery

I have one big and two small issues with my query I need help with. First, I want a SQL (postgres if it matters) query that gets SUM of last two ratings of fruits and makes a new column for each fruit ...
1
vote
1answer
62 views

How do I recover data from the postgres data directory on my hard drive?

I had an old iMac which stopped working a while ago I take weekly backups of the database I was updating which contains feed items from several rss feeds I am still missing the data for the last week ...
0
votes
1answer
23 views

how to search key in jsonb type column in postgresql

I have a column in PostgreSQL with jsonb datatype. I need to check one key whether it exists in that column or not by executing query.
0
votes
3answers
38 views

Postgres how to update first row in the child table for all parent entries

I have a table parent table workflow and child table workflow_task having one to many mapping. workflow` workID workTitle 1800 vpInv1231 1801 vpInv1231 workflow_task id task_Type workflow_id(fk) ...
0
votes
2answers
34 views

Restrict insertion of new row if a row exists with certain field values

Using a Postgres DB. I have a table let say t with columns txn_type, txn_id. I want to add a constraint where it restricts to insert new row if I have a row with particular values existing in table. e....
1
vote
2answers
52 views

Why is ASC 100x faster than DESC and what can I do about it?

I'm on the cusp of understanding this, but I don't understand the fix that I need. The following query takes about minute, give or take, to return 20 records: explain (analyze, buffers) select "...
3
votes
1answer
34 views

Indexing options for Postgres for a table with billion records when you want to index only recent records

We have a table which has close to 1 billion records taking up to ~315 Gigs. This is just the size of the raw table without the size taken by any indexes. This table has a updated_at column which gets ...
0
votes
1answer
15 views

Postgres performing SEQ scan for specific user

I am trying to see why the following query is taking so long to execute (around 5min) for a specific user but for others with the same number of rows involved it is taking 2 / 3 seconds There is one ...
0
votes
1answer
22 views

INSERT INTO with subquery that might return 0 results

I have a status table that I am using to sync other long running processes, and my following query/system is functioning, but also filling up my error logs: CREATE TABLE status(subject_id UUID PRIMARY ...
0
votes
1answer
38 views

PSQL Bulk Insert into multiple tables

I have this logic that I want to construct a query either with plain SQL or with PL/pgSQL. So the logic is I have a large set of data, around 100,000 records that I need to upsert into two different ...
1
vote
1answer
31 views

Is it necessary to re-insert all the data in specific order, when I'm going to use the BRIN index?

I'm going to use a BRIN index to replace the B-tree index on a date/timestamp column in Postgresql-11. I never used it before. Since that a BRIN index can be more effective if the data is stored ...
0
votes
1answer
40 views

Does postgres optimise repeated expressions?

select column_1, (column_1 <-> cube(:input)) / (:input <-> cube(0)) difference from table_1 for this query, is calculation of :input <-> cube(0) repeated for every row? Also, for ...
0
votes
1answer
23 views

Anyway to view original query on top of views using postgres

For example, I have a simple query: SELECT * FROM information_schema.columns where table_schema = 'public'; Which turn out very complex, information_schema.columns is a complex view. Right now, to ...
-1
votes
1answer
32 views

Why is my database 5 times larger in size than all the individual files combined?

I have 1000 csv files that have a total size of 200 MB. When I import these files into a new database table, the final size is 1 GB. Why is this happening, and how do I reduce the size of my database? ...
0
votes
1answer
32 views

Date calculations give different results in different environments

This query: WITH RECURSIVE cte AS ( SELECT 0 n UNION ALL SELECT n + 1 FROM cte WHERE n < 20) SELECT n, ('2020-04-13 00:00:00 Europe/Berlin'::timestamptz - make_interval(days => ...
1
vote
1answer
46 views

Add a number of days to a timestamp

I'd like to add (or rather subtract) a number of days from a timestamp. For example from 2020-04-13 00:00:00 Europe/Berlin I want to subtract 20 days and I expect the result to be 2020-03-24 00:00:00 ...
0
votes
1answer
29 views

Postgres latest sequence id and uncommitted writes

Hoping someone could clarify a section of this article "scalable-incremental-data-aggregation" and the corresponding question. Assume Postgres 10 and up. In Postgres 10, you can use the ...
-1
votes
3answers
75 views

SQL query - avoiding use of joins

Consider the following SQL database: I have two questions that I'm struggling on. I'm not really sure where to start. Select the third largest salary from all the employees. No joins allowed. ...
1
vote
2answers
40 views

Deleting rows from child table without deleting from parent [closed]

I have 2 tables, connected with inheritance. I want to delete some rows from the child table. The deletion part works but it deletes rows from the parent as well: CREATE OR REPLACE FUNCTION ...
1
vote
1answer
27 views

Junction table Trigger after update in another table

I'm just trying to create a trigger that performs an update in a junction table. In principle, I have three tables with Many-to-Many relationship. Table a: a_id, a_is_active Table b: b_id, b_is_active ...
0
votes
0answers
22 views

Migrate MySQL spatial data to PostgreSQL(PostGIS)

We have a web system that uses MySQL spatial databases. We want to migrate from MySQL to PostgreSQL. Our database has geometry and point data types, 21 tables and its size is 1.6GB. I've been looking ...
-1
votes
2answers
54 views

Applying Multiple WHERE conditions on a single Column of a Table

I am using PostgreSQL Database and here is the Sample Data. It is a large dataset and I am using the first two rows for example. Price | Moduletype 172 | 1 173 | 2 What I want is to query the ...
0
votes
2answers
51 views

Possible to disable THE postgres role?

Postgresql 9.6, if it matters. We have an audit finding which says that the "postgres" role must not be used at all. After creating a new SUPERUSER role, is it possible to disable role "...
0
votes
2answers
23 views

How to compare time portion of dates for multiple dates

I have a postgresql database with various records which have a date column which looks like 2020-10-12 12:45:55. I am wanting to construct a query that looks for activity with timestamps that are on ...
0
votes
0answers
11 views

How to debug connection between ec2 and rds in aws?

In AWS, created 2 accounts A and B. A: EC2 instance -> Installed a postgres client B: RDS postgres cluster These 2 accounts were set transit gateway from each VPC. So they should be connect each ...
2
votes
1answer
58 views

Postgresql: Modify one value inside of a nested json column

I have a json column called "Parameters" in a "Assets" table. The jsons are nested and have the following structure: { "params1": {"k": 1.1, "efficiency&...