A message from our CEO about the future of Stack Overflow and Stack Exchange. Read now.

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
1
vote
1answer
34 views

How to select functions that belong in a given extension in PostgreSQL?

I am trying to compose a SELECT from pg_catalog that pulls function name, its schema name and the name of extension that created this function. To pull function list I use (a simplified version) ...
1
vote
1answer
13 views

set -x equivalent for the Postgres cli/console/prompt/repl

Is there a "set -x" for the Postgres prompt/repl ? In bash when you want to execute a set of commands, and come back to check it after lunch you can do set -x command1 command2 command3 and bash ...
0
votes
1answer
30 views

Implementation of Rollback in PostgreSQL

How is the rollback command implemented within the PostgreSQL database? I wanted to know the implementation details and what exactly happens underneath when a rollback is executed, i.e. state of ...
3
votes
2answers
186 views

ENUM and ID as a Foreign Key

Using PostgreSQL 11. I've tried digging through both Stack Overflow and here and was unable to find an answer on best practices. I'm working on a database design and have arrived at a schema that ...
0
votes
1answer
73 views

Is the double dollar sign ($$) quotation method in SQL standard?

In PostgreSQL, strings can be quoted very flexibly using a pair of dollar signs ($) with a tag in between. For example, SELECT $f$ Hi $f$; Is this quoting mechanism a part of any SQL standard, or ...
1
vote
2answers
28 views

Postgres doesn't use expression index for group by

I'm doing a simple group by on a single column with a matching index and it works fine: create index on contacts (home_city); select home_city, count(*) from contacts group by home_city limit 50; ...
0
votes
1answer
59 views

Secure postgres 9.5 database packed on Windows desktop application

My desktop app includes a postgres database and a front app. I need the db to be accessed only by my front app. The credentials of this user are going to reside in the front app binaries. I realized ...
1
vote
2answers
57 views

Postgresql query is very slow for my big table

