Questions tagged [postgresql]

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

Filter by
Sorted by
Tagged with
0
votes
1answer
18 views

PostgreSQL Correlation Between “max_connections” and “numbackends”

I'm trying to figure out why the field numbackends in pg_stat_database never goes above 16 (and often stays the same even after a restart). I have max_connections set to 100 and ...
3
votes
1answer
53 views

Get base table from materialized view in postgresql

I use materialized view in postgresql. when retrieving the list of materialized view (select * from pg_matviews), in the "definition" column, there is a query building the materialized view. how do i ...
2
votes
2answers
27 views

Postgres database not using indexes

I have a SQL optimization question. Table Name: charts Total Records: +250 million Columns: timestamp(string), imported(boolean), .... Indexes: CREATE INDEX index_charts_on_timestamp ON public....
0
votes
0answers
13 views

on insert rows in a table how to update that inserted data in another table?

I'm trying to update large amount of rows (20,000) rows, so i created a temp table similar to this one. and i want to create a trigger on insertion or update and a function to take the inserted or ...
0
votes
0answers
22 views

Restoring Postgresql database on Windows if only “data” folder available?

Is is possible to restore a PostgreSQL database if all I have available is the "data" (C:\PostgreSQL\data in my case) folder of the server whose databases I want to restore.I know this folder contains ...
-4
votes
2answers
67 views

Why is this query not returning the right result? [closed]

I want to select/filter by 2 columns status and state from a table of 6 columns . I have written the query below to select cars that are unsold and either new or used but the filter returns both new ...
1
vote
1answer
15 views

Using plpgsql to remove all functions

I intended to drop all functions in all schemas within a certain database, however, after I executed the bash file containing the following code block, the functions are still there # use pg_dump and ...
0
votes
0answers
15 views

Postgres count the same join by different values

I've been trying to create a query that counts the number of different types of events associated with each entry in another table city job job_events id | name | state ...
0
votes
0answers
12 views

Postgresql to have date of 12 last months but it should 1st day of every month [duplicate]

hei, i am getting ERROR: syntax error at or near "int" in declare. declare v_n int; v_i int = 0 v_n := month(now()) - 1 select v_n if object_id('tempdb..#t12345') is not null then drop table #t12345;...
0
votes
1answer
40 views

How to query results from a huge postgres database?

I am trying to query a few tables in a 125GB Postgres database. I find a hard time getting the results back quickly! Even to find unique rows of a column it takes more than 10 minutes. Either I use a ...
1
vote
1answer
158 views

Error: FATAL: no pg_hba.conf entry for host - PostgreSQL - pg_hba.conf is empty

I upgraded the PostgreSQL database from version 10 to 11. As I tried to access the database from a client pgadmin4 console, I got the below error: I checked the two file postgresql.conf and pg_hba....
0
votes
0answers
29 views

Bidirectional Publish/Subscribe with PostgreSQL

With PostgreSQL, I can set up a publication and subscription between two databases and the subscription will duplicate the contents for the published table. What I found interesting is that the ...
0
votes
0answers
29 views

Postgresql - Can we create streaming and logical replication replication from same server

I have a postgresql setup with 1 master and 3 slaves. Now I want to setup a replication for only one database to reporting server. My problem statements: I can't use bucardo (no approval for ...
1
vote
0answers
51 views

Querying foreign table with SP-GiST index results in “ERROR: cache lookup failed for type 0”

I'm using PostgreSQL 11.3 (compiled by Visual C++ build 1914, 64-bit) and I want to access a table in another Postgres database using postgres_fdw but since the original table has an SP-GiST index on ...
0
votes
1answer
5 views

Postgresql. Real-time copy of a database to another disk on the same server

How to make a real-time copy of the database to another disk? My database is very small, but WAL-logs are very large (many changes in time). I don't need 'Point-in-Time Recovery', I just need the ...
0
votes
1answer
20 views

Merge 2 columns from joined table

lets say I have 2 tables : group { id, export boolean not null default false } and channel { id, group_id null, export boolean null } Is it possible, with 1 query, to ...
0
votes
0answers
25 views

WAL buffer checkpoint/flush time during serialization isolation

