Questions tagged [postgresql]
All versions of PostgreSQL. Add an additional version-specific tag like postgresql-11 if that context is important.
10,975
questions
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 ...
