Questions tagged [postgresql]
All versions of PostgreSQL. Add an additional version-specific tag like postgresql-11 if that context is important.
10,168
questions
0
votes
3answers
25 views
How to prevent trigger from overwriting values that were explicitly set?
I tried this but doesn't work because NEW.updated is never NULL (I suppose NEW gets populated by the values currently in the row in addition to the values in the update-statement?):
create temp table ...
0
votes
1answer
23 views
pg replication connection not returns wal events to java code client , it hangs some times
Describe the issue
We are facing issue of pg replication slot not listen database wal events.
Once pg database restarted and we attach
Driver Version: 42.2.6
Java Version: JDK1.8
OS Version: Window ...
1
vote
1answer
155 views
Alter postgres password from python?
When I run this query from psql shell everything works fine:
ALTER ROLE postgres WITH ENCRYPTED PASSWORD 'somePassword';
but when I try exactly the same query from python (with psycopg2 package)
...
1
vote
1answer
22 views
ALTER TABLE ADD CONSTRAINT failing when executed from function
So I have 2 functions that I am writing, one to drop all foreign constraints for a schema, and the other will recreate these foreign constraints as they once were.
The drop constraints function works ...
1
vote
2answers
6k views
How to access row value from UPDATE sub query function?
I would need to use UPDATE like this:
update offer set (bank, bank_id) = (cost, gid)
from (
select cost, osm_points.gid from pgr_kdijkstraCost(
'SELECT gid as id, source, target, walk_cost as cost ...
0
votes
0answers
9 views
replication & load balancer with postgresql configuration
I configured a master-slave replication server with postgres 9.6 following this doc and all worked fine. When I make changes on master server immediately are replicated to slave server and I cannot ...
0
votes
1answer
16 views
Create a foreign key to a table that doesn't exist in postgres
I'm trying to put together a solution where I have a bunch of script files that contain my schema creation. Something similar to Visual Studio's SQL Project, but for Postgres.
My problem is that the ...
0
votes
1answer
94 views
postgres: using unnest in multiple rows
I have an array column containing information like
{a,b,c,a}
{a,b,a,b}
{b,a,a,b}
I want to run unnest on this array column, get the occurence of of each character.
a 2
b 1
c 1
a 2
b 2
b 2
a 2
I ...
0
votes
0answers
27 views
Sql, get data/rows for more selected ids in one condition, from file
I am using PostgreSQL 11.5 on Ubuntu
Question
How can I get data/rows for more selected ids in one where condition? Ideally from file with list of ids.
Example
|product_id |product_name ...
4
votes
2answers
4k views
How to configure replication from PostgreSQL (RDS) to vanilla PostgreSQL?
I have an Amazon RDS Postgres DB and I created a "replicator" user and granted it the "replication" privilege.
db=> create role replicator login password 'something';
CREATE ROLE
db=> alter ...
0
votes
1answer
29 views
How can I run a pgAgent job every 10 second?
I have a PostgreSQL 11.3 Database. I installed pgAgent for creating jobs. Now I want to run my job every 10 second, but in GUI there is possibility to write with precision of minutes, not with ...
0
votes
0answers
27 views
Postgres uses sequential scan with OR condition, but index scan with AND condition
We are doing a full text search on an object. Unfortunately, the object is normalized across different tables, and the full text search results in a condition with several OR, because the term can ...
1
vote
1answer
24 views
Unable to alter partition table to set logged
I have a partition table that I created UNLOGGED. I'm using pg_partman to manage the partition. I then proceeded to load data into it with a COPY command.
Afterwards, I ran an alter table set logged ...
0
votes
1answer
20 views
Multiple versions of PostGIS and PgRouting on CentOS 7
I'm trying to install multiple versions of PostGis and PgRouting in the same instance of PostgreSQL v9.6, using CentOS 7.5.
I've been fighting with dependencies and shared libraries, to make all ...
0
votes
0answers
25 views
Grant Privileges on Production Database Throws “Error: tuple concurrently updated”
I would like to grant read access to all tables in my schema
GRANT SELECT ON ALL TABLES IN SCHEMA public TO XXX;
While it works on our alpha environment, it fails with our production database:
[...
36
votes
2answers
26k views
Can't rename columns in PostgreSQL views with CREATE OR REPLACE
In PostreSQL 8.3, I'm trying to create a view which will look just like an existing table but have different column names.
This works
CREATE OR REPLACE VIEW gfam.nice_builds AS
SELECT (family_tree....
4
votes
1answer
560 views
What can I do to monitor “out of shared memory” issues?
I'm new to database administration and I would really like some advice on what tools I could use or what I could do to monitor "out of shared memory" issues.
I've been seeing these "out of shared ...
1
vote
1answer
1k views
PostgreSQL database size doesn't match used space on disk
I have a PostgreSQL 9.6 database with high-volume traffic. I run periodically pg_repack reclaim unused space in tables/indexes. On larger tables repack sometimes fails to complete the process which ...
4
votes
2answers
1k views
Count NULL values per row
I would like to count the number of null's present per row in a table without enumerating column names. For example:
WITH t as (VALUES
(NULL ,'hi',2,NULL,'null'),
('' ,'hi',2,3,'test'),
(NULL ,'...
1
vote
3answers
54 views
Take average of multiple nullable columns
I have an example table (year) as below in PostgreSQL 9.5:
Name | 2010 | 2011 | 2012
-------------------------
A | 10 | | 40
B | 10 | 20 | 30
Now, if I write a simple query as ...
2
votes
2answers
30 views
Summing the count of two columns in postgres that contain the same value
I want to sum the total number of occurrences of one value across two columns in the same table.
So, an example would be:
id |node1 |node2
1 | 111 | 123
2 | 122 | 124
3 | 111 | 125
4 ...
0
votes
2answers
42 views
How to hide the partition child tables in PostgreSQL?
I am creating partition tables in postgreSQL. And the partition is on date column so its nearly high in number to show in schema. I want to show only my parent table for other users.
I tried many ...
18
votes
5answers
122k views
Query to return output column names and data types of a query, table or view
Is there a PostgreSQL query or command that returns the field names and field types of a query, table or view?
E.g., a solution if applied to simple SELECT query like SELECT * from person should ...
0
votes
2answers
33 views
When to ANALYZE/VACUUM/REINDEX when adding primary keys and indexes
I have a recurring job that creates a Postgres (v11) database from a template and loads it with data. The scale of the data is on the order of 10s or 100s of millions of rows, so for performance ...
1
vote
2answers
41 views
postgresql - add the missing rows to each group of year/date
i have this table --> https://pasteboard.co/IrVXlRe.png
and there is another table --> https://pasteboard.co/IrVXNPh.png
what i need is to show ALL categories regardless of the quantity. if there ...
16
votes
4answers
25k views
How much time will a vacuum/autovacuum operation take?
I manage a big (some hundreds of gigs) database containing tables with various roles, some of them holding millions of records. Some tables only receive large number of inserts and deletes, some other ...
0
votes
2answers
37 views
How to count decimals without zeros
How can I count all decimal place up to the first zero
For example I have a column named x with an entry of numeric datatype value 1.955000000
I want to count the decimal places without the zeros. ...
0
votes
1answer
18 views
Postresql dynamic index creation for many tables
How can I create an index in Postgres dynamically over many tables but always with different names.
Lets assume table x has a column y with all table names of hundreds tables which need to get an ...
0
votes
1answer
42 views
How to deal with different badges in Postgres Explain Visualizer?
I've been working with several DBMS during my career, but always Oracle/Sql Server/MySql. Now I have to deal with PostgreSQL and my first task is to tune several queries to speed them, as our DB is ...
0
votes
1answer
23 views
Error: pq: 1 columns must reference exactly 1 columns in referenced table (found 2) [closed]
I have a few tables and I am facing this issue most likely with the table "item".
When I remove restaurant_id and category_id, it works fine.
CREATE TABLE IF NOT EXISTS restaurant
(
id ...
1
vote
2answers
32 views
copy entire PostgreSQL cluster to another (identical) server
I'm looking to clone a PostgreSQL 10 cluster from server1 to server2, which is running the same Postgres version on identical hardware. The purpose is load-balancing and HA. Things to keep in mind:
...
3
votes
2answers
4k views
starting pgbouncer on startup
I installed pgbouncer and configured it and it starts only via using this command
pgbouncer /etc/pgbouncer/pgbouncer.ini
however using service pgbouncer start it dose not start. I then set up ...
2
votes
1answer
151 views
Inefficient execution plan selected by postgresql
I have the following SQL query
explain analyze
select CERTIFICATE_ID,
SERIAL_NUMBER,
SUBJECT_DISTINGUISHED_NAME,
NOT_BEFORE,
NOT_AFTER,
FIRST_SEEN,
REVOKED,
LINT_ERRORS from (...
1
vote
1answer
22 views
sequence keys for static data
In my data model I have some static data that is added to over time as the system changes. For instance:
create table region (id SERIAL,crname TEXT);
customer has a link to the region, my system ...
0
votes
1answer
32 views
Schema and Query design for table with multiple languages [closed]
I've been researching on how to create a schema for a database that contains multiple languages. So I decided to separate the translations on a different table with a column as the language and ...
6
votes
1answer
4k views
Update by iterating table in batches faster than whole table in PostgreSQL
I have a table with approximately one million rows.
It is being used in production and I ran an UPDATE which covers ~95% of those rows.
After five hours I cancelled the request because it was taking ...
0
votes
1answer
260 views
SQL query to get the leaf record in a parent-child relationship
I use PostgreSQL. I have 2 tables, products and product_renewals.
Products
+----+--------+
| id | name |
+----+--------+
| 1 | Foo |
| 2 | Bar |
| 3 | Foo |
| 4 | Foo |
+----+------...
1
vote
1answer
105 views
Postgres Column Level Security
I have a schema snow within a database testdb. I'm trying to achieve column level security for my users. My users are boss, manager and intern. I set all of this table up as the superuser me. They are ...
0
votes
1answer
24 views
How to insert values to one table from the table stored in another database in postgresql?
Here is the structure of table:
`* Table 1 of database A contains mobile number and userId and both field cannot be blank
I want to fetch data from table 1 of database A and insert that with some ...
0
votes
1answer
97 views
Pass 'interval' value to date_trunc function in PostgreSQL procedure
I am fetching the "age" of two timestamp columns (End and Start) and further choosing it as to_char 'MM' format. So I have the difference of the months from two timestamp columns.
The syntax in the ...
1
vote
1answer
154 views
Full text search on larger text rows
I am wondering what is the best practice to use the right query in my case.
The problem is I tried the LIKE and ILIKE and it was faster then pg_trgm and the postgres full text search, because they ...
2
votes
1answer
105 views
Accessing a row directly at a table in PostgreSQL by using the PostgreSQL's internal library
I am writing a PostgreSQL extension using the C language. I would like to do the following tasks:
Store a set of rowids (or other row identifier, like ctid). Each rowid uniquely represents a row in ...
4
votes
2answers
785 views
Which index type is best suited for exact-match against a list of values?
I have this table:
CREATE TABLE public.foos (
id integer NOT NULL,
bar_string character varying
);
I want to do this query:
SELECT id from foos where bar_string in ('one', 'two', 'three')
...
0
votes
1answer
170 views
How to use windowing functions to find gaps in sequences grouped by a column, in PostgreSQL
I have a table structure which has 2 columns: client_id, order_no, and both are integers. I'd like to find gaps in order_no in the data so that, for example, for a table containing these rows:
42, 1
...
0
votes
0answers
61 views
Postgres TX Wraparound - confused
i am running postgres on aws and trying to get my head around the "tx wraparound topic".
i executed the following steps to "play" around
take snaptshot of production database (here "our_main_db")
...
2
votes
1answer
31 views
Why does DELETE not work after an UPDATE in a CTE?
CREATE table test (
id integer,
x integer
);
INSERT INTO test VALUES (1, 1);
SELECT works:
WITH res AS (
UPDATE test SET x = 10 WHERE id = 1
RETURNING id
)
SELECT id FROM test WHERE ...
1
vote
1answer
9 views
Postgresql replication slot used by barman has a long replay_lag
I set up barman using wal streaming, I just notice I have a hug replay_lag. I'd like to keep it down to 0 but have no idea about how to do this.
I also have a database replica and it is working fine.
...
24
votes
4answers
38k views
Very slow DELETE in PostgreSQL, workaround?
I have a database on PostgreSQL 9.2 that has a main schema with around 70 tables and a variable number of identically structured per-client schemas of 30 tables each. The client schemas have foreign ...
0
votes
2answers
34 views
Does enabling pg_stat_statements reduce disk space?
Recently I was informed that since enabling pg_stat_statements, the available disk space is dropping fast. I don't think it's caused by that, but I wonder: Does enabling pg_stat_statements reduce disk ...
4
votes
1answer
134 views
Calculating balance sheet with a single query
I have this schema for double entry bookkeeping system:
CREATE TABLE account (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
parent_id INTEGER REFERENCES account(id),
UNIQUE (name, ...