I have a doubt regarding the flush/checkpoint time of WAL buffers during serialization isolation in Postgres. Is it possible to lose acknowledged writes even if transaction runs in serialization ...
1
vote
1answer
24 views

How do I select the count of a specific value without grouping in postgresql?

I have a view that's a result of a UNION of two selects. It has contents similar to this: id foreignkey 1 a 2 a 3 b 4 c I'm looking to add a column in the view's definition that will show a ...
0
votes
0answers
11 views

Script to alert me that a droplet resize is necessary for PostgreSQL DB to fit into memory?

I am trying to answer the question, "How big is my PostgreSQL DB relative to memory on my Ubuntu server?" The goal of asking this question is to be able to alert me to when the size of my database is ...
0
votes
0answers
25 views

“syncing” rows to another table

I have a database: companies -> properties properties -> accounts accounts -> line_items An "account" represents a dual-entry accounting account (like Cash or Accounts Receivable) Each property ...
1
vote
1answer
24 views

Why can't I double quote built-in type names in Postgres?

Why can't I double quote the primary built-in type names in Postgres despite being able to double quote their aliases? For example, these work fine SELECT CAST (1 as bool); SELECT CAST (1 as "bool");...
0
votes
0answers
24 views

Know which table is READ / UPDATE / INSERT / DELETE

Context : I have a legacy PostgreSQL database (currently 9.5 but can be any newer version) and a tons of script / program acceding it. All this script can do various operations on the table's row (...
0
votes
1answer
22 views

'utf-8' codec can't decode byte ERROR when querying data

