Questions tagged [postgresql]
All versions of PostgreSQL. Add an additional version-specific tag like postgresql-11 if that context is important.
12,295
questions
-2
votes
1answer
10 views
Not able to connect through PostgreSQL [closed]
I am trying to connect through few Remote backends through my PowerShell Script and so far I am able to connect through SQL Server & Sybase backend without any issue . But while connecting ...
0
votes
1answer
12 views
Count missing values in each column of the table in PostgreSQL
Question: Which column of fortune500 (a table) has the most missing values? To find out, I have checked each column individually mentioned below, which is very tedious.
SELECT
SUM(CASE WHEN ...
0
votes
1answer
14 views
How to write join query in sub query method?
This is the question:
What is the most common stackoverflow tag_type? What companies have a tag of that type?
My Solution:
SELECT type, count(tag) AS count
FROM tag_type
group by type
order by count ...
2
votes
0answers
26 views
Create a foreign-key-like constraints that checks something more generic than equality
There are some situations where I would like to set up some constraints in a table that depend on values in another table.
For example, I can define two tables as:
CREATE TABLE foo (
foo_id serial ...
0
votes
1answer
18 views
Postgres CREATE OR REPLACE VIEW… can it block?
I'm looking for a way for a table to be swapped while it's being used. Say we have a view
CREATE OR REPLACE VIEW my_data AS SELECT * FROM my_data_v1;
that is being used, e.g. lots of SELECT * FROM ...
-1
votes
0answers
12 views
SQL parsing error in RelaX [closed]
I have the following relation in RelaX
Flights = {
flno:number, from:string, to:string, distance:number, departs:number, arrives:number, price:number
}
Aircraft = {
aid:number, aname:string, ...
1
vote
1answer
22 views
Does Postgresql create a new tuple for an no-change update statement?
I need to fix some data issues in a Postgresql database, the primary of which is trimming whitespace from text columns. I'm doing this using a statement like:
UPDATE app.products
SET "description&...
1
vote
1answer
18 views
How to compare the data type of a query value in Postgresql
How to compare the data type of a query result value? For example, to check if something is an 'interval'
select pg_typeof(current_timestamp - '2020-01-01 00:00') = 'interval';
gives the error
ERROR: ...
0
votes
0answers
10 views
How to find the columns, for which Postgresql spends the most time to find them? [duplicate]
I would like to define some indexes in my database. But I am not sure on which columns. I try to find out, which columns are most used. I found the view pg_stat_user_tables. But it shows just ...
0
votes
1answer
15 views
Postgres JSONB - Flatten nested objects and groupings
As i've learned, if i need to flatten a JSON contained in a specific field of my postgres db table, i can use one of the following functions/operators
Now, those functions are perfect for a simple for ...
0
votes
1answer
19 views
PostgreSQL know table hierarchy
Is there a way to know the parent - child relations (in terms of foreign keys) of all the tables in postgresql database? We are planning a full database migration using AWS DMS, we need specify in ...
0
votes
1answer
33 views
Enabling TLSv1 in postgres12 / openssl1.1.1f
After upgrading a server to postgres12 / OpenSSL 1.1.1f (Ubuntu 20.04) I got tlsv1 alert protocol version error messages from a client using an old openssl 1.0.1 library supporting only tlsv1.
I ...
-1
votes
1answer
16 views
indexing impact
If I have a table with 4 columns(a,b,c,d)
I create the following index for this table :
CREATE INDEX index_t
ON table(a,b,c);
Will the PostgreSQL optimizer consider using the index in the following ...
0
votes
1answer
28 views
Cross join flatten JSON - flatten only one the most recent row
As reported in this question, i want to flatten a JSON starting from a nested JSON object that contains arrays like this one:
port_name, port_description, port_status_int, port_status, port_speed_int, ...
0
votes
1answer
17 views
What is the effect of number of CPU cores on update operations in Postgres?
I have batch update operations being sent to the PostgreSQL server to be executed. Each operation (batch) will be updating rows in the same table but it is assured that they will be updating different ...
0
votes
2answers
48 views
Postgres create Index
im running out with ideas for creating postgres index for a better performance.
This example below is a selection of a big table with many entries for a diary to person relation.
usually, i create an ...
0
votes
0answers
9 views
Getting Transient connection Error while connecting applications to postgresql using pgbouncer
We are connecting PostgreSQL using pgbouncer from application. However, it is not working. it is giving transient error while connecting.
I can provide other parameter details
An exception has been ...
0
votes
0answers
37 views
Data not reflecting after pg_upgrade in expected database
My Postgres v10.11 has data_directory at '/post10/data' location and has a tablespace at location /xyz for my 'db1' database.
I have tried upgrade of Postgresql database (version 10.11) to Postgresql (...
-2
votes
0answers
9 views
Unable to See Server Option in pgadmin [closed]
I have installed pgadmin database on my Windows laptop and it was working fine then suddenly, I am unable to see anything in the database. I have uninstalled the pgadmin and install again but won't be ...
-1
votes
1answer
41 views
How can I manage live database updates & migrations for a team? [closed]
I don't want to directly access the database and run SQL UPDATE commands.
It's error prone and there's no way to trace previous changes, including who did it.
Is there an open source tool i can use to ...
0
votes
0answers
18 views
pgbouncer is not creating connections as per min_pool_size
I have setup a pgbouncer in my local vm and connecting this with Azure PostgreSQL. The setups are as:
MIN_POOL_SIZE = 200
DEFAULT_POOL_SIZE = 4000
I can provide list of other parameters and its ...
0
votes
2answers
23 views
Hint: There is a column named “pma_timestamp” in table “table_a”, but it cannot be referenced from this part of the query [closed]
I want to use a user defined function so that while inserting values in column i want to insert last_day of the month in one column by referencing other column in same table.
my code is-
insert into ...
0
votes
2answers
24 views
Error querying postgresql table created in pgAdmin
I have successfully created a table in pgAdmin which generated the code:
CREATE TABLE public."Test3"
(
"PID" integer,
"Name" character varying(20),
PRIMARY ...
0
votes
1answer
20 views
How to use repmgr with PostgreSQL 10?
Followed the official repmgr document, I set these items to the configuration file:
shared_preload_libraries = 'repmgr'
max_wal_senders = 10
max_replication_slots = 10
wal_level = 'hot_standby'...
0
votes
1answer
15 views
drop postgresql schema public without losing extensions
As part of my development workflow, I often use
drop schema public cascade;
create schema public;
in order to get a clean state of the test database before re-loading my table and stored procedure ...
-1
votes
2answers
17 views
Returning best matching data objects given a boolean list
I hope this question is appropriate for this forum. I am setting up a PostgreSQL data base and need a few ideas on how to structure my tables so I can pull the data I need.
I know I need a table that ...
0
votes
0answers
22 views
postgresql log is not work
My postgresql log on Windows appears to be not working.
I followed this postrge quey logging for windows question/answer and my query is not recorded.
2020-09-22 09:38:37.823 KST [20060] ė”ź·ø: DB ...
0
votes
1answer
24 views
Is there any significant performance difference when using string vs bigint as a primary key in Postgres (Aurora)? [duplicate]
We can use same logic for both types (string/bigint) to generate consecutive ids (e.g. like https://instagram-engineering.com/sharding-ids-at-instagram-1cf5a71e5a5c). This way both are naturally ...
1
vote
0answers
20 views
How can I change the password for pgadmin in Windows?
I want to know how I can change the password of pgAdmin, the same one that is created when it is installed in Windows
The point is that I have not forgotten, but for personal reasons I want to change ...
1
vote
1answer
26 views
Strategies for long term data growth optimization for a query with an index on a joined table
Given two simple, contrived example tables
create schema test;
create table test.exchange (
id serial primary key,
created_at date not null
);
create index on test.exchange(created_at);
create ...
-1
votes
1answer
18 views
What syntax does PG want me to use?
I have:
SELECT * FROM table WHERE textcol NOT IN $1;
I get:
ERROR: syntax error at or near "$1"
$1 is sent as an array of strings from the application. Parameterized query. pglib. PHP.
0
votes
1answer
21 views
Proper way of restoring base backups
We have daily backups of a postgres db and it is in the form of a tarred (gz) base backup.
I need to restore this into another db of the same version. However, I am confused with the article that I am ...
0
votes
1answer
21 views
Is there any way in Postgres to parameterise a procedure for sort order column, when the columns are of different types?
I am trying to write a function which does (some complex stuff) and returns the results in different orders based on a parameter.
A simplified version would look something like this:
CREATE OR REPLACE ...
0
votes
1answer
14 views
PostgreSQL update table then insert on another table
I have a User table and a Log table. I need to update a field on the User table and insert a new entry on the Log table recording the update. I have written the statement for the update:
UPDATE ...
0
votes
1answer
32 views
Understanding JOIN Syntax
Given:
postgres=# \d foo
Table "public.foo"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | ...
-2
votes
0answers
26 views
What's with these empty log files? Why does csvlog mode create plaintext ones? [closed]
I've been fighting for days now to get logging set up. I've had to write a ton of code manually because PG doesn't provide any automated mechanism to do this, for some reason, nor even tells you ...
0
votes
1answer
11 views
Using intermediate result in backend with node-postgres
Firstly, I am using the node-postgres module to access database in a node application.
I have this SQL code that pulls up data from a table data of which is pulled from another table.
SELECT dwa_id
...
0
votes
1answer
34 views
Why doesn't the $1 syntax work for all queries?
I tried to do this:
COPY postgres_log FROM $1 WITH csv;
And with a separately sent file path, like I make all normal queries (SELECT/UPDATE/INSERT/DELETE). However, it gives:
ERROR: syntax error at ...
1
vote
1answer
63 views
PostgreSQL: query slow, planner estimates 0.01-0.1 of actual results
I will preface this by saying I'm not well-versed in SQL. I mainly work with ORMs, and this recent headache has brought me to dive into the world of queries, planners, etc..
A very common query is ...
0
votes
1answer
40 views
Ensuring relationship between child tables exists prior to SQL insert
I have a situation where I have three tables: user, assignment and test. A user must have completed an assignment before he can take the test. This means the test table has both both a user foreign ...
0
votes
1answer
42 views
Inadvertently created a table named “select * from table where …” [closed]
I found out I created a table with this literal name
"select *
from rendiconti.mapping_stringhe ms
where nome_tabell", but I don't know how!
I know that a query like
select column1 into ...
-1
votes
1answer
25 views
Is InfluxDB faster than PostgreSQL [closed]
According to this article the only real benefit influx has over postgres (using time-based indices) is space used. PostgreSQL is more performant, with time indices.
Why would one use influx-db over ...
1
vote
0answers
29 views
Update and insert performance with partial indexes
I have different queries for fetching data from a large table (about 100-200M rows). I've created partial indexes for my table with different predicates to fit the query because I know each query.
For ...
0
votes
0answers
14 views
Create hot standby on top of Barman streaming backup
I set up a Barman server that uses pg_basebackup and pg_receivewal for streaming replication. It is pretty much the same setup as described in the Barman documentation (scenario 1).
Is there a ...
-1
votes
1answer
21 views
Posgres database requires a group by clause. (column \“candidates.id\” must appear in the GROUP BY clause or be used in an aggregate function",)
I have 2 tables, agents and candidates. The candidates table has a foreign key "agent_id". I want to get all agents and aggregate the candidate counts on each agent object; the different ...
1
vote
1answer
24 views
Postgres Query not using Index but faster with Index
Sorry if this seems to be a duplicate question. I'm using Postgres 11.6 on AWS RDS. I have 2 tables:
CREATE TABLE public.e
(
id character varying(32) COLLATE pg_catalog."default" NOT ...
0
votes
1answer
35 views
What is a good strategy for updating lot of rows in postgersql?
I have an inventory table which contains item_id and the quantity remaining of the item (And also some other meta data). I have an administrator update the inventory by uploading a CSV file which ...
0
votes
1answer
40 views
Postgres query slow despite index being used
I have the following tables:
The main lead table with close to 500M rows:
create table lead
(
id integer,
client_id integer,
insert_date integer (a ...
0
votes
1answer
21 views
Creating and dropping partition in PostgreSQL “on the fly”?
In our web-applicatons we have PostgreSQL database. Users write into and delete from this database ecology forecasts. Because data amount is significant (more than 100 Gb), we use declarative ...
0
votes
1answer
14 views
How can I determine what's a better configuration for my app? [closed]
I'm currently using Postgres 12 on AWS RDS and am using their default configuration for the database.
I use a gem called PgHero that advises me to use PGTune to improve the efficiency of the database. ...