My database version is postgresql 9.5. create table if not exists request_log ( id bigserial not null constraint app_requests_pkey primary key, request_date timestamp not ...
0
votes
1answer
17 views

Postgres: change database name without interruption?

I have a production database that I'm told is "named incorrectly". Is there a way to change the name without interruption? The database in question is named something like prod_api_db and should be ...
1
vote
1answer
36 views

Relationship with optional in-between table

I am currently implementing a self-assessment tool. The requirements for its questionnaires are as follows: A questionnaire has one or more dimensions and every dimension has either one or more ...
1
vote
1answer
42 views

How to check if a value is in an array in a table

I have a table if key - array pairs and need to check if a string is an element in a given array in this table. I have managed to write an if statement to check if the element is in an array but if I ...
0
votes
1answer
17 views

auto_explain does not log

looks like i do it wrong. I turned auto_explain on with adding next valued to postgresql.conf: shared_preload_libraries = 'pg_stat_statements,pg_wait_sampling,pg_stat_kcache,auto_explain' ...
0
votes
0answers
14 views

How to improve Postgres performance using Prepare?

In this query, an inner join is repeated every time, imagine billions of orders and hundreds of stores... PREPARE findNextId(uuid) AS( WITH producedNextIds AS( WITH ordersOfStore AS ( ...
0
votes
2answers
27 views

Insert data from two table into another [closed]

I'm trying to generate and insert data from two tables in new one. I should take all data from M table where Type is 1 and all C data where type is also 1 and add it as shown in result Sample of ...
0
votes
1answer
25 views

Raise NOTICE, Raise INFO not saved to logs in POSTGRES

I searched a lot on google but no solution found, I am not sure what I am missing here. My intention here is to store the notice , info from this procedure to postgres logs (txt files) that are ...
0
votes
1answer
11 views

aggregating sums for specific time but would like to have multiple times in the same query

I am trying to sum the number of orders for two years and have them side-by-side but am not sure how to do this. To summarize one year, I currently have: select menu_item_id, menu_item_header,...
1
vote
1answer
27 views

How to troubleshoot poor trigram (gin) index performance?

I have a compound LIKE in a query that would have seemed to have benefited from a gin index however on two practically identical servers I see vastly different performance, with the worst case being ...
2
votes
1answer
32 views

Different sort despite same collation in Postgres

I am getting different sort results for items with diacritics even though the collation being used is apparently the same. So, after installing Postgress.app in my mac box for development I create ...
2
votes
1answer
29 views

CTE Insert then select with join

I'm trying to figure out why this query doesn't work, the goal is to insert an item, then return its ID and use that ID to get related data from another table, however it returns an empty record set ...
0
votes
2answers
21 views

Can I tell postgres to use a different “collation” for an integer column?

Let's say I'm using an integer column to represent an "enum" in my application code, for a "priority" attribute. 0=low 1=medium 2=high The app is operating for a while like that, and then I want to ...
0
votes
1answer
40 views

Postgres 12.1 uses Index Only Scan Backward instead of index when LIMIT is present

I have a medium sized table (~4M rows) of "functionCalls" which consists of 2 columns, input and function (both ids for another table): Column | Type | Collation | Nullable | Default ----------...
0
votes
1answer
43 views

Changing all character varying to text across all tables and columns

I'm trying to find a way to iterate over all of my tables and changing each charvar[n] to text and charvar[][n] to text[]. However, only found ways to change it for a specific table and column or ...
0
votes
0answers
15 views

Single-tenant to multi-tenant: restore old database to schema in new database

I have a Django app which uses Postgres as a database w/ the django-tenants package providing multitenant functionality. This is a migration from what was originally a single tenant database with just ...
1
vote
0answers
21 views

JDBC can't use the current primary database by setting multiple hosts

node1: 192.168.0.1 as primary node1: 192.168.0.2 as standby The JDBC string is setting as this: <url>jdbc:postgresql://192.168.0.1:5432,192.168.0.2:5432/mydb</url> Using repmgr doing ...
0
votes
0answers
18 views

Schema design for infinitely expanding variables

I'm putting together a schema for a progressive story that will track user choices. Because it will unfold over at least a year, I need a schema that is safe to handle constant additions. I have a ...
0
votes
1answer
16 views

Why do function inputs need an extra set on parens in this Postgres function?

This works. However, it fails with a syntax error if the "extra" parenthesis in the second function are omitted. Why? create function epochSeconds(timestamp) returns int as $$ select extract(epoch ...
1
vote
1answer
15 views

Vacuum increases bloat size as reported by Heroku's bloat SQL query

I am using using the Heroku command as described here to detect bloat in tables. One of our tables reported a bloat size of around 7GB, but running vacuum on it, then running the same bloat command, ...
0
votes
1answer
30 views

How to loop through JSON array of JSON objects to see if it contains a value that I am looking for in postgres?

Here is an example of the json object rawJSON = [ {"a":0, "b":7}, {"a":1, "b":8}, {"a":2, "b":9} ] And I have a table that essentially looks like this. demo Table id | ...(other columns)...
0
votes
2answers
16 views

slow index creation with gist and gist__int_ops

I am seeing performance issues with frequently updated / inserts table, and one of my hypothesis is the gin index I have on int array. The gin index was added since we have query of "X exists in ...
0
votes
0answers
15 views

Updating column based on reference column in postgresql

i have the data from three different sources which is 'CRM','PRM','MDB' and i need to update the fields which is null and update the fields which value exist in another source. Plese find the attached ...
0
votes
1answer
23 views

COPY TO statement in postgres outputting double backslashes

I would like to output a string with backslashes that is enclosed in dollar quotes to a file in postgres. For example, with the following query: COPY (select $$a\x5cb\x5cc$$ ) to $$c:\test.txt$$; I ...
0
votes
1answer
22 views

Does PostgreSQL have native, internal support for Roman numbers? (NOT for output/formatting!)

This is a long shot, but I need to ask: Does PostgreSQL have support for, instead of using Arabic numbers (0, 1, 2, 3...), it can use Roman numbers (I, II, III...) internally, for integers/serials ...
0
votes
1answer
28 views

Risk in creating a string domain

In an effort to more easily migrate from Visual Basic to plpgsql I'd like to create a string as a domain in Postgres. Is there any inherent risk in doing so? CREATE domain string AS TEXT; I ask ...
0
votes
2answers
19 views

Postgres - Union with different number of columns

I have 2 queries that needs to be run on a table. [SELECT *, 1 as query_cr_0 FROM invoices LEFT OUTER JOIN customers ON customers.id = invoices.customer_id WHERE (customers.id IN (64, 66, 63, 62, 65))...
0
votes
2answers
62 views

Efficient key value store in Postgres

I want to use Postgres(11 in CloudSQL) as an efficient key value store. I have about 200GB of dictionaries (average size is 10kB, the structure can be different and nested). I'm thinking about ...
0
votes
1answer
28 views

Partition table behavior for Postgres

I have created a partitioned table with 1 partition. below is the script for the same: CREATE TABLE public.ms ( id int8 NOT NULL GENERATED BY DEFAULT AS IDENTITY, a int8 NOT NULL, b int4 NOT NULL, c ...
0
votes
1answer
49 views

How do I get rid of the “LOG: could not receive data fr…” message with PostgreSQL on Windows?

(It didn't let me paste the full message into the title.) I run PostgreSQL all day, every day, on Windows. I don't normally look at the GUI window for PostgreSQL's server/daemon, but when I do, it's ...
0
votes
2answers
35 views

Locate multiple duplicate columns in PostgreSQL table

I am trying to report on duplicate records in a single table which has a unique key of app_cao_number. The duplicates occur if either: 1. The Passport field is duplicated; 2. The ID field is ...
0
votes
1answer
27 views

How to do automatic failover for PostgreSQL with application automatic switch?

There are two ways to try. Method 1 Use JDBC connect the PostgreSQL cluster (primary, standby 2 servers only): jdbc:postgresql://192.168.0.1:5432,192.168.0.2:5432/jiradb But failed: The ...
7
votes
1answer
119 views

How can I order rows returned by the RETURNING clause of a DML statement?

I am using this statement to get all the rows ordered by timestamp in ascending order DELETE FROM @tableName WHERE id = ANY ( SELECT id FROM @tableName ...
1
vote
1answer
42 views

LTREE extension does not exist after CREATE EXTENSION

I am trying to create a trigger that uses ltree column. CREATE EXTENSION IF NOT EXISTS ltree; Running SELECT * FROM pg_catalog.pg_extension gives the following output: extname |extowner|...
0
votes
1answer
41 views

Finding how many times a value is shown on a table - Postgresql

I am trying to find employees that sold the most different type of subscriptions. The tables are: Employee(PK employee_id, name, email, phone_number) Person(PK email, ...) Order (PK invoice_id, ...
0
votes
1answer
29 views

Create window (PARTITION BY?) out of column values

I have a table structured as: | date | key | value | |------------|-----|-------| | 2019-01-02 |S1 |20 | | 2019-02-04 |S1 |30 | | 2019-03-10 |S2 |15 | | 2019-04-07 |S1 |0 ...
0
votes
2answers
23 views

Work around average behaviour with nulls

I have two tables: Product (PK product_id, brand, model) Order (PK invoice_id, customer_name, customer_email, FK product_id) I am trying to find the product(s) whose number of times that product is ...
0
votes
2answers
30 views

Database table archiving

I am trying to create a stored procedure (SP) in order to archive a table within my production database which is about 1TB. The production DB runs on Linux SUSE OS and the destination location for ...
2
votes
0answers
54 views

pg_stats is huge and slowing PostgreSQL server down… What do I do?

I have two servers running PostgreSQL 10.11 that are configured the same, run on comparable hardware, contain the same databases and (mostly) the same tables. Various Nagios checks using ...
1
vote
1answer
21 views

Trying to understand TID defenition from documentation

I trying to understand what is TID and how it works. I found 2 definitions of TID in documentation: 1) from https://www.postgresql.org/docs/11/datatype-oid.html A final identifier type used by ...
0
votes
1answer
18 views

Could you monitor all changes made within a postgresql database by somehow subscribing to the replication log?

I've been reading about postgresql replication and was wondering if it was somehow possible to listen to the replication binary log and push that to some tool like sumologic or splunk so that any ...
1
vote
1answer
22 views

Select rows within group before condition is met

I want to select rows within each group following a condition in time. I have the following table trajectory_data in PostgreSQL 10 with daily points for each group. | traj_id | timestamp | height |...
0
votes
0answers
29 views

Postgres Audit Table

Is it good practice to use a table in Postgres as an audit log for login events? The database has other tables like users, posts etc. If the backend receives a failed login, it would insert into the ...