Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [postgresql]

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

0
votes
1answer
37 views

How to unnest and display elements of a JSON array?

i have this table : And i want display all the names in the table like this : Also i want to list the number of gender each genre : Desired output:
0
votes
3answers
34 views

How safe are PostgreSQL triggers against attacks?

in my PostgreSQL database I setup some triggers to prevent DELETE and EDIT operations (the triggers make sure that these operations lead to nothing). However, I'm wondering how safe these triggers are?...
0
votes
2answers
18 views

where does pg_dumpall store *.bak file?

I want to dump a database. and I use: pg_dumpall by using this code, data which has been stored on data base is scrolling in the terminal and it says: PostgreSQL database dump complete PostgreSQL ...
0
votes
1answer
18 views

Add foreign key constraint to an existing table column, referencing a column in a newly created table

Let's say I have one table defined as this CREATE TABLE sales ( agent_id integer references agents(agent_id), sale_date date, amout numeric(10,2) ); Then an ETL process fills ...
-2
votes
1answer
60 views

What are the numbers in the output of an INSERT statement, and why is the first one zero? [closed]

When you INSERT, you'll get back something like # CREATE TABLE foo ( a int ); # INSERT INTO foo VALUES (1),(2); INSERT 0 2 What is the first and second number after the INSERT (the 0 and the 2)?
0
votes
1answer
24 views

Use Common Table Expression (CTE) to reduce locks (max_locks_per_transaction)

I am getting "ERROR: out of shared memory" presumably because my plpgsql function drops/creates a lot of temporary tables. Searching for a solution to the issue, I found this answer that says: ...
-1
votes
1answer
22 views

SELECT Every Parent Table Record and INSET Multiple Record in Child Table Against the Parent_Id

Suppose I have two tables name company and screensavers as given below: **Company:** id (pk auto inc) name (varchar) **ScereenSavers:** id (pk auto inc) name (varchar) path (varchar) company_id (int)...
1
vote
1answer
27 views

serializability ordering after one transaction finishes in postgres

Suppose, I have a table product with integer column price. Here are two transactions that I begin at the same time (almost): T1: BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; UPDATE "product" ...
0
votes
0answers
11 views

aurora postgresql logs

We are in the process of sending logs of Aurora PostgreSQL to a SIEM log management solution. However, there doesn't seem to be a option to publish Aurora PostgreSQL to Cloudwatch log group. Is there ...
1
vote
1answer
21 views

SQL query to replace attribute value with another attribute value- postgre

I have a table on PostgreSQL as below: id | user_id | attribute_key | attribute_value I have data in the table as below: 1 | 1 | first_name | Jesus 2 | 1 | last_name | Christ 3 | 1 | Email ...
0
votes
1answer
33 views

PostgreSQL getting the latest value

I have started working on a postgreSQL database. There is a table keeping the record of stocks and every time a record is updated, a copy of the original row needs to be kept, with a flag added to say ...
0
votes
0answers
30 views

NpgsqlException: ERROR: 22P02: invalid input syntax for type json [migrated]