I have created this foreign SQL Server using tds_fdw foreign data wrapper: CREATE SERVER servprod_sqlprod_mydb FOREIGN DATA WRAPPER tds_fdw OPTIONS (servername 'XX.XX.XX.XX', port '1501',msg_handler ...
0
votes
1answer
17 views

Split rows and assign a specific value to each group

I have a scenario where i need to split rows in a table and assign certain values to a column for each group. The table name is customers(contains around 300K rows) and i need to split the customers ...
0
votes
3answers
87 views

Postgres speed up index creation for large table

I have a large Postgres table with 2+ billion entries (1.5TB) and mostly non-null, char var columns. To speed up inserts, I dropped the indexes before bulk uploading. However, it is now taking forever ...
-1
votes
2answers
31 views

index for ILIKE on postgresql, inconsistent performance

I have a table with about 64 Million rows, I have created the following index on the address_url column CREATE INDEX trgm_idx_address_link ON public.address_link USING gin (address_url ...
2
votes
1answer
50 views

How to pass variable to PL/pgSQL code from the command line?

I am running a psql script from the command line with variables something like: psql ...... -v now_utc=$NOW_UTC Then I want to use this in my script like: $$ DECLARE _now_date timestamp := :...
8
votes
2answers
79 views

Will Postgres perform the computation of calculated columns that aren't selected in a view?

I'm trying to understand the performance impact of selecting data from a view, where one of the columns in a view is function of other data in the original table. Does the computation get performed ...
0
votes
1answer
23 views

PostgreSQL 9.6: Update on a single row table takes an unreasonable amount of time

I've got a table called source which has a primary key on its id and a “Unique Constraint” on the column name. This is the way the table looks: id int4 name ...
0
votes
2answers
27 views

Speeding up Postgresql manual index reset?

I'm merging databases with each other. One problem I have is that both of the database tables have the same primary keys (starting from 1, but otherwise they are different records). To solve this, I ...
0
votes
2answers
36 views

SQL Server - TIMESTAMP caused exception

I am syncing from PostgreSql to MSSQL using SymmetricDS. Some tables of the source database have timestamp field set too '0001-01-01 00:00:00.0' and it could not be able to insert to destination(SQL ...
0
votes
1answer
161 views

Postgres Windows post-install “The database cluster initialization failed”

I've seen many solutions and have been working on this issue for hours now. After installation the post-installation script fails with the message above. This is a development installation of ...
0
votes
1answer
26 views

Reusing literal string in different parts of query

I created this PostgreSQL query while troubleshooting a problem that turned out to be due to non-printing characters in my strings: with cte as ( select 'The character between the xes is evil: xx' as ...
0
votes
1answer
23 views

In postgres, is using a lock better than transaction when reading data?

I have a race condition in the code for my database, and I'm wondering how I can prevent this using either locks or transactions? (And which is best) I'm using an ORM to update values, however the ...
0
votes
1answer
30 views

Fetching rows from million rows table(Optimization)

I have millions of rows in a table of Greenplum database out of which I have to fetch around 45k rows and store them in a python List. It's taking more than 2hrs to fetch the data. How can I optimize ...
0
votes
2answers
25 views

large number of tables and postgres query performace

I have a scenario where there are 40000 tables in the database. but there are no huge unions. Very simple single table select queries are performing much slower than usual (table with just 600,000 ...
0
votes
0answers
26 views

Advantage and Disadvantage of Partitioning by some of values of Primary Key

I'm going to implement/deploy a table like this: CREATE TABLE docref ( namespace VARCHAR(50), key VARCHAR(50), time_order INT, state SMALLINT, hash TEXT, ...
0
votes
0answers
20 views

Does Postgresql logical replication support hot standby?

Postgresql has two ways of replication Physical replication Logical replication with physical replication everything is send over and the servers must have the same major release. Which makes ...
1
vote
2answers
86 views

How to structure an append-only database in PostgreSQL?

Preface We have been stuck for years using a monolithic program with terrible and inefficient database structure. Now we are finally moving away from it with our own program. The only area we are ...
3
votes
1answer
43 views

Slow performance on partitioned PostgreSQL table when using now() to filter

I have a PostgreSQL v10 table partitioned on a timestamp field: CREATE TABLE front.probes ( bucket_start timestamp NOT NULL DEFAULT now(), -- Extra fields here ... ) PARTITION BY RANGE (...
1
vote
1answer
30 views

Optimize a slow running Postgres SQL query

I have a SQL optimization question. Raw SQL SELECT releases.id AS release_id, releases.name AS release_name, releases.label_id AS release_label_id, releases.provider_id AS release_provider_id, ...
0
votes
1answer
51 views

Psycopg2 Errors on SQL statement when trying to copy data from CSV file into PostgreSQL database

I am not a developer or PostgreSQL DB admin, so this may be basic questions. Logistics: Windows 10 server / pgAdmin 4 / Postgres 10 / Python 2.7.13 I'm using a python script to ingest external data, ...
1
vote
2answers
30 views

Split cases in WHERE clause based on boolean variable

I have to split a WHERE clause based on a boolean variable: If ShowZeroVariable is true, I only what to show rows value 0 in a certain integer column. If false I want to show all that are greater ...
0
votes
1answer
29 views

Restore database from a save

I use pgadmin 4 and PostgreSQL I try to restore a database from a save. I have put the directory of my file save C:\Users\H2O\Desktop\temp_DB\ as a binary path in file preferences. When I try to ...
0
votes
1answer
43 views

PostgreSQL multiple column foreign key [closed]

I've got the table "session" with CONSTRAINT u_session UNIQUE (where, who, when) I need to use that combination of (where, who, when) as the foreign key in table "results". How can I do it?
1
vote
2answers
61 views

Postgres: why did this trigram index slow down regexp queries?

I have a TEXT keyvalues column in Postgres: select * from test5 limit 5; id | keyvalues ----+------------------------------------------------------ 1 | ^ first 1 | second 3 ...
2
votes
1answer
47 views

Is there a way to have PostgreSQL consider the Interpunct character · as a letter?

I'm working on an application storing strings of the Gallo language. In this language the Interpunct character · is considered a letter, not a word divider. So I want for instance the query SELECT * ...
0
votes
1answer
32 views

index does not get used when there is “OR” in the query

I have an index on the check_timestamp column, the index gets used by the following query ... where check_timestamp < NOW() - INTERVAL '30 days' ORDER BY check_timestamp NULLS FIRST LIMIT 10 ...
0
votes
0answers
11 views

how do you query for empty tables in Redshift?

I would like to perform some long overdue DB cleanup, and am trying to analyze table statistics. I'd like to: Get a list of all empty tables Get metadata by table relating to the last time data was ...
1
vote
1answer
36 views

In PLPGSQL assign a variable based on result of SQL function

I have a PLPGSQL function which receives an address in two parts: num = '123' location = 'main street, anytown, KS' To utilize the various portions of location I found there is a SQL function ...