Questions tagged [postgresql]
All versions of PostgreSQL. Add an additional version-specific tag like postgresql-11 if that context is important.
11,940
questions
1
vote
2answers
526 views
Logical Replication to different schema name
We have PostgreSQL 11 database with structure one database one schema (public).
dbname : production_one
schema : public
dbname : production_two
schema : public
I would like to set up a logical ...
0
votes
1answer
11 views
to_char(resolved_date-opened_date, 'hh24:mi')
I am using the below to return the hh:mm difference between two date ranges. How would I update this code to minus the hours by 5?
CURRENT CODE = to_char(resolved_datetime-opened_datetime, 'hh24:mi')
...
2
votes
3answers
278 views
Execution Performance Issue with Similar Query
I have been having some performance issues running a simple query on PostgreSQL on a table that has about 3 million rows with a join on a table that has about 120 rows.
If I run a query only on the ...
0
votes
0answers
32 views
Postgresql Cluster gets shut down signal
I hope you can help me with my current Postgresql Cluster Problem. I deployd the Cluster regarding this instruction.
-> https://github.com/vitabaks/postgresql_cluster
My current problem is that ...
2
votes
1answer
196 views
PostgreSQL: simple equality query returning no results for indexed columns
I have a very large table (around 140M rows) with five columns:
CREATE TABLE IF NOT EXISTS citations
(
citing_id text,
citing_issn text,
cited_id text,
cited_issn text,
year int
);
...
1
vote
1answer
16 views
Delete duplicate records and update the mapping table
I have store_table_map and item_price_map. I want to delete duplicate records in store_table_map and retain the latest row based on updated_date because i want to put constraints in store_id and ...
0
votes
1answer
27 views
Is it possible to block the access of a super user?
In a scenario that the databases run on the client's server.
Company "A" has a database installed on that server. The customer hires the services of a company "B", which will also ...
1
vote
1answer
145 views
To find data in Multiple tables and delete it?
I am using a PostgreSQL server. Suppose I have 200 values in a master table that need to be deleted. There are 9 other tables referencing this master table. The other 9 tables not only reference the ...
32
votes
3answers
31k views
For absolute performance, is SUM faster or COUNT?
This relates to counting the number of records that match a certain condition, e.g. invoice amount > $100.
I tend to prefer
COUNT(CASE WHEN invoice_amount > 100 THEN 1 END)
However, this is ...
0
votes
0answers
3 views
Postgresql Input And Output [migrated]
import psycopg2
try:
connection = psycopg2.connect(user = "postgres",
password = "formal",
host = "127....
1
vote
1answer
25 views
Dilemma with Partition Key choice
One of our tables is over 200gb and the old records are hardly ever queried, and this makes me think partitioning is a good solution, however I am having trouble figuring out which column to use as a ...
2
votes
1answer
222 views
+100
How to create read-only user for schemas which can be created in the future?
SO and Internet contains a lot of answers to question how to create read only user (for example see this article), BUT all of them promote the following way:
Create user
Grant USAGE privilege to all ...
0
votes
1answer
16 views
replace string in field
I have these entries in a field in one table under a DB running Postgres 9.4:
copy schema_name.table_name to '/path/path2/path3/path4/reports/export"
and would like to replace ONLY the word &...
2
votes
2answers
1k views
Parent Child Relation in same Table . Select Query to get only parent records and Records not comes under parent child relation
I have a table with Parent-Child Relation in the same Table. Some Sample Data
id │ name │ parent │ is_active
════╪══════════════════════╪════════╪═══════════
1 │ Company A ...
0
votes
0answers
15 views
Query to find hierarchical relationship between objects [duplicate]
I want to execute a SQL query in PostgreSQL 10.6. I have two tables, A and B, that contain a common field, which contains a code. Each code has an inherited code and I want to build a hierarchical ...
0
votes
1answer
23 views
Unable to create partial indexes using bloom
I'm new to bloom indexes and currently my table has 3 partial indexes for the same column in the WHERE clause and with different column combinations for the same table. I was trying to replace them ...
1
vote
2answers
3k views
pg_dump permission denied for sequence
I have a strange issue with dumping post-data section.
Dump command is:
pg_dump -Fc --verbose --section=post-data --no-owner --no-acl -h localhost -p 9999 -U root db_name -W > constraints.dump
...
-3
votes
0answers
16 views
connect postgis with google cloud [closed]
I am working on windows I have a database PostGIS in Django. I want to connect my pgAdmin google cloud VM instances but I get the error
Unable to connect to server:
timeout expired
I use the external ...
0
votes
0answers
34 views
Syntax error near & and : in psql
I am getting below errors in sql log of Native Postgres database :
psql.bin:G_co-ac_G_grant_ivspc.sql:1: ERROR: 42601: syntax error at or near "&"
2 LINE 1: grant select on ...
0
votes
1answer
411 views
How to mark a table as pre-populated with postgresql logical replication
I accidentally loaded data into a table before setting up a logical replication subscription for it and now I'm getting errors when I try to sync it (duplicate key already exists).
I can't delete ...
0
votes
2answers
3k views
Postgresql - Large object exists in pg_largeobject table but cannot open with lo_open?
I have a problem with opening Large Object in postgresql version 9.2,
select * from pg_largeobject where loid = '19423';
will return data like this:
loid oid;page no integer;data bytea
19423;0;"<...
0
votes
3answers
51 views
Why is the database size larger than sum of table sizes
I wanted to know why database size is a way greater than thesum of table size in a database? How does it change?
0
votes
0answers
21 views
Space concerns for unique constraint on row for time-series db (PostgreSQL / TimescaleDB)
I was wondering if there is a more space-efficient way of doing unique row constraints on time-series DBs. Adding a normal unique key for the entire row will result in an index the same size or ...
1
vote
3answers
2k views
Postgres 10 Query waits on IO: BufFileWrite causing failure to get new database connections
I have a query (attached with query plan) that is run every 5-7 seconds from every device that is running our dashboard (typically, 500+ devices at peak time). This query, at the outset looks like it ...
0
votes
1answer
14 views
How to replace to_tsquery by phraseto_tsquery in PostgreSQL?
For word suggestion when do search.
I can query like
select title from test where ts_filter(tsv, '{A}') @@ to_tsquery('english_nostop', 'god<->o' || ':*')
# tsv is something like to_tsvector('...
2
votes
1answer
189 views
How to deal with seldom used big database and postgresql?
I loaded in PostgreSQL (9.3) OpenStreetMap data for whole europe (400gb). Then I installed a geocoding api (nominatim) that queries the database with gis queries.
My problem is :
This database is ...
1
vote
1answer
28 views
Google Managed PGSQL DB migration to DigitalOcean Managed PGSQL DB
We are migrating from Google-Cloud PGSQL managed database to a managed PGSQL server on Digital ocean.
The dilemma we are facing is that both Google and Digital ocean are managed so we have no access ...
4
votes
1answer
381 views
PostgreSQL is not using index with large data
Enviroment
My PostgreSQL (9.2) schema looks like this:
CREATE TABLE first
(
id_first bigint NOT NULL,
first_date timestamp without time zone NOT NULL,
CONSTRAINT first_pkey PRIMARY KEY (...
0
votes
1answer
13 views
Return average using same table data
I've got this query I'm testing with:
select sum(units) / (select count(distinct week) from record_sales_data where customer_country_id=2 group by customer_country_id) as average
from ...
0
votes
0answers
22 views
Postgresql: order by slows down query [closed]
I have seen lots of queries in postgresql where "ORDER BY" affects the query badly.
Removing order by on some of my queries yields marginal performance improvements.
For example:
explain (...
0
votes
1answer
16 views
return field name and number of rows which meet condition in one sentence
I have a table in postgres with 10 fields, and need to check for certain values in 3 of them, then return the field name which met the condition and the number of rows in each field.
So it's something ...
-1
votes
1answer
31 views
How can I join two queries to get a single output? [closed]
First query
select employee_name_en, count(*)
from fl_file fl
inner join fl_file_movement movement
on movement.file_number_fk = fl.file_id
inner join vw_employee_details_with_department vw
...
0
votes
1answer
13 views
The 24 is the storage overhead per row in a table is fixed for all tables ot it varies?
I wanted to know whether the storage overhead per row in atable is fixed for all tables created irrespective of given datatypes or it varies from one table to another?
0
votes
1answer
84 views
PostgreSQL cluster has two primary databases after network glitch
My test environment had network glitch over the weekend and I ended up having two Primary databases up and running in the cluster.
ID | Name | Role | Status | ...
0
votes
0answers
14 views
How to stop or delete repmgr service?
I installed repmgr10 on PostgreSQL server before. But now will reinstall them.
Deleted postgresql's data folder: /var/lib/pgsql/10/data/.
Stoped postgresql-10 service: sudo systemctl stop postgresql-...
1
vote
2answers
29 views
Sum all unique values from previous dates
Let's say, for simplicity sake, I have the following table:
id amount p_id date
------------------------------------------------
1 5 1 2020-01-01T01:00:00
2 10 ...
0
votes
1answer
158 views
pg_repack not organizing table by column
I'm using pg_repack to order one table by a timestamp column, however I'm finding that after running pg_repack command it's not ordered at all.
I'm using this command
pg_repack --jobs 4 -t ...
2
votes
1answer
38 views
Caveats of OUTER JOIN on nested JSON value
I'm writing a query which is supposed to find the elements from a list which DO NOT exist in the DB. My first attempt at this was to use a nested query where the first query fetches the ids, then I ...
2
votes
0answers
36 views
Can a FOREIGN KEY be referring to the same table as the “source”?
I was sure that I had done this countless times, but I can't find any previous use of me doing this. But it seems to make perfect sense, so I assume that it must be possible? However, the manual says:
...
0
votes
1answer
25 views
Unable to create view with temp table
Hi I am working on one project and want to create a view. I am using temp table in my query. can any one let me know how we can create such view ?
Following is my query, I tried using WITH..AS , but ...
3
votes
4answers
3k views
Set non-negative floor for rolling sum, in PostgreSQL
This is a really fun question (asked for SQL Server) and I wanted to try it out to see how it was done in PostgreSQL. Let's see if anyone else can do it better. Taking this data,
CREATE TABLE foo
AS
...
2
votes
1answer
21 views
Postgres table size is not growing after adding row
I couldn't see any growth in table size when I added a row in the relevant table. I see the same memory space even after adding 5 rows. Could anyone please help me and explain what I'm doing wrong?
1
vote
0answers
18 views
Should I use Postgres' JSON columns for additional related data of different types and not yet known?
I have a simple database table, lets call it tbl_main with a few columns and relations to other tables via foreign keys.
The tbl_main is the most frequently written to table, as in it receives new ...
0
votes
1answer
185 views
Create multiple output tables from one query
I'm an inexperienced Postgres user and need help if someone can suggest it.
I require output tables of column 'county' so all counties in the table have their own table. I need independent tables for ...
6
votes
2answers
5k views
Postgresql json column to view
I have a table that looks like this
id | json_column
---+------------
1 | {"text1":"a", "text2":"b", "text3":"c", ....}
2 | {"text1":"b", "text2":"c", ....}
and I want it to be like this
text1 |...
0
votes
1answer
86 views
Poor Query performance in postgresql 9.3
I have a simple query which is taking about 2 minutes to complete.
select distinct
a.statename,
a.districtname,
count(b.rel_globalid) as samplescollected
from fulladmin a
...
0
votes
0answers
12 views
Why got pending status when use createuser command with PostgreSQL?
Using PostgreSQL 10 on RHEL 7.7.
Start service and login to postgres user:
$ sudo su - postgres
-bash-4.2$
Create a user
-bash-4.2$ createuser -s repmgr
It will pending and wait for a long time can'...
24
votes
6answers
63k views
PostgreSQL: How to backup only One Schema from a database and restore it on another server
I have a database named "A" which has two schemas "B" and "C".
I want to backup and restore Schema "B" on a different server? not sure how to do this as I am new to Postgres.
Do I have to create a new ...
0
votes
0answers
26 views
Where to store cash book debit in double entry bookkeeping relational database?
I'm designing a very basic banking system, where Users can Deposit to Bank / Withdraw from Bank / Transfer to other user accounts.
I've been studying double entry bookkeeping and so far my database ...
0
votes
0answers
23 views
Combining old and new data in database
I have upgraded an application to a newer version, and this application runs in a new server. The old application runs on another server. The new application went live yesterday (6/7), and the old one ...
