Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange
Join us in building a kind, collaborative learning community via our updated Code of Conduct.

All versions of PostgreSQL. Add an additional version-specific tag like postgresql-11 if that context is important.

0
votes
0answers
26 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
20 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
24 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
37 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
37 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
25 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
339 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
39 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
49 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
500 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 ...