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
33 views

Foreign keys to prevent “orphan” file rows in a database for a scenario involving projects, files and downloads

In a system that monitors a file mirror by regularly downloading a file, I want to store a history of the results. So I have three tables: ┌──────────┐ ┌──────────────────┐ ┌────────┐ │projects │ │...
0
votes
1answer
18 views

How to run multiple ST_DWITHINs/ ST_INTERSECTS within a single WHERE clause

PostgreSQL 11, on Windows 10. I'm working with a post-gis enabled database. I want to UPDATE a large polygon table (100,000+ rows) WHERE the geometry of that host table intersects the geometry of ...
2
votes
2answers
159 views

Should I split timestamp parts into separate columns?

I am building a PostgreSQL database and I have created a timestamp table, where the primary key is the timestamp itself (e.g. id: Fri Apr 13 2018 15:00:19). The database is supposed to be later ...
0
votes
1answer
24 views

How to optimize query based on explain analyze in PostgreSQL?

I am running the following query via EXPLAIN ANALYZE but it is taking a bit of a long time to run. Based on the output of EXPLAIN ANALYZE, what are some things I can do to my table to make this query ...
1
vote
1answer
42 views

Find multiple rows that share one field value but have one unique value

I have tables cards, decks, and a join table card_decks. I want to find the count of a unique set of pairs of cards where the a given attribute is true. I feel like I'm close but there is another step ...
0
votes
1answer
21 views

How to restore output from pg_dump into a new table name

I am dumping a large postgres table (~15m records) like this: pg_dump -h myserver -U my_user --table=mytable -Fc -Z 9 --file mytable.dump mydb The above creates a mytable.dump file. I now want to ...
0
votes
1answer
31 views

Postgresql running total

I have a bunch on time entries, they have entre_dates and hours associated with them like so. drop table if exists data cascade; create table time_table (id int, entry_date date, hours numeric); ...
0
votes
1answer
19 views

PostgreSQL cluster has two primary databases after network glitch

My test environment had network glitch over the weekend and I ended up having two Primary databases up and running in the cluster. ID | Name | Role | Status | ...
2
votes
1answer
17 views

Determine if columns in postgres table are inherited

I want to be able to list out the columns in a child table and see which ones are inherited from the parent table and which ones are defined on the child table. SELECT column_name, inheritied FROM ...
0
votes
1answer
66 views

Having a subquery somehow do the same group by as the outer query

i'm pretty decent with SQL but it's not my main experience of expertise. A colleague asked me a stumper of a question the other day. I was wondering if you guys could provide assistance. Basically we'...
0
votes
0answers
23 views

How to get record by date from 1 table and update other table in Postgresql?

I have two tables. In one table (order_product) I have multiple records by date and in the other table (Transfer_product) also multiple record by date. order_product table have correct record. I want ...
0
votes
1answer
14 views

Postgres : Select items for an interval from the max date in store

I have a database with items looking like this : id , item_id , date_item , price 0 , 3232 , 2019-04-22, 10 0 , 3232 , 2019-04-23, 4 0 , 3232 , 2019-04-24, 1 0 , 3232 , 2019-04-...
0
votes
1answer
22 views

Run a count query for every row ID in results

I'm trying to count the number of cars at each parking lot in my app, along with some other info. But, I'm not too sure how to approach this. Tables I have 3 tables. lot - this table contains ...
0
votes
1answer
37 views

PostgreSQL, Stored Procedures, Recursive call Tree structure

I'm faced with a problem.I have a ternary structured data. The relation is from child to parent, more specific a child has an parent_id. A parent shouldn't have more than 3 children. When 4th child ...
0
votes
1answer
17 views

PSQL incomplete backup : how to debug

I've been doing backups of my database " screen ". after connecting to PSQL and typing \l+ I'm getting (among other things): Name | Owner | Encoding | Collate | Ctype | ...
0
votes
1answer
32 views

postgres: using unnest in multiple rows

I have an array column containing information like {a,b,c,a} {a,b,a,b} {b,a,a,b} I want to run unnest on this array column, get the occurence of of each character. a 2 b 1 c 1 a 2 b 2 b 2 a 2 I ...
0
votes
1answer
41 views

Subquery to return multiple columns or a JSON object