I have the following JSON string: {"packedRecipeType":"DynamicCharacterAvatar","name":"Character","race":"HumanFemale","dna":[{"dnaType":"DynamicUMADna","dnaTypeHash":1932961400,"packedDna":"{\"...
0
votes
1answer
58 views

Error: There is a column named in table “*SELECT*”, but it cannot be referenced from this part of the query

Assume we have a simple table, CREATE TABLE foo ( a int PRIMARY KEY, b int ); I'm wanting to write a complex query inside and refer to that query INSERT INTO foo SELECT 5 AS z ON CONFLICT (a) DO ...
3
votes
1answer
64 views

How can I specify the last parameters in a function call and let the others default to the DEFAULT in the declaration?

I have a function with 5 parameters, arguments p1, and p2 are mandatory arguments p3, p4, and p5 are optional and have a DEFAULT set The function looks like this, CREATE FUNCTION test_func( p1 ...
2
votes
1answer
41 views

Why can't temporary tables reference regular (non-temp) tables in PostgreSQL?

Why can't temporary tables references non-temp tables in PostgreSQL? When I run the following CREATE TABLE foo ( a int PRIMARY KEY ); CREATE TEMP TABLE bar ( a int REFERENCES foo ); I get, ERROR:...
1
vote
0answers
47 views

UNIQUE constraint encompassing all the columns of a table - is it bad practice?

Is it considered bad practice to have a UNIQUE constraint applying to all columns on a table, essentially enforcing unique rows? My use case is to store tokens from textual message from various ...
2
votes
2answers
34 views

How to express an empty relation (or table) in PostgreSQL?

Is there a way to express an empty relation in SQL without actually creating an empty table? For testing purposes, I want to find a way to test something like: SELECT count(*) FROM (...) ...
0
votes
1answer
15 views

How can I import an old pgdump into an updated schema?

I'm DB newb doing a software upgrade, and my predecessor's original migration plan may not have been as well thought out as I thought... The Plan was to use our backup, created via pg_dump -U derp -...
0
votes
0answers
14 views

How to restore Postgres cluster from pg_probackup archive

I'm trying to configure continuous backup solution using pg_probackup tool. So far I did the following: Configured archive_command according to the docs Created backup using pg_probackup backup ...
1
vote
1answer
58 views

What are some consequences of putting quotes in a WHERE clause for an integer column?

I have a table with a column some_column of data type integer. I've noticed that my PostgreSQL 10.6 instance is smart enough to interpret both of the queries below as same: select * from my_table ...
0
votes
1answer
31 views

Copy large object field into remote PostgreSQL DB [closed]

We have configured postgres_fdw to move data from a DB to remote DB. It works fine but the large object field's data in source db not getting copied to remote DB. This is the statement used to copy: ...
3
votes
2answers
46 views

Breaking out of a recursive query in Postgres 11

I'm having some trouble getting the correct data out of a tree structure, where I only want the first nodes in a branch matching criteria to be the result of the query. I have this table of domains: ...
0
votes
1answer
29 views

How to fetch the string from the column of table in postgresql?

I have the following table Userid District_code 1 05 1 03 2 03 2 04 3 06 and want to get this output: userid=1 dist_code(05,03)
0
votes
1answer
23 views

How to store token in postgresql?

I have a column in table with type character varying here is the query: update logins set access_token='l38r8cfjm31b38ye91yjn1956vr3n74tc5sc0btholj0iain77' and uuid='123456789' where username = '...
1
vote
0answers
53 views

postgres boolean function and check constraint [duplicate]

I been using postgres for a while. Mostly simple stuff. Now trying to implement a small perl function to check for a single valid email address. My employees table should have either a single valid ...
1
vote
1answer
16 views

Get the combined row count by month from 3 tables

Using Postgres 10+, I have three tables with similar structures, where I'm trying to get a count of rows grouped by months. I have a functional query working for two tables, as below: CREATE TABLE ...
1
vote
3answers
270 views

PostgreSQL 11 error: column p.proisagg does not exist

Using phpPgAdmin v5.6 and PostgreSQL v11.2 on CentOS v7, when I try to access the Functions tab within the public schema, I get the following error: ERROR: column p.proisagg does not exist LINE 18: ...
2
votes
1answer
51 views

How to GRANT SELECT by default to B, on tables created by A?

Background In my PostgreSQL instance (say with one unique database constituted of one table only), I have 2 users: user_A, who can create and populate my_table, user_B, who should only be able to ...
0
votes
0answers
164 views

psql: FATAL: the database system is starting up

I'm running into this message but not able to make heads or tails out of why it's throwing this: -bash-4.2$ /usr/pgsql-10/bin/postgres -D /data/patroni --config-file=/data/patroni/postgresql.conf ...
0
votes
1answer
14 views

Temboard: when monitoring a server modifying postgresql.conf on the server is not effective?

I've installed the Temboard tool (http://temboard.io/) to start monitoring my postgresql server. I've noticed that when I modify the postgresql.conf on the server and I restart it, the change is not ...
1
vote
1answer
43 views

Is this database fragmentation or something else? What can be done?

I have the table below in PostgreSQL (automagically generated with insertions using Marten): create table mt_doc_order ( id uuid ...
0
votes
1answer
11 views

How to join two tables in postgresql

First table Complaint_id Status Reopen 127493 5 1 127494 5 0 Second table Parent_id Currentstatus Reopen datetime usertype 127493 0 0 2019-04-08 ...
0
votes
1answer
10 views

How to install repmgr 4.2 (ubuntu)

apt-get install postgresql-10-repmgr give me repmgr 4.3. However, my primary server is still on 4.2. How do I install repmgr version 4.2? Thanks in advance!
-1
votes
1answer
24 views

How can I get the max value for each column?

If data of table like this, [co1:varchar][col2:varchar][col3:varchar][col4:timestamp] And, Follow is data example. 224 / 755 / 542 / 2019-05-23 10:12:23 230 / 741 / 530 / 2019-05-23 10:12:23 221 / ...
0
votes
1answer
18 views

System-wide startup file for Postgres 10 on Ubuntu 18.04

When I log into postgres under my personal shell account, my .psqlrc file runs a series of meta commands to set up what is for me an optimum working environment. I'd like to set that same environment ...
0
votes
1answer
12 views

Join on unnested integer array column and count occurrences in array

I am trying to join two tables after I have unnested one of them. The tables are: CREATE TABLE teachers (id SERIAL, first_name VARCHAR(60), last_name VARCHAR(60)); CREATE TABLE training_modules (id ...
0
votes
1answer
15 views

PostgreSQL Trigger to override insert on table with inheritance

We have a table documents that is partitioned by time into child tables like documents_2019_01, documents_2019_02. We do this using a BEFORE trigger on the parent documents table to insert into the ...
-1
votes
1answer
24 views

Design schema to handle different data types (avoiding EVA anti-pattern)

So I have a table that stores ML model metadata. I have a table that holds my model. I need to also include hyper parameter information (epochs, batch_size, weight_decay, classes etc.). I don't want ...
0
votes
0answers
46 views

Store large amounts of text is Postgres

I have a large text corpus that I am storing in Postgres. A table articles has all of the metadata for each article, about 19 million rows. It's primary key is articles.doc_id. Another table (texts) ...
0
votes
1answer
40 views

Is it better to install a very large Postgres database (billions of rows) in a real or a virtual machine? [closed]

From my common sense, my physical machine hosting a very large Postgres database (for big data) should be real rather than virtual or container. Some people say it is better to install on a virtual ...
1
vote
1answer
36 views

Index on JSONB not improving query speed

table structure Column | Type | Collation | Nullable | Default ------------+--------------------------------+-----------+----------+------------------------...
0
votes
2answers
28 views

how to update a column from another database?

I would like to update a field that has been delete from the backup database. how can I update the field from an other database
1
vote
2answers
20 views

postgresql 9.* - user cannot SELECT from VIEW inside a SCHEMA as DEFAULT PRIVILEGE

hope you can help me. I have 3 users First is me, the db admin. I run the GRANT queries, I can do anything. (name doesn't matter now) Second is the data manager user. = manager_u Third is the data ...
1
vote
1answer
33 views

Subquery in where clause forces seq scan instead of using trigram index

I have a query that is supposed to use a bunch of trigram indexes to speed up ILIKE clauses. This works as long as there isn't a subquery in one of the where terms. I've simplified my query and can ...
2
votes
0answers
17 views

Query plan (JSON format) in pgAdmin not showing completely [duplicate]

I would like to use the tool Postgres Explain Viewer (PEV). For it I need to paste the execution plan in json format. If I try to get the execution plan in pgAdmin with this query: EXPLAIN (ANALYZE, ...
0
votes
1answer
36 views

Speed up slow postgres query with indices (~5 minutes)

I have roughly 100 million rows in a table called 'bets' and a couple million in 'games'. The problem is that even with indices particular queries run very slow. The tables are structured as so: ...
-2
votes
0answers
23 views

move data directory for pgsql-9.2 on rhel 7

I am installing database on VM which are very restricted about outside access. Firewall does not allow yum to get packages from outside. I have installed the pgsql-9.2 (only available) on rhel7 VM. ...
1
vote
1answer
12 views

pgsql Return Partial Text Field Ending In A Whole Word

I found this PHP code that returns a sub-string ending with a whole word (IE separated by a space): if ( strlen( $body ) < $maxlength ) return $body; $body = substr( $body , 0 , $maxlength ); $...
0
votes
0answers
34 views

How to set the wal_level in AWS RDS Postgresql?

How do I know which wal_level my AWS RDS Postgres 11.1 database has? I checked 'parameter groups', but was unable to find the wal_level. Once found, how do I change it?
0
votes
1answer
31 views

Postgres role without attribute Login

I am a bit confused by the role attribute LOGIN | NONLOGIN, where postgres docs says: These clauses determine whether a role is allowed to log in; that is, whether the role can be given as the ...