Questions tagged [postgresql]
All versions of PostgreSQL. Add an additional version-specific tag like [postgresql-13] if that context is relevant.
13,435
questions
0
votes
1answer
404 views
PostgreSQL not using varchar index when ordering by primary key
My company has a pretty large PostgreSQL 9.4.5 database (we'd rather not try updating it atm as it's stable), one of the tables used to hold some sort of statistics wasn't designed well and when it ...
3
votes
1answer
137 views
How to turn a set of flat trees into a single tree with multiple leaves?
We have this beautiful Postgres tree generator. Yet it kind of produces cuts of a tree not a whole tree all at once:
item_id jsonb_pretty
1 {
"title": "PARENT",
"...
0
votes
1answer
1k views
pg_restore: [archiver] could not find entry for ID 12
I'm trying to restore a dump (-Fd --data-only) from PostgreSQL v9.4.9, but I've come across the following error:
pg_restore: [archiver] could not find entry for ID 12
Exactly the same happens if ...
2
votes
1answer
5k views
Postgresql: Does autovacuum impact query performance? Does it apply locks which impact the performance of other queries? [duplicate]
Postgres recommends using the autovacuum to remove the dead tuples. But does running the autovacuum end up making other queries to the same table slow? Does it add locks (row or table level) which ...
-2
votes
1answer
15 views
POSTGRESQL Query and insert based on results [closed]
In this case I'm trying to perform a SELECT based query followed by an insert of new records into the same table based on those results. For example a simple table:
Table "samples"
Fields:
...
0
votes
1answer
2k views
Having issues with postgresql, what should I log?
Here's the error message I am receiving.
could not translate host name "localhost" to address: hostname nor servname provided, or not known
From my searching, it seems like postgresql has crashed ...
0
votes
1answer
24 views
Actual used and free space in WAL segments
I am new to the PostgreSQL world. I have an experience in administrating Microsoft SQL Server and Oracle databases.
In SQL Server, we are able to find how much space is actually used in pre-allocated ...
2
votes
1answer
24 views
What are the possible meanings of NULL and zero for autovacuum_count, last_autovacuum, and autoanalyze_count in PostgreSQL?
I have a big table (let's call it big_table) in a PostgreSQL 12.7 database running on a Debian GNU/Linux server. The machine has 8 GB of RAM and 4 CPU cores, and mostly dedicated to this PostgreSQL ...
-1
votes
0answers
26 views
Create Primary Key for Table [closed]
I am trying to identify a primary key but ended up having no idea. Can anyone please give me suggestions?
I have the following table:
racer_id | team_id | timestamp | races | time | typed | ...
0
votes
1answer
32 views
Create Postgres Index for OR query
This is the query I want to optimize:
SELECT *
FROM invited_member_events
WHERE (user_id = '422820341791064085'
OR inviter_user_id = '422820341791064085')
AND guild_id = '...
0
votes
1answer
97 views
How to eliminate duplicates made by recursive CTE [closed]
I have following schema in PostgreSQL 12 (objects and their relations are versioned by date ranges):
CREATE TABLE tmp_deps (
id bigint,
code text,
name text,
start_date date,
...
0
votes
1answer
21 views
How to list all sequences by psql with postgres?
I tried \ds didn't get any relations data.
But I use SELECT * FROM information_schema.sequences; can get all the data.
Why doesn't \ds work?
0
votes
1answer
341 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
2answers
250 views
How to deconstruct a JSONB object
In a PG10 database, I have a table called ToDos where I have defined a jsonb field called tags.
Records look like
+----+-----------------+--------------------------------------------+
| ID | name ...
1
vote
1answer
32 views
Constraint in ON CONFLICT clause has no associated index
I have this simple setup using PostgreSQL 13, latest dockerized:
CREATE TABLE stop (
id INT UNIQUE,
name TEXT,
PRIMARY KEY(id)
);
CREATE TABLE line (
id INT UNIQUE,...
0
votes
0answers
17 views
How to select rows each having all of the specified tags? [duplicate]
This is the table containing hytags_hyobjects that are tagged with hytags_tags
Table "public.hytags"
Column | Type ...
0
votes
2answers
1k views
Is it Possible To Take Full And Differential Backup in Postgresql Like Sql Server?
I have a postgresql server. I want to take its backup full and differential mode as it is like in sql server. Is it possible to do this in postgresql? How can i do it, if it is?
4
votes
1answer
1k views
For each category, find the count of foreign-key items in all child categories using a PostgreSQL Recursive CTE
I have a typical tree structure stored as an adjacency list in PostgreSQL 9.4:
gear_category (
id INTEGER PRIMARY KEY,
name TEXT,
parent_id INTEGER
);
As well as a list of items attached ...
0
votes
1answer
221 views
Realtime Update MySQL on every Update occurred on PostgreSQL
I have a PostgreSQL for my accounting system and a MySQL database for my php website, i want to sync these two database for every update may occurred on PostgreSQL it also updates MySQL database, they ...
0
votes
1answer
126 views
How to resolve the error for “Alter Subscription disable”?
I have the following subscription for logical replication:
pctest1=> SELECT subscription_name, status FROM pglogical.show_subscription_status();
subscription_name | status
----...
0
votes
1answer
23 views
PSQL asks for password for a specific database
I am hosting two different databases (manage_invite and manage_invite_v49) on the same server.
As you can see, when I try to connect them using the CLI, it demands the password for the second ...
0
votes
1answer
15 views
Is there an analog to pg_notify that allows (un)listening from a dynamically generated channel name?
I am able to do something like the following in my application:
PERFORM pg_notify((
SELECT CONCAT('some_functional_prefix_', notify_key)
FROM some_table
WHERE lookup_key = my_lookup_key
), '');
...
0
votes
1answer
25 views
Make ALTER TABLE wait for lock without blocking anything else
Lots of PostgreSQL ALTER TABLE commands, like adding a new column with a default value, have clever optimisations in the latest versions of PostgreSQL that allow them to execute basically instantly, ...
12
votes
1answer
11k views
Faster query with pattern-matching on multiple text fields
I have one Postgres table with more than 20M tuples:
first_name | last_name | email
-------------------------------------------
bat | man | batman@wayne.com
arya | vidal | foo@...
-2
votes
1answer
42 views
How I can search inside the json that is consisted of an array of a JSON into objects?
Ι have the following table named discounts:
id Serialize
discount INT
offers JSON
With the following records
id
discount
offers
1
10
[{"type":"discount","percent":100},...
7
votes
1answer
162 views
Reckless behavior of psql \e
psql 13.2 executes the query buffer no matter what when exiting the editor, even after quitting it - in which case the buffer may hold the most recently executed SQL query now.
To reproduce:
Start ...
0
votes
0answers
17 views
Convert jsonb column into view for Grafana
I have a partially dynamic data that I have to save into Postgres 13 and then visualize it in Grafana.
Example for one document insert (partial)
{
"Timestamp": {
"$date": {
...
1
vote
0answers
22 views
Postgres - get list of table that no query has run against them
Is there a way to get list of Tables that has not been used for specific number of days?
For example of no Query has run against that table for specific number of days?
I wrote this query, but it's ...
0
votes
1answer
19 views
No schema, tables after sucessful restore of PostgreSQL database
I backed up the stack exchange archive database hosted on an RDS instance of PostgreSQL like so:
pg_dump stackexchange -h <host> -U postgres -password -o > stack.dmp
...A couple of hours ...
3
votes
1answer
30 views
Postgres varchar(100)[] -> text[] column conversion
I need to change a column type from varchar(100)[] to text[]. The table has >100M rows and heavy write load, so I'd like to avoid taking an access exclusive lock and rewriting the index. I saw in ...
1
vote
1answer
618 views
RDS Postgres - pg_stat_statements is missing queryid column
I upgraded my RDS postgres instances from 9.3 -> 9.4 -> 9.5 several months ago. When installing the pganalyzer-collector tool, I got a funny error that the "queryid" column does not exist in the ...
5
votes
1answer
305 views
How do I create an insert-only user for a Postgres table with an index?
Here's my proposed schema:
CREATE TABLE Surveys (
id serial primary key,
user_email citext,
survey_data jsonb,
created_at timestamp default current_timestamp
);
CREATE INDEX ...
0
votes
2answers
18 views
ALTER ROLE on replica
I have a primary server, with a secondary replicating it.
I want to change a role from LOGIN to NOLOGIN.
I can do this easily on the primary, but the change doesn't replicate to the secondary, and I ...
4
votes
1answer
3k views
Cast all boolean fields in a SELECT * query to integer
In PostgreSQL, I can cast individual boolean fields to integer when I want to get 0/1 output instead of f/t.
SELECT some, thing, mybool::integer FROM mytable;
But is there a way to ask PostgreSQL to ...
12
votes
4answers
29k views
How can I determine if a table exists in the current search_path with PLPGSQL?
I'm writing a setup script for an application that's an addon for another application, so I want to check if the tables for the other application exist. If not, I want to give the user a useful error....
9
votes
2answers
9k views
PostgreSQL and default Schemas
Whenever I create a brand new database in PostgreSQL Maestro it creates the following list of default schemas:
Now from my understanding Schemas are like folders for organization, etc. So the ...
0
votes
1answer
32 views
Perform Postgres Upgrade from 9.6 to 13 with minimal downtime
I am looking to upgrade our Postgres 9.6 server to Postgres 13.
I have looked into the following options:
pg_upgrade, if I was doing this without the --link option, this would take considerable time ...
0
votes
2answers
44 views
Export a query result directly to Excel “xlsx” format [closed]
I made a little Linux script that exports the result of a PostgreSQL query to csv with a date and time suffix.
This is my script:
#!/bin/bash
psql postgresql://test:test@192.168.1.2/test << EOF
\...
0
votes
1answer
22 views
Joining a tree structure
Table 1 (named as family) has a tree structure with 2 columns id and pathid, where pathid describes the tree structure. In the following table 1 is a root node with 2 as its child. 3 is a rootnode ...
1
vote
2answers
27 views
Tracing Postgres frontend and backend messages
As per my understanding when we execute a command or query, the Postgres client sends a frontend message, and in return gets the response in the backend message format.
How do I capture and check ...
2
votes
1answer
1k views
Access denied error when archiving WAL during postgresql 9.5 replication setup
I want to setup Postgres replication on Windows. I followed the given youtube url step by step: youtube video.
For archive_command I use this:
archive_command = 'copy "%p" "\\\\ip-address of ...
1
vote
1answer
2k views
Show all privileges given to a schema or user in postgres
I want to know what are the privileges a given schema or user has in Postgres. Like whether it can create a table, view, function, sequences or not.
I found one query but it showed privileges on table ...
2
votes
2answers
42 views
Convert postgres varchar[] to text[] without downtime
As I discovered in researching this question, converting a column from varchar(100)[] to text[] requires a table rewrite and rebuilding indexes.
Is there a way to manage this conversion without the ...
5
votes
1answer
12k views
How does unnest() treat NULL values and empty arrays?
I am trying to achieve something like LEFT JOIN with array unnest() function. I want the query to return a row with a null value if the array is empty. Therefore, by using a CASE construct, I wanted ...
1
vote
1answer
2k views
pgAdmin 4 Not connected to the server
I've installed Postgresql 11 and pgAdmin4 (version 4.5) and while I'm able to create tables in a new database, if I attempt to SELECT or INSERT into those tables I always get a message 'Not connected ...
0
votes
1answer
48 views
Issue with Patroni - postgres not starting
I am learning Postgres and I have installed 3 VMs as follows:
node1 - Installed Postgres and etcd here
node2 - Installed Postgres and etcd here
node3 - Installed etcd here
I have setup streaming ...
0
votes
1answer
41 views
postgresql-setup --initdb with custom data directory
I am trying to setup Postgres 12 on Centos 8.
Postgres shall be installed in the default directory i.e. /var/lib/pgsql, however I want the data directory to be in /data/pgsql
I want to use postgresql-...
1
vote
1answer
400 views
How to store user activity that supports undo?
I can track user activity on posts, so that users can see for example "2 updates!". The essence of the strategy I’m using leverages 2 tables:
post_activity:
post_id | user_id | time_created
...
11
votes
3answers
3k views
Why is an OR statement slower than UNION?
Database version: PostgreSQL 12.6
I have a table with 600,000 records.
The table has the columns:
name (varchar)
location_type (int) enum values: (1,2,3)
ancestry (varchar)
Indexes:
ancestry (btree)...
4
votes
1answer
559 views
How am I supposed to update PostgreSQL on Windows, once installed, without manually installing new installers?
I have PostgreSQL 11.5 installed on Windows 10. It was installed with the EnterpriseDB installer, which is the only one which exists these days.
If I go to https://www.postgresql.org/download/windows/...