Is there any way for me to return multiple columns from a subquery or at least return a column that is a JSON object of all of the columns I need? This is a working query that works: select r.*, ...
0
votes
0answers
20 views

POSTGRES - handle several unique indexes differently in ON CONFLICT clause

I have a table with several unique indexes. Is it possible to ON CONFLICT UPDATE only on one of them and DO NOTHING on all others, handling with DO NOTHING future unique indexes without going back and ...
1
vote
0answers
40 views

How to encrypt a column using aes-256-gcm encryption in PostgreSQL?

As a database point person, I am required to encrypt SSN numbers wherever they are in a table. We use PostgreSQL. My application counterparts require that we use the AES-256-GCM encryption algorithm. ...
1
vote
1answer
33 views

Recursion on deletion trigger that calls a function

I have the following schema: CREATE FUNCTION myfunc() Returns trigger as $myfunc$ DECLARE someVal integer; BEGIN SELECT Count(*) into someVal FROM MyTable ...
1
vote
1answer
22 views

Postgres Log Table

Is there a table that with the log file information in postgres? I wish to create a view/report of the information in the postgres logs. I'd like to do something like: SELECT * FROM log_table;
2
votes
1answer
151 views

Is there a way to get a count about grouped rows?

I am trying to get a count about a grouped rows. Here is a description of the table of relevance: column_name data_type ------------- --------- id integer title character ...
1
vote
0answers
19 views

Which PgBackRest policy for differential backups in PostgreSQL

I set up pgBackRest and PostgreSQL configuration in two differents servers. I set up with crontab one full backup a week and one differential backup a day. 0 1 * * 0 pgbackrest --type=...
1
vote
1answer
23 views

pg_restoring into a table with a not null column

I'm merging two databases. The newer one has a table with an integer column failed_port which is NOT NULL. The older database does not have this column. So after using pg_dump to dump both ...
0
votes
0answers
33 views

Select JSON elements from nested documnet

