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
2answers
47 views

Postgresql, commas in string_agg function [closed]

Need FIELD like this, with ' and without , at the end of whole field. {name:'color',value:'red'},{name:'width',value:'300 ml'} Tried string_agg(distinct '{name:\'' || param_name || '\',value:\'' ||...
-3
votes
1answer
32 views

How to copy data from a table to another in O(1) time complexity?

I have a table Customer with fields id, account_balance, etc. At the end of every month at midnight I want to copy the whole list of customers with their account balance to another table(month, year, ...
-1
votes
2answers
31 views

How to get column values in where condition in sub query? [closed]

I am trying to get distinct list of values in where condition through sub query but it shows 0 rows in result set. Here is my query. SELECT a.topic_id, t.id as topicid, t.value as value, t.topic as ...
0
votes
0answers
7 views

How to find the master instance of a read replica in AWS RDS PostgreSQL?

I have an RDS Postgres instance whose role is Replica. I do not see the master in the same region and probably is in another region of the AWS account. How do I find the master for this replica? ...
2
votes
1answer
21 views

Gather values from different related rows with JSONB fields

Say I have the following table structure: jobs +----+------------+ | id | some_field | +----+------------+ | 1 | some_val | | 2 | some_val | | 3 | some_val | +----+------------+ events +----...
0
votes
1answer
21 views

Alternative defaults with a different table name

Say the original table has DEFAULT nextval('some_sequence'). I'm looking for a way to specify DEFAULT nextval('another_sequence') on a table view/alias so that I can just insert into the alias as ...
1
vote
1answer
34 views

How can I generate fake data that adheres to the PRIMARY KEY-FOREIGN KEY constraints of my schema?

I have a schema for which I would like to generate fake data. I can generate fake data using Python for each of the tables separately, but I don't know how to make sure the fake data generation ...
2
votes
0answers
31 views

Postgres wait_event_type Extension

I have a function that was written in Language SQL (not plpgsql) that is scheduled to be called nightly from pgAgent. It was running fine until last night. The function usually takes under 3 minutes ...
1
vote
1answer
14 views

Unable to Run psql in Windows Command Prompt

I have just installed postgreSQL and have set up the user postgres and its password. In the psql Shell I am able to login as normal however I can't login in Windows Command Prompt. When I run the psql ...
0
votes
1answer
23 views

How to set minimal token length for to_tsvector() function?

I parse xml documents with to_tsvector() function, and, sometimes, it produces tokens, which are shorter than 3 characters: '1':89,91 '2019':14 '25':4 I know, that to_tsvector([ config regconfig, ]...
1
vote
1answer
20 views

Run out of disc space on Debian server due to postgresql database

I recently run out of disc space no my server due to implementing some audit tools on Postgresql database. Two tables holding data about changes made by users quickly reached 16GB each, and totally ...
0
votes
0answers
23 views

Can I see the remote IP address for failed PostgreSQL logins?

We're running PostgreSQL on Heroku, with all the application logs going to Logentries, and I'm seeing around 4,000 failed login attempts per hour for a specific set of credentials: 2019-07-30T09:08:...
0
votes
1answer
93 views

.pcppass not working

I run pgpool-II as postgres user so I can replicate exactly what I did in one sequence here in this post. -bash-4.2$ echo "pgpool:$(pg_md5 test)" > /etc/pgpool-II-11/pcp.conf -bash-4.2$ cat /etc/...
1
vote
1answer
20 views

List Postgresql foreign data wrappers without the use of the `psql` command

As seen in a stackoverflow answer I can use the psql command and the \dew[+] command in order to list the foreign wrappers. But the use of universal database tools such as DBEaver, DataGrid, etc ...
1
vote
1answer
69 views

Hotel Booking Database design problem

I am currently designing (as an assignment for the course at the university) a database for online booking of hotels worldwide and I have stumbled into a problem. Here's is the conceptual design: ...
0
votes
0answers
23 views

How to set psql command line arguments in a sql script?

The broad question is can you set psql command line arguments within a sql script? Something simliar to SET In particular, Postgresql psql has a -S option, which turns on --single-line. According to ...
1
vote
1answer
18 views

Running .sql files in postgres (structured programs)

In oracle I could create .sql files and run them with a structure (like a stored procedure) with begin declare end; I did some tests, because I need to run scripts to fix problems, not is ...
0
votes
1answer
24 views

Does a read replica help if the problem are heavy queries?

The application that our team is responsible for is having ever more often DB performance problems. The application querying it doesn't throw lots of concurrent queries, the problem we have are rather ...
1
vote
1answer
34 views

max_stack_depth error in postgresql

I am fairly new to PostgreSQL and would appreciate any help and recommendations. I'm running the following function to update a table: $BODY$ DECLARE commercial_period date := (SELECT periods....
1
vote
0answers
30 views

Brand new postgres setup - what to check first?

My sys ops team have recently handed over a brand new set of postgres clusters (prod, stage, test etc). We don't have any DBAs and they are under resourced. Mistakes in config have been found before. ...
-1
votes
0answers
23 views

Restore PGAdmin 3 into PGAdmin 4

Is there an easy way to restore databases from PGadmin 3 into Pgadmin 4? Right now data on both, and I want to gather all my data on the same server. The port on PGadmin 3 is 5432 and 5433 on PGadmin ...
0
votes
1answer
14 views

Insert on conflict return false otherwise true in single query in stored function

At now I use code like that create function something() returns boolean as $$ begin if exists (select 1 from some_table where some_cond) then return false; end if; insert into ...
0
votes
1answer
22 views

pg_trgm index not used with commutator

PostgreSQL 9.6, on a table with ~100000 rows of text up to 3200 characters long. I have the trigram indexes: CREATE INDEX trgm_idx_gin ON test_trgm USING gin (t gin_trgm_ops); CREATE INDEX ...
0
votes
2answers
38 views

Storing the groupwise most recent related record

I have two tables, customers and purchases. There are a lot (thousands) of purchases per customer. I usually only need the most recent purchase for each customer, which is why I have the "...
0
votes
1answer
26 views

Using WHERE / AND in Joins

Can someone elaborate on how the following two queries would provide different results? That is, how the query is processed differently using WHERE or AND. select orders.*, accounts.* from ...
0
votes
1answer
21 views

Accessing nested array in json blob in PostgreSQL

I have a query I need to run that accesses a doubly nested array inside a json blob. The data is structure like the following: { "id" : "5", "data" : "[[1,2,3],[2,3,4],[4,5,6]....] } My query needs ...
0
votes
0answers
20 views

Can I get pg_dump to simplify the ACL commands?

When using pg_dump with a database with non-default schema foo like so: pg_dump --schema-only --schema='foo' -U myuser -d mydb -h myhost -W > mydb.sql The SQL dump file is full of these seemingly ...
0
votes
1answer
23 views

How to speed up Oracle to PostgreSQL data transfer

I have an Oracle schema that I am transferring to PostgreSQL with ogr2ogr. This particular table has 5 columns with number(22) each. There are some 300M rows. In under 10 minutes I can transfer this ...
2
votes
1answer
29 views

PostgreSQL ORDER BY clause effect different number of rows to return in pivot table

I've worked on "Seven Databases in Seven Weeks" recently, and when implementing the the task with building a pivot table using crosstab() - the task was already described here: https://stackoverflow....
0
votes
0answers
28 views

User-defined harmonic mean function performs worse than query in PostgreSQL 9.6

We created a harmonic mean function based on this blog post: CREATE OR REPLACE FUNCTION public.harmean_accum(numeric[], numeric) RETURNS numeric[] LANGUAGE sql STABLE COST 1 AS $h$ SELECT array[$1[1]+...
4
votes
2answers
101 views

How to optimize IN query on indexed column

I have a table with over 50M records. One of the fields is COLOR_CODE. I've set an index on the column COLOR_CODE like this: "mytable_colorcode_idx" btree (color_code) I'm noticing that when I run ...
0
votes
0answers
18 views

Postresql using subquery select in order by clause

Is it possible to order by a selected columnname? SELECT * FROM tablex ORDER BY quote_ident(( select column_name from information_schema.columns where table_name = tablex AND column_name LIKE '...
1
vote
0answers
20 views

high-water mark of number of sessions or connections

I was wondering if we can query/obtain the high-water mark of number of sessions or connections reached in a Postgres database. Is there a view or command that can provide this information. The ...
2
votes
1answer
40 views

How to tell if Postgres was built with the option to include LLVM technology for JIT?

Postgres 11 gained the ability to perform Just-in-Time Compilation (JIT) based on LLVM technology. The manual notes that this feature depends on Postgres having been built with the --with-llvm ...
0
votes
1answer
14 views

Postgresql Date Function Returning Range In Readable Format

I am wondering if it is possible to use postgresql's date-time , concat and case functions to give one of the following results: June 4th to 11th 2019 (where the 1st and 2nd date are within the same ...
0
votes
1answer
30 views

Optimization for table with text and time columns

I have a simple table with the following columns: id (int4) time (timestamp) name (varchar) service_name (varchar) For every service (there are not more than 500 different names), there are a lot of ...
1
vote
1answer
12 views

Creating dummy from JSON values and using it in the WHERE clause

The variable I'm interested in is an answer to a questionary, stored in a JSON column in a table called Questions. What I need is the number of users who've answered 'no' to a question coded as ...
-1
votes
2answers
40 views

EAV in postgresql

I'm building an application that needs to be deployed several times, with a relatively well organized data model. A table needs to be configured for each deployment with a small number of additional ...
0
votes
1answer
20 views

How to Bulk Insert sqlalchemy subclasses

I am trying to bulk insert SQL-Alchemy Subclasses into the parent table and their respective tables ie fruits tables -> Apple Table and so I insert a table of APPLE and it will insert both the row ...
0
votes
1answer
19 views

Altering Tables using Tablenames from other Table

I have a database with several tables and i have a table which contains all tablenames which I want to alter. I want to perform a query like this: ALTER TABLE (SELECT "TABLE_NAME" FROM "...
0
votes
3answers
22 views

Order of WHERE IN result for PostgreSQL

If I use the following selection SELECT * FROM users WHERE id IN (3,1,2) Can I expect the result to be in the same order like the provided ids?
0
votes
1answer
22 views

PostgreSQL: What is the best way to cast from from text to point[]?

I'm looking for a way to cast from a text value: '(180,90),(-180,-90)' into a point[] value: {"(180,90)","(-180,-90)"} What is the best way to do this? One possibility that I can think of is to ...
1
vote
1answer
39 views

updateable table from function

I have a plpgsql function that returns a table. One of its columns is dynamically generated based on the parameters passed to the function. Is there a possibility to have the user have the ability to ...
1
vote
1answer
18 views

How do you select “array”-type columns via the DBLINK function?

Using PostgreSQL, I can easily do a select statement to return a column that is an array of the datatype point. i.e. colA is the datatype point[] select colA from tbl Returns a column that contains ...
0
votes
2answers
57 views

Postgresql 11 - slow insert performance when table reaches ~5 million records

I have a table with ~6 million records that gets loaded on a postgresql instance in a small machine (with standard PC HDD). It seems to be a I/O problem, since there are free memory and processing ...
0
votes
0answers
49 views

OpenProject pg_restore - input file does not appear to be a valid archive

I'm trying to restore my OpenProject's database created with the command sudo openproject run backup this command created the file postgresql-dump-20190722131834.pgdump which I'm trying to restore ...
0
votes
1answer
45 views

slow count queries - what are we doing wrong?

Using Postgresql 9.6.14, running the following query: SELECT COUNT(*) AS "__count" FROM "crm_client" INNER JOIN "crm_role" ON ("crm_client"."role_ptr_id" = "crm_role"."id") INNER ...
0
votes
1answer
16 views

How to check and change function volatility in Postgres?

I have a function that I think is defined as volatile, but I now know I want it stable. How can I check the function volatility (volatile, stable, immutable) of a certain function? When a function ...
0
votes
3answers
42 views

Should I create a new table or save “static” data in all the tables? [closed]

I'm using Postgres as the database management system. Currently, I have a countries table, which has only four countries, i.e. four rows (might add one or two more countries, i.e. rows, later). --...
1
vote
2answers
36 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. ...