Questions tagged [postgresql]
All versions of PostgreSQL. Add an additional version-specific tag like postgresql-11 if that context is important.
9,833 questions
1
vote
0answers
3 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
1answer
8 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
10 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
9 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 ...
-3
votes
2answers
44 views
Why is this query not returning the right result? [on hold]
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
12 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
11 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
11 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;...
-5
votes
1answer
37 views
Create table of sequence first days of the calendar month in Postgresql [on hold]
hei,
I am trying to create temp table in Postgresql sql script is:
I know how to write in SQL server but in Postgresql while running below code i am getting error as ERROR: syntax error at or near "@"
...
0
votes
1answer
32 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
30 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
16 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
18 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
23 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
18 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
19 views
Delete duplicate Data joining different tables
I've this problem, and I'm using PostgreSQL 9.4.12 on x86_64 ; I have these tables
Entity
------------
idEntity_pkey
value
...
...
Points
---------------
idPoints_pkey
idEntity_fkey
x
y
...
1
vote
0answers
60 views
Filter sorted overlapping ranges with window functions
I have the following data set:
layer period
1 ["2019-06-17 10:00:00","2019-06-17 11:00:00") -- visible
1 ["2019-06-17 12:00:00","2019-06-17 14:00:00") -- visible
1 ["2019-06-17 15:00:...
0
votes
0answers
13 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
23 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
9 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
23 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
20 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
22 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
11 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
2answers
39 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 ...
0
votes
0answers
22 views
TPC-H, create tables with primary/foreign keys or indices [on hold]
I'm a benchmarking noob. I need perform some benchmarking using TPC-H. I noted that the original dss.ddl script provided by the tpc website for TPC-H has no indexes or primary/foreign keys.
The ...
-1
votes
2answers
24 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
29 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 := :...
7
votes
2answers
56 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
0answers
23 views
plpgsql: mismatched parentheses at or near “;” in nested loop structure [closed]
I wrote a do block to keep only the tables in a Postgres database, but I received this error message when I ran the script. I can't see where parentheses are mismatched. I added the labels after I saw ...
0
votes
2answers
25 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
31 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
33 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
18 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
20 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
26 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 ...
-1
votes
0answers
6 views
Removing records from PostgreSQL database periodically that surpass timeline [closed]
Found this Removing records from database periodically that surpass timeline
Can somebody help with the same for PostgreSQL database.
Thanks in advance,
Albin
0
votes
0answers
17 views
PostGreSQL “in-memory” with multiple terabytes of working set data? [closed]
I've come to the understanding that PostGreSQL generally scales up well, with an 8-socket x86 configuration having been tested before showing near-linear scalability. However, such tests were done a ...
0
votes
2answers
24 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
20 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
16 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
41 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 ...
0
votes
0answers
9 views
use database in W10 from vmware virtual machine(w7) [closed]
So im trying to access postgresql database inside a windows 7 virtualmachine but seems thath im having some networking problems due to i cannot access from inside virtual machine but yes from outside
...
2
votes
1answer
10 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 (...
-2
votes
0answers
18 views
AWS Aurora Severless [closed]
I am building a new app that I plan to put on the App stores. I was thinking of using the AWS Aurora Serverless. Since this is relatively new and I wanted to ask what have some of the experiences of ...
1
vote
1answer
24 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
0answers
21 views
Looking for a tool that runs on Linux to do bulk transfer of data from SQL Server to PostgreSQL [closed]
I am providing data to a client from my company's SQL Server database. My client wants the data transferred to his PostgreSQL database running on a Linux VM. I can find tools that run on Windows to ...
0
votes
1answer
14 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, ...
