Tagged Questions
All versions of PostgreSQL. Add an additional version-specific tag like postgresql-11 if that context is important.
2
votes
0answers
17 views
Postgresql UDF (User Defined Functions) overhead
Disclaimer
The task may seem esotheric, but nevertheless I want to create a POC of some sorts.
The goal
My goal is to make PostgreSQL database (version 10) expose an API to an application that uses ...
-1
votes
1answer
13 views
structure of query does not match function result type
I want to create a basic function which takes an input and throws a table with multiple columns and rows as an output . I have written the following query for the same :
create or replace function ...
0
votes
0answers
19 views
Count and Sum columns with sub queries or union
Hi everyone I have 2 tables being:
odk_blackead.bh_ohs_comp_core a (Primary Key is = a."_URI"::text )
odk_blackead.bh_ohs_comps_rpt_audit c (Foregin Key is = c."_PARENT_AURI"::text)
Table 1 ...
1
vote
0answers
14 views
change column type from integer to varchar
I have an integer column size in my table product in PostgreSQL 10.5 database. I want to change it's type from int to varchar(20) using alter table if possible.
The obvious way which worked for me ...
0
votes
0answers
15 views
Postgres — Calculating average distance between two points (long/lat)
I am new to Postgres and really SQL in general, so please bear with me. For a project of mine, I'm trying to calculate the average distance between two points, given their longitude and latitude
I ...
-1
votes
0answers
4k views
PostgreSQL vs MySQL Configuration file Comparison For Performance
How I can make my PostgreSQL 10 fast like my MySQL,
My Hardware:
Processor 12 Core
RAM 42GB (20GB Ram Already used for MySQL in same machine)
HDD 1TB
Usage:
SELECT-ONLY
Searching - LIKE%
Column &...
0
votes
0answers
21 views
Find Popular/Common States
State Table
CREATE TABLE state (
id serial primary key,
name varchar(255) not null
);
id | name
--------------------
1 | NC
2 | OH
3 | MA
4 | NY
5 | Connecticut
6 | New York
...
0
votes
0answers
6 views
PostgreSQL using SymmetricDS with tokens in the initial_load_select field
I'm creating a new server test group using Postgres 9.6 & 10.3 with SymmetricDS 3.9.13. I've got a successful installation in production that syncs one database server with approx. 10 slaves. This ...
0
votes
0answers
18 views
Store or log actual dynamic SQL EXECUTE … format() … USING
exec_str := format('UPDATE table SET id = $1
WHERE val = $2
AND json#>>''{' || '%I' || '}'' like $3
AND id is null', _element);
...
-2
votes
1answer
44 views
How do a have a column filled by a default value? [on hold]
I would like to fill a column with a default value as soon as i finish edit other columns using SQL code in PostgreSQL 9.5/ PostGIS.
The value of this column is "01LU" the column name is "...
1
vote
1answer
50 views
Why full text search on table with GIN index is still very slow
With what I've been able to gather thus far, if you need to run a full text search on a table in a PostgreSQL (psql 9.6.2, server 9.6.5) database with lots of entries (say on the order 1.2M+), the ...
0
votes
0answers
33 views
Comparing rows in two tables without foreign key
I am building a smart locker reservation system with the following Postgres tables:
CREATE TABLE lockers (
id serial primary key
)
CREATE TABLE doors (
id serial primary key,
locker_id integer ...
-2
votes
1answer
27 views
PostgreSQL count distinct per 6 months grouped by month [on hold]
Using PostgreSQL, I need to count how many distinct clients are active each month where active means they’ve posted a job within a 6 month period... and display that by month. So for example, 2018-10 ...
1
vote
2answers
35 views
How correct Postgres plan in case of a double JOIN, ORDER BY and a LIMIT?
I got a SQL request that takes more than 4s:
SELECT "Content"."contentId"
FROM "UserFeed"
JOIN "FeedRole" ON "FeedRole"."feedId" = "UserFeed"."feedId"
JOIN "Permission" ON "Permission"."...
0
votes
1answer
22 views
Get the average of the last X records for each foreign key
My table is a list of yacht handicaps for yacht racing. After each race a new handicap is calculated based on how the yacht performed which is added to the table. The actual handicap used to modify ...
0
votes
0answers
57 views
Very slow nested SQL query
I seem to have hit some bottlenecks around nested SQL queries.
I have three tables in PostgreSQL with the following:
Table-1: has 2443 unique rows
Table-2: has 3414569 unique rows
Table-3: has ...
1
vote
1answer
22 views
Backfill new column with values based on foreign key
I'm new to RDS and am looking for help in updating a new column with ascending values based on a FK.
Can I and should I be doing this as a sequence of SQL commands that calculates my business logic ...
0
votes
1answer
36 views
Optimizing SQL query with two table joins and over 40 million rows in partitioned table
I have three tables, lets say their names are:
table1 (has total over 40 million rows, but is partitioned with Declarative partition https://www.postgresql.org/docs/10/static/ddl-partitioning.html ...
0
votes
1answer
24 views
Realtime Update MySQL on every Update occurred on PostgreSQL
I have a PostgreSQL for my accounting system and a MySQL database for my php website, i want to sync these two database for every update may occurred on PostgreSQL it also updates MySQL database, they ...
1
vote
0answers
20 views
+50
Is it better to store one tsvector column for all fields or one tsvector column per field?
Consider a simple movies table like this:
CREATE TABLE movies(
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
title character varying(255),
categories character varying(255),
...
0
votes
0answers
12 views
(Valentina Studio) FATAL: no pg_hba.conf entry for host “IP”, [on hold]
DB Tunnel
sshpass -p [PASSWORD] ssh [REMOTE_CREDENTIAL]@<IP> -L [LOCAL_IP_DB]:[LOCAL_DB_PORT]:[REMOTE_IP_DB]:[REMOTE_PORT_DB]
[LOCAL_IP_DB] = 127.0.0.1
[LOCAL_DB_PORT] = 15432
[REMOTE_IP_DB] = ...
0
votes
0answers
31 views
How do you model a recurring order in a shop?
I am making a B2B-shop system for a customer where their customers can order items from their shop.
The system needs to handle placement of recurring orders like: "Every tuesday of the week send me 4 ...
0
votes
1answer
26 views
Populate a column using the values of other ones
I would like to add a new column in my table using SQL code in PostgreSQL / PostGIS, the values in this column must be filled from the first eight columns and updated with a trigger as I insert data ...
1
vote
1answer
46 views
Why is my query taking so long if my field is correctly indexed?
I’m trying to figure out why my query is taking so long. I have indexed my property_id field but still get awful results when querying it. I have the following Explain and wonder if anyone can shed ...
0
votes
2answers
19 views
AWS RDS PostgreSQL - Monitoring & Analytics [on hold]
Need a recommendation for a 3rd-party monitoring tools that would work with AWS RDS Aurora PostgreSQL databases. While Amazon CloudWatch does a good job monitoring AWS instance resources, I was ...
0
votes
1answer
18 views
Installation of PostgreSQL-11: Depends: libllvm6.0 (>= 1:6.0~svn298832-1~) but it is not installable
I've added the official repository to my sources.list,
deb http://apt.postgresql.org/pub/repos/apt/ cosmic-pgdg main 11
When I try to install PostgreSQL-11 on Ubuntu Cosmic (18.10), I get the ...
1
vote
0answers
16 views
ERROR: invalid input syntax for type oid: “english”
While creating an index in Postgres I am getting an error:
invalid input syntax for type oid: "english"
LINE 1: ...s ON entity_attributes2 USING gist ( (to_tsvector('english',...
The query ...
1
vote
1answer
22 views
postgres pg_xlog explodes during set tablespace
I'm running postgreSQL 9.6.0 on CentOS 7.2. Occasionally I need to shift data on to a larger tablespace to prevent /var from filling up. When this is running the pg_xlog directory grows rapidly, and ...
0
votes
1answer
22 views
Column to be populated using other columns values
I would like to add a new column in my table using SQL code in PostgreSQL / PostGIS, the values in this column must be filled from the other eight columns and updated with a trigger as I insert data ...
0
votes
0answers
15 views
Logging bad rows importing input CSV files in Postgres with Pgloader
I'm creating a pgloader command file to import a set of CSV files supplied by another team in a table in a Postgres database. Loading well formed files woks fine, but our input files might be badly ...
-1
votes
0answers
43 views
Should have primary key when rows are read in groups, and deleted individually, in a huge table [closed]
I have a table whose candidate key comprises 5 columns: 3 of bigint/integer type and two of varchar(255) type. The table will typically have a huge number of rows (millions).
The rows are read in ...
-1
votes
0answers
19 views
Server sizing for Database [closed]
I have been looking for an online calculator that will provide me the hardware needed if i need X-amount of transactions/sec for my database server. Is there such an online calculator? If so, can you ...
1
vote
0answers
27 views
Postgres 10 replica service error on startup
I have migrated a database promoting the read-replica in a server with more capacity, this process worked smoothly. But I'm trying to create a read-replica now from this promoted server to have a ...
0
votes
1answer
25 views
Which Index for WHERE UPPER(“my_col”::text) LIKE “FOO” search on bytea
I use the unusual bytea column type in a PostgreSQL DB because the data in this column is like a blob. I mostly contains text, but sometimes it contains binary data.
Which index would fit if the ...
1
vote
1answer
14 views
Postgres ignoring index on join, but all planner estimates are correct?
I've got a query that looks like this:
SELECT *
FROM (
SELECT *
FROM (VALUES ('abc123')) AS user_ids(user_id)
) AS user_ids(user_id)
LEFT JOIN users ON users.id = user_id
But instead of ...
2
votes
1answer
38 views
Performance of count() vs count() in a window function
I'm having a hard time trying to figure the performance gap between a raw count() query vs a count() OVER() window function.
Here is the test setup. Let's create a dummy table:
CREATE TABLE main (id ...
1
vote
1answer
21 views
PostgreSQL: Slow JOIN between multiple tables.
Context:
I am working on a data warehouse that needs to have responsive look ups as it is used as the source for an operator console. The target is to achieve sub 1 Second lookups (ideal would be ...
0
votes
1answer
28 views
How are Postgres database files updated?
I'm curious how the /base files are updated for a Postgres database. Do the segments correspond to segments of each table, so that only a few segment files are updated after an update to subset of the ...
0
votes
1answer
16 views
Recover ancient versions of postgresql
I was running different postgresql versions. And accidentally they were removed. they contained several databases and I want to recover them. I'm wondering if there'a a command line in linux that ...
0
votes
0answers
25 views
Dynamically select target columns for insert
I'm guessing the answer in no, but in Postgres can you somehow use EXISTS or similar to dynamically choose the right set of columns for an insert as below:
insert into table_1 (x, y, z)
(
select ...
1
vote
1answer
39 views
Should I stop my PostgreSQL server before reboot?
Every week I do an apt-get upgrade and dist-upgrade on the server where my PostgreSQL is running. After that, I do a sudo reboot now.
Should I stop the PostgreSQL service before this reboot?
-1
votes
1answer
21 views
LIKE search in bytea datatype (PostgreSQL)
Table:
\d blob
Column | Type | Collation | Default
---------+--------------+-----------+------------------------------------------
id | integer ...
0
votes
1answer
26 views
Which grants are needed to access a table?
I want to know which grants at the very least are required to select/insert/update/delete from a table.
Say I have a table t in schema s in database d and a role r.
I start with granting the ...
1
vote
1answer
162 views
Remove duplicate comparisons in postgres?
I have a polygon layer named scale_polygons_v4. I want to get ids of overlapping polygons. I have tried the code below:
select distinct s1.id,s2.id from scale_polygons_v4 s1
inner join ...
0
votes
1answer
20 views
Postgres FOREACH array as composite type
I'm trying to loop over an array with two types as a composite type, but it seems that I can not use the composite type as the variable array to loop over it. What I'm trying to accomplish:
create ...
-2
votes
0answers
25 views
Syntax Error in PostgresSQL function definition [closed]
The following is the body of a Postgres function I'm trying to make
-- logic to convert 53N 1E 8 to 4530108 and 53 N1 W8 to 2530108
DECLARE
dir integer;
"twn" ALIAS FOR $1;
"range"...
6
votes
2answers
88 views
Postgres Transaction OOM on 100k DDL statements
We execute approximately 100k DDL statements in a single transaction in PostgreSQL. During execution, the respective Postgres connection gradually increases on its memory usage and once it can't ...
1
vote
2answers
55 views
Create 2D truth table from “cross product” of two tables via PostgreSQL view or function
I have a working Excel-based method for creating a truth table from two vectors exported from a PostgreSQL database. The process takes about 4 hours to complete due to a large number of VLOOKUP and ...
0
votes
1answer
40 views
one to many design struggle [closed]
I have two tables :
CREATE TABLE fiber (
fib_id uuid primary key default gen_random_uuid(),
fib_number not null,
fib_object uuid not null reference cable, --useless here but imagine a cable
...
2
votes
1answer
32 views
How to “insert into select from” skipping exceptions
1st post on SE/DBA; i am actually more of a DB developer but did not find a better suited forum, let me know if I picked the wrong one. I am new to PG but know Oracle.
I will need to report on JSON ...