I have a JSON document such as: { "type" : "episode", "AssetId" : "urn:episode:ABCDEFG", "_id" : { "$oid" : "12345678" } } Here is the table definition: CREATE TABLE "v22"."assets_exp" ( "_id" ...
1
vote
1answer
15 views

Audit trigger 91plus: setting up with proper role?

One of the solutions available for auditing in PostgreSQL is Audit trigger 91plus. wiki page github page There is a section in the wiki that is confusing to me: Changes by the table owner and ...
1
vote
1answer
56 views

How to specify column positions in insert statements generated by pg_dump?

I need to add a column to a specific position in a table. I'm doing this because I'm merging tables from two different databases: DB1.schema1.table1 has these columns: name_pk | date | type while ...
0
votes
1answer
46 views

What are some technical reasons for not including N-ary multiplication in the SQL standard? [closed]

If I want to add up two values in a query, I can use the + operator. If I want to sum up all values returned by a query, I can use SUM. If I want to multiply two values in a query, I can use the * ...
0
votes
1answer
25 views

Barman: What's the best way to switch from backup_method postgres (streaming) to rsync (ssh)?

My production postgresql server is currently backed up with barman using the backup_method postgres (streaming server). I would like to switch to rsync method (ssh) to benefit from better compression. ...
1
vote
1answer
34 views

In PostgresQL, how do I make a query match exactly one row or fail?

I am making an arbitrary DELETE or SELECT query and I know this should effect or return only one row. How can I ensure that the query doesn't execute and I get some signal that something is wrong when ...
1
vote
1answer
35 views

first and last record in one query

i want to select first and last score from such data WITH t AS ( SELECT 1 user_id, '2019-05-15'::date AS created_at, 4 as score UNION ALL SELECT 1 user_id, '2019-05-13'::date AS created_at, 12 as ...
0
votes
1answer
20 views

Monitoring PostgreSQL backup

I am new to PostgreSQL and have questions regarding the backup procedure. How do I check whether a PostgreSQL database was backed up successfully? And how do I monitor the backup? I have tried the ...
0
votes
0answers
52 views

How to get hit (number of possible results of a query) from a table with more than 100 million records in Postgres

We have a table X in postgres v11, where it has 5 columns like A B C D E. We all columns are indexed and there are all integer values. We have around, 100 milion records, but soon will turn into 800 ...
1
vote
2answers
41 views

Can anything reveal that 2 independant entries were created or updated at the same time?

Imaginary example, simplified to better explain the question. Let's say I have a form with the following fields : user email who did you vote for in 2012 who did you vote for in 2016 When submitted, ...
0
votes
1answer
22 views

Error on PostgreSQL streaming replication

I have the exact same problem as Wal segment still exists on master, but logs on slave and master say it's been removed except that it is in Production ! PostgreSQL 9.4 / Debian Jessie ...
4
votes
1answer
57 views

When is the AS keyword required?

In the following statement (PostgreSQL 11): => SELECT c cost FROM tt; ERROR: syntax error at or near "cost" LINE 1: SELECT c cost FROM tt; I get an error. Adding parenthesis around the field ...
0
votes
0answers
18 views

How to pick one particular table from different databases to single database [duplicate]

I am working with pgadmin and i would like to pick one particular named table from different databases into single single database and to be merged into single table.
0
votes
1answer
20 views

GROUP BY on view queries

PostgreSQL 9.5.16 I'm writing a query which contains several aggregate columns and many ungrouped columns. I've condensed the core logic. SELECT min(table_b.aggregate_column), table_a1_key, ...
0
votes
1answer
21 views

update value in row based on value in another row

I have a table with timestamped data. t_stamp | col1 | col2 | col3 I want to update the value in say col3 based on values in col1 and col2 from the previous row in the table. By previous row I mean ...
0
votes
1answer
23 views

Copying table subset from one database to another with same schema

I am trying to subset of a table from one table to another users(id, name, referrer_id, country_id) this table have self referential association with refferer_id as foreign key to itself. I ...
3
votes
2answers
18 views

How to distinguish free-standing composite types from table composite types in pg_type?

When querying pg_type, I'm interested in identifying free-standing composite types (user-defined) and not those which are created along with tables. Perhaps there is a way to lookup pg_class (with ...
0
votes
0answers
40 views

PostgreSQL logical replication and partition table

We have a large database and we want a copy of the database in our company for faster access. we are going to achieve this copy by logical replication on PostgreSQL 11. But we have a big problem. We ...
0
votes
1answer
20 views

How to update two tables using subquery in postgresql?

I have two tables main table is Complaintid Status Templateid 123 2 135 5 2 142 5 8 143 3 145 5 Second table Parent_complaint_id ...
1
vote
0answers
25 views

Problems with Postgresql and Pacemaker

I have 2 nodes with Postgresql 10 on Centos7, one as master and one as slave, replicating from recovery.conf the content of recovery.conf is: standby_mode = 'on' primary_conninfo = 'host=10.240.49....
0
votes
3answers
76 views

Window Function to get pairs of records and compute the time difference

I'm working with an event dataset as the one in the example below: Event_Type Event_Timestamp Is_Active A 2010-10-01 00:00:00 1 B 2010-10-01 00:00:01 1 A ...
1
vote
2answers
85 views

Why do experienced SQL Server DBA's qualify examples with the default schema dbo? [closed]

In PostgreSQL like in SQL Server we have a default schema under a normal install. In PostgreSQL it's public. CREATE TABLE foo ( int a ); SELECT * FROM foo; Assuming a default SEARCH_PATH (which is ...
1
vote
1answer
26 views

Error in update. I did not find the error. Why is max(C) not found?

(Translated from Portuguese with Google Translate) I created a table and made an update that should update column B equally to the percentage that C represents in relation to the maximum value of ...
0
votes
0answers
17 views

pg_dump can't write to drive C | Windows 10

Problem is just as the title says. pg_dump literally can't write to anywhere on drive C: - the drive windows is installed on. More details: created Temp folder on C: drive modified access - added ...
3
votes
2answers
42 views

Bad performance on recursive query in PostgreSQL

I have bad performance with recursive query in PosgreSQL. psql --version psql (PostgreSQL) 10.7 (Ubuntu 10.7-1.pgdg16.04+1) The idea is to have tree-like structure of comments. So when parent_id is ...
2
votes
3answers
43 views

Conditionally multiply by relation

I have the following postgresql query that takes some time entries and multiplies them by the latest staff rate that occurs before that time entry. Outlined here Multiply by value from related row ...