All versions of PostgreSQL. Add an additional version-specific tag like postgresql-11 if that context is important.
0
votes
0answers
23 views
PostgreSQL: cross-database views/queries
Using PostgreSQL 10 I am trying to create a view in the database that would pull information from a different database on the same server.
Having such setup as an example:
database: clients
table: ...
2
votes
1answer
19 views
Finding and storing groups of rows PK
My problem is for Postgresql 10 but it probably is also relevant for other DB systems.
I have several tables in which I need to identify groups based on several criteria (some of them are PostGIS-...
1
vote
0answers
14 views
Postgres logging_collector probably blocks auto_explain output
Postgres 10.4
Postgres 9.6
Problem on both versions
I want to collect postgresql slow queries plan, so I added to my postgresql.conf setting from documentation
session_preload_libraries = '...
0
votes
1answer
23 views
CROSS JOIN with table that has no row
In definition;
SELECT *
from T1, T2
Cross Join: If T1 has N rows, T2 has M rows, the result set will have N x M rows.
If T2 has no row, then result of the JOIN has no row also.
However, I want ...
0
votes
0answers
33 views
How to speed up a within distance spatial query in Postgis?
I have the following query:
CREATE TABLE IF NOT EXISTS foobar AS
SELECT
A.field1,
A.field2,
A.field3,
A.field4,
B.fieldA,
B.fieldB,
B....
2
votes
1answer
67 views
error when creating a view with CTE
When running the CTE below, it fetchs me the data I want.
WITH d as
( SELECT *
FROM report.get_sa001('2013-01-01'::date, TO_CHAR(NOW(), 'YYYY-MM-DD')::date, 32) ),
a as
( SELECT *
...
1
vote
1answer
28 views
Convert Date format into bigint format in postgresql
How can I convert date format into bigint format in postgresql?
for eg: '2018-08-20' to '20180820'
0
votes
0answers
39 views
Queries are super slow, a delete on an empty table takes 12 minutes [on hold]
I am trying to figure out why my queries are becoming super slow. I tried a delete on an empty table, and it took 12 minutes:
I am not a DB expert, so I would greatly appreciate some guidance as to ...
-1
votes
0answers
13 views
How to insert bulk data from one table to another if not exist in postgresql
I have to insert bulk of data from one table to another only if not exists I have written following query in postgres
INSERT INTO deposit.dormant_dep_accounts(dormant_date,account_number_id, ...
0
votes
1answer
31 views
Is it a good idea to index timestamp in postgresql?
Here is my table definition
-- Table: public.my_table
-- DROP TABLE public.my_table;
CREATE TABLE public.my_table
(
my_row_id text COLLATE pg_catalog."default" NOT NULL,
my_item_id text ...
1
vote
1answer
36 views
Why does pg_hba.conf sometimes have random rules added to it? (postgresql)
I have postgres 10.4 set up on a Linux machine, and sometimes there will randomly be rules at the top of pg_hba.conf that only allows an MD5 connection by a user "pgdbadm". I'm not sure if the ...
2
votes
1answer
44 views
Directly go to Hadoop or use SQLite/Postgres as a stepping stone towards Hadoop/Spark? [on hold]
In our organization, some people are working on setting up Hadoop with a lot of security restrictions etc. By the rate of progress, this seems complex, especially considering security restrictions, ...
0
votes
1answer
20 views
How to create trigram by joining two columns
I am new to sql so this might be overly simple question that I am confused about. I am trying to create trigram index by joining three columns (images, tags, images_tags). I used string concatenation ...
1
vote
1answer
24 views
recover postgresql database after removal with --purge
I noticed, after a while, that when I got new versions of postgresql (I'm using debian testing) they were being installed while keeping older versions of the DBMS running in parallel (one listening on ...
1
vote
1answer
16 views
Migrating PostgreSQL DB from AWS to Alibaba Cloud
I'm migrating PostgreSQL RDS instance from AWS located in Frankfurt to the RDS instance in Alibaba Cloud also located in Frankfurt.
I use Data Transmission tool from Alibaba Cloud. It connects both DB ...
5
votes
4answers
336 views
How to most efficiently find out if a record has child records?
I am writing a query that returns a single record from the parent table. I would like to also return in this query if it has any children. This is a one to many relationship.
parent:
-parent_id
-...
1
vote
3answers
38 views
Should a privileged port (<1024) be used to deploy Postgresql in production?
I've never found a recommendation to run Postgresql on a privileged port in production. What type of port should be used in production regarding security and best practices?
0
votes
0answers
14 views
How use select query for 2 different tables based on the data [migrated]
array.map(function(t){
var table_name = t.user_id == null/undefined ? "university" : "users";
var table_field = v.user_id == null/undefined ? "university_id" : "user_id ";
...
1
vote
0answers
30 views
AWS RDS postrgres massive disk use, small tables
I can't figure out why our AWS postgres server has consumed all of it's space. We just had to up the storage space allocated to it, but can't find any hint from postgres that it's using that much ...
3
votes
1answer
25 views
PostgreSQL GiST compress skeleton when no compression required
I'm trying to implement my GiST index, where storage type is the same as column type (bytea). They are even going to have same length because these are bitarrays and unions are just disjunctions of ...
-3
votes
1answer
48 views
What is a “clob” or Character Large Object?
I see in this post on listagg — Rows to Delimited Strings talking about SQL's LISTAGG() clause ON OVERFLOW
The return type of listagg is either varchar or clob with an implementation defined length ...
2
votes
1answer
21 views
Handling variable fields in a CSV file with JSON?
Im trying to figure out the engineering to get a CSV file into PostgreSQL.
Requirements:
The CSV has X number of predetermined required fields
The CSV file has Y number of dynamic fields
Example:
...
0
votes
0answers
66 views
PostgreSQL and GDPR compliance [on hold]
I recently read this article https://eng.uber.com/mysql-migration/ which explained that PostgreSQL does not actually delete tuples (rows) when a change is made, but simply keeps it and adds canonical ...
0
votes
1answer
29 views
Trigram index on all values of a JSONB column
I have a JSONB column that contains a bunch of key/value pairs, no complicated nested structures or arrays in there. The keys are dynamic, and I don't know them in advance. I'm using PostgreSQL 9.6.
...
0
votes
0answers
15 views
PG10: Not able to take backup
I have only one database PG10 is installed via EDB GUI installer on Ubuntu 16.04.3 LTS, Now I'm trying to take backup of my database and it is giving me an error, which I can't understand.
Command:
...
0
votes
0answers
20 views
EDB Replication Server - Replicate from ppas 9.3 to ppas 9.6
I am new to EDB replication server and I was thinking if I can use this tool as upgrade tool - to upgrade from postgres plus advanced server 9.3 to postgres plus advanced server 9.6 - is this a bad ...
1
vote
1answer
35 views
Aggregate a JSON doing an average on values without defining keys
I would like to know if there is a way or an existing function to perform an aggregation on a JSON field by doing the average of values if the key appears multiple times with PostgreSQL. The json's ...
2
votes
1answer
47 views
How to narrow down records in SQL based on conditions
Customer Rank Joining_date salary
A 2 2017-10-12 500
A 1 2017-10-10 800
A 1 2017-10-20 400
B ...
2
votes
1answer
37 views
Information about youngest child with inner subquery
Consider a table like this :
CREATE TABLE people (
id INTEGER NOT NULL PRIMARY KEY,
motherId INTEGER REFERENCES people(id),
fatherId INTEGER REFERENCES people(id),
name VARCHAR(30) NOT NULL,
...
-1
votes
1answer
24 views
Converting unix timestamp to an sql timestamp in postgres
I have passed Date.now() and other plain unix timestamps like 1534360109480 into sql and ran them through this.
"@converted_date" = to_timestamp("@date") AT TIME ZONE 'UTC';
And it consistently ...
3
votes
1answer
47 views
What does @ signify in a query expression?
What does the @ symbol signify in this sql expression?
update some_table set some_number=(@some_number+1) where some_id=1;
It seems to return the same result as:
update some_table set some_number=...
0
votes
1answer
29 views
How to swap array values in PostgreSQL.?
How to swap array values in PostgreSQL. For example i have a table that has a column whose data type is bigint[]. It has following value in it.
value: {111, 222, 333}
My desired output is {333, 111, ...
0
votes
0answers
32 views
Calculating monthly data as it is inserted [on hold]
I'm very new to databases and database design, so apologies if this is a silly question or has already been asked - I was unable to find any answers to this via Google, though.
I am currently ...
1
vote
0answers
35 views
Sharding PostgreSQL with postgres_fdw (Foreign Data Wrapper)
We want to shard a single PostgreSQL 10.2 database by tenant (client id) to multiple servers. One of the easiest approach is to use Foreign Data Wrapper (postgres_fdw extension).
It will looks like:
...
1
vote
3answers
42 views
Show rows with non-unique value in one field
I'm familiar with how to aggregate rows as shown in this answer:
Show only duplicate values
I'm also familiar with how to filter the aggregated results using a HAVING clause.
What I can't seem to ...
0
votes
0answers
22 views
Do any of the PostgreSQL releases for Windows support automatic updates?
PostgreSQL has two distributions for Windows,
EnterpriseDB
BigSQL
Do either of these Windows distributions support automatic updates for the term of support? Something that looks for the updates ...
1
vote
1answer
45 views
How do I an add a DEFAULT on a column if the table already exists?
I want to add a DEFAULT for a timestamp column in PostgreSQL. With SQL Server you use ALTER TABLE ADD CONSTRAINT
You can see the example in their docs,
ALTER TABLE dbo.doc_exz
ADD CONSTRAINT ...
1
vote
0answers
37 views
Postgresql 10 arrays equality failure
I am using AWS RDS postgres instance version 10.3.
Completly stuck with the problem.
I have a table like:
create table social_message(
social_profile_id int8 not null,
body text,
subject ...
6
votes
1answer
499 views
Left join without duplicate rows
I have two tables called record and record_history. For each record, there could be more than one history. They can be joined by id and record_id. I want to get all the record's entries with recent ...
1
vote
1answer
21 views
How to clean every non-digit except the + at beginning of string with regexp_replace in Postgresql?
Related to a comment, I would like to find a way to clean with regexp_replace in PostgreSQL any non-digit except the + at beginning of the string.
For example +(7)9125415501 shall become +79125415501
0
votes
1answer
34 views
How to list all constraints of a table in PostgreSQL?
How to list all constraints (Primary key, check, unique mutual exclusive, ..) of a table in PostgreSQL?
3
votes
2answers
48 views
Creating same unique constraint with different names
In MySQL 5.7
alter table mydb.roles add constraint uk1 unique (role_name);
alter table mydb.roles add constraint uk2 unique (role_name);
The 2nd alter command threw me a warning stating:
...
0
votes
1answer
23 views
Make to_tsquery return OR of lexemes instead of AND
I'm using an dict_xsyn extension in postgres which returns multiple lexemes.
e.g.
CREATE EXTENSION dict_xsyn;
CREATE TEXT SEARCH DICTIONARY names_xsyn (TEMPLATE = 'xsyn_template');
CREATE SEARCH ...
0
votes
0answers
20 views
How to write complex expressions while creating a row-level security policy in Postgresql
I've got a table structure where A has records with an owner ID, B links to A, and C links to B. I want to restrict access to rows in C by the ownerId, which is set as a setting on the session in the ...
1
vote
1answer
41 views
Mysterious postgres process pegging CPU at 100%; no running queries
I have a recurring issue where a postgres process is pegging my cpu at > 100%. It's a strange one, you can see below the command is ./Ac2p20853 (surrounded by asterisks, added by me).
Running SELECT ...
0
votes
1answer
18 views
How to configure PG text search dictionary through sql
I want to add terms to a text search dictionary without access to the file system shares/tsearch_data (specifically for google cloud SQL).
Is there a syntax to do this via SQL?
All I have found is "...
2
votes
1answer
45 views
PostgreSQL Geo Spatial Query is slow
After given up with MySQL i have tried Elasticsearch and now wan't to see if I could go with PostgreSQL/PostGIS that could allow me to go with PostgreSQL only.
I need to fetch records from a Table ...
0
votes
0answers
15 views
PostgreSQL database timeouts with high timeout value, but works fine with lower value
Our website uses pg_connect while connecting to a database.
After doing some tweaking we increased the value connect_timeout from 3 to 7 seconds.
We started seeing timeout expired exceptions. Then we ...
2
votes
1answer
30 views
PostgreSQL insert record by trigger [on hold]
create table tab1(id int,name text);
create table tab2(id int,name text);
create or replace function t_tab() returns trigger
as
$$
begin
insert into tab2
values (new.id, new.name)
return new;
...
1
vote
1answer
37 views
Bulk percentile calculation in Postgres
I'm trying to find the best way to calculate percentiles for a dataset in bulk on Postgres, returning both the percentile and the value.
generate_series() plus percentile_cont() and lateral join ...
