Questions tagged [postgresql]

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

Filter by
Sorted by
Tagged with
2
votes
3answers
204 views

Postgres heap vs SQL Server clustered index

I am transitioning from SQL Server to Postgres, and one of the biggest things for me to digest is the non-existence of the "clustered key" that sorts the data in Postgres. Can someone share their ...
5
votes
1answer
3k views

What is the data type of the ‘ctid’ system column in Postgres?

The Postgres system columns are documented in Chapter 5. Data Definition > 5.4. System Columns. That page mentions that oid values “are 32-bit quantities”. And that page says the same about ...
0
votes
1answer
558 views

PostgreSQL pg_dump custom file input not dumping data

When I'm using custom format (-Fc) in pg_dump, the file is smaller (42kb), and when I restore it, it only restores the tables and columns, not the data. But when I try to use plain text (-Fp), the ...
0
votes
2answers
30 views

What is the best way to upload custom-parsed data into AWS Aurora PostgreSQL

I have a large (5-10 GB) binary file on AWS S3 that will require custom parsing, probably in python. It is essentially a sequential set of millions of dataframes, all having the same structure. What ...
0
votes
2answers
37 views

psql: could not connect to server: [closed]

I have installed PostgreSQL in Ubuntu, it installed successfully but psql command not run. It shows that psql: could not connect to server: No such file or directory Is the server running ...
-1
votes
1answer
25 views

append the text to postgres crosstab query [closed]

Am using below crosstab query: SELECT * from crosstab('SELECT id,task_id, status_id FROM table ORDER BY 1,2') AS final_result(id character varying(50), task1 int,task2 int,task3 int,task4 ...
1
vote
1answer
38 views

PostgreSQL read only user

We have a PostgreSQL database 'db1', which is having around 500 schemas. I am trying to create a read-only user for this particular PostgreSQL database. I could find the following command for ...
2
votes
1answer
44 views

uncommitted xmin during vacuum, what to do?

Running vacuum on two specific tables in my postgresql 10 database results in the following error: VACUUM (FULL|ANALYZE|FREEZE|) table; ERROR: uncommitted xmin 359569171 from before xid cutoff ...
0
votes
0answers
30 views

Reset all sequences so that they continue after max(id) + 1?

It looks like I messed up a database migration and while all sequences are there, they would start at 1, which triggers errors like: django.db.utils.IntegrityError: duplicate key value violates ...
2
votes
1answer
910 views

Can I trim values on COPY from csv?

I used csvsql to generate CREATE statements, and it looks like it assumed trimmed fields though the data has some spaces padding the field: ERROR: value too long for type character varying(11) ...
1
vote
1answer
333 views

install postgresql in unattended error

I try to install PostgreSQL within myapp.exe (which used advanced installer to build) in unattended mode, I set parameters as below: --mode unattended --superpassword xxxxxxx --prefix c:\programs ...
5
votes
1answer
660 views

How to run pg_dump so that it doesn't slow down other connections?

I am running a daily pg_dump on PostgreSQL 9.3.14 database of approx. 50 GB of size: pg_dump -Fc dbname > file The problem I am having is that while pg_dump runs, which takes several hours, all ...
1
vote
1answer
33 views

Postgres Full Text Search With Short Word Matching

I'm using Postgres' full text search to return results, but it seems any query with 3 characters or fewer returns no results. Is there a way to configure Postgres so that I can query with 2 and 3 ...
0
votes
1answer
346 views

ERROR: could not connect to the publisher: FATAL: no pg_hba.conf entry for replication connection from host

I am trying to create a logical replication between 2 postgres servers. But when I try to create a subscription from the slave to the master I get the error ERROR: could not connect to the ...
0
votes
1answer
26 views

Insert data in multiple tables

I have three tables and I want to insert data into that three tables as per their respective data. Query: with acupressure_point_data as ( insert into acupressure_point(id, url) values('...
0
votes
2answers
29 views

Database Offset & Limit without order by?

To my understanding, in MySQL/Postgres database there is no guarantee for the order of values with a select statement without an order-by clause, thus one should be getting rows arbitrarily. If so, ...
4
votes
2answers
235 views

GIN index ignored when query has many (more than 18) OR statements

I have an issue where the execution is not using a GIN index when going over 18 OR statements. This is my index: CREATE INDEX emailjobs_externalreferences_gin ON vsko_mailer_api_prod.emailjobs USING ...
1
vote
1answer
19 views

Rollback to savepoint with repeatable read isolation

I want to use savepoints to do subtransactions that I can rollback and retry within a transaction. I have the following scenario when using repeatable read isolation on PostgreSQL 11. START ...
28
votes
2answers
21k views

How to wrap long lines when SELECTing SQL text columns?

I'm selecting from a table with long text columns. I'd like to wrap long lines to a maximum line length. From: SELECT * FROM test; test_id | text --------+-----------...
0
votes
1answer
34 views

Postgresql, case, select in when

System PostgreSQL 11.5 (Ubuntu 11.5-0ubuntu0.19.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 8.3.0-6ubuntu1) 8.3.0, 64-bit Case Does select work in case condition? I need return 1 state ...
0
votes
1answer
27 views

Issue migrating oracle package to postgres using ora2pg

I was migrating an oracle(12c) package to postgres(Enterprise db 10.5) using ora2pg. The migration run successfully for most of the functions. However, one function referenced multiple times in the ...
2
votes
3answers
39 views

How do I select by a JSONB array containing at least one element within a range?

I need to model products with properties. Properties need to be dynamic, in the sense that application users can add / delete properties to products. For this reason, I am using a JSONB column ...
0
votes
1answer
23 views

How postgres deals with XIDs and wraparounds?

I was reading the docs about the xid and it's wraparounds. But can't understand how it all works. Normal XIDs are compared using modulo-2^32 arithmetic. This means that for every normal XID, there ...
0
votes
1answer
17 views

GROUP BY after ORDER BY/LIMIT/OFFSEt?

I have two tables events and eventdate, where eventdate has a row event that corresponds to the id column in events. (So an event can have multiple dates). ` Now I am trying to get the unique events ...
6
votes
1answer
1k views

PostgreSQL operator uses index but underlying function does not

I'm attempting to use JSONB with JDBC, which means that I have to avoid any of the operators which use the '?' character (as the PostgreSQL JDBC driver has no escaping for this character). Taking a ...
2
votes
3answers
168 views

Get rows that exist exactly once per day for a given period

I have a case where I want to extract the device ids (DID) that exist once and only once for each day in a certain period. I have tried different methods and partitions but I seem to only be able to ...
0
votes
1answer
42 views

How to find all participants of a chat thread?

In my database I have the following tables: user, post, post_participant, and post_reply, largely as inspiration from this answer. One thing I'm having trouble with figuring out though is how to find ...
2
votes
1answer
59 views

Querying on multiple joined rows

I have a table called visits that is structured like so: +----+--------------+-------------+ | id | name | visitor_id | |----+--------------+-------------+ | 1 | event1 | 1 |...
2
votes
1answer
162 views

Select column values linked to exact set of values in another column

Given the below chats_users table in Postgres 9.5, with integer columns chat_id and user_id, I want to find the chat_id associated with an exact set of user_ids, for example: user_id IN (1, 3) => ...
0
votes
1answer
23 views

how to update PostgreSQL array of jsonb

I have the structure like this (json pretty): [{ "uid": "comment_1", "message": "level 1 - comment 1", "comments": [{ "uid": "subcomment_1", "message": ...
0
votes
1answer
18 views

Barman shows huge variance in WAL size

I'm using barman to take backups from postgresql. This is the result of list-backup: prod 20190831T000005 - Sat Aug 31 00:04:58 2019 - Size: 16.0 GiB - WAL Size: 654.2 MiB prod 20190830T000005 - ...
0
votes
1answer
265 views

postgres - node-postgres client does not return rows with between clause for dates

I have a view created as shown below. CREATE OR REPLACE VIEW aggr.v_param_data AS select date(a.received_timestamp) run_date, a.mId, min(a.param_value) min_value, max(a.param_value) max_value, ...
2
votes
1answer
109 views

Converting a graph database schema to a relational database schema

I currently am using a graph database and am considering moving off of it to a relational database management system like PostgreSQL. My concern is that I have lots of relationships between ...
1
vote
1answer
16 views

ERROR: invalid XML content, when Select unnest(xpath(… on a text column

I have a text column named big_xml in my table which has the following value: <?xml version=1.0 encoding=UTF-8?> <dpid:DpidDatabase xmlns:dpid=http://ddex.net/xml/dpid/11 xmlns:xsi=http://...
0
votes
1answer
17 views

How to migrate external procedure from Oracle Listener to Postgres

Consider that I'm using C or java external procedure called from my Oracle 12.2 Listener (EXTPROC listener parameter). That Oracle database will be migrated to Postgres. My question is how to migrate ...
0
votes
1answer
470 views

Use ENUM in array of objects

Is it possible to setup an ARRAY column with objects/JSON on a TABLE, forcing a parameter of the objects in the ARRAY to adhere to an ENUM, while keeping that object parameter unique? Data examples: ...
-2
votes
1answer
32 views

Best possible design for a database about users, companies and departments [closed]

I have an app with Postgres and two tables: users and companies. A user belongs to a company so a company_id column is present on the users table. Now a company can have multiple departments so the ...
4
votes
2answers
3k views

Postgres cost of large volume of inserts in many tables in a single transaction

What is the additional cost incurred by inserting large volume or rows (millions), in many tables in a single transaction ? Can something be done (tuning parameters) so that the cost of inserting in ...
0
votes
1answer
21 views

Understanding trigger behavior

I'm having trouble with a trigger which I can't get my head around. I have a table called TABLE_A with an AFTER INSERT trigger that executes a procedure. The procedure basically does the following ...
1
vote
1answer
21 views

two-table-over <+> functional relationship: maintaining referential integrity

I have three tables, the last two having a many to one relationship with the first, written here in SQL: create table study ( id bigint primary key, name text ); create table treatment ( ...
0
votes
1answer
29 views

postgresql recovery.conf file location in 11.5

I'm new to postgres. We need to take incremental backups for postgres databases as well as we must be able to perform PITR. I searched a lot on internet and tried to find out any article referencing ...
0
votes
1answer
21 views

grouping rows of common values to create a new 'group id' for each set

I am trying to achieve the below but was not able to so far, any help would be greatly appreciated. I have this data (sorted from a query by id, anchor, date, and time) that I wish to group by common ...
122
votes
13answers
142k views

How do I find PostgreSQL's data directory?

I forgot how I started PostgreSQL the last time (it was months ago) and I don't remember where the data directory is located. The postgres command seems to require the location of the data directory. ...
0
votes
0answers
20 views

Postgres using powershell scripts - where do trigger messages go?

I have a windows (10 pro) powershell script that reads messages from an external server and INSERTS them into a postges (v10.5) database table. The table has an AFTER INSERT trigger defined on on it ...
0
votes
1answer
18 views

Jailer exclude tables to be exported

As seen in this answer I can use jailer in order to export a subset of data into an SQL script. But in my case I also want to exclude some tables completely from being extracted at all especially on ...
0
votes
1answer
9 views

Reporting table size to grafana

I am trying to report partition metric to influxdb so that it can be visualised in grafana. I have partitioned my table using pg_partman. I want to report the size of the default table that is created ...
0
votes
1answer
27 views

Postgres, `where` condition, sort by first column, but I don't want that

I am using PostgreSQL 11.5 on Ubuntu Issue I have sql with joining tables and with where condition, which looks like this. ... where s_product.product_shop_id in ( '237002', '230041', '467173', '...
3
votes
1answer
370 views

Create event trigger as non-superuser in Postgres

Is it possible to give a role or a set of attributes to a non-superuser in postgres that would allow the user to create an EVENT TRIGGER? Simple example of what (obviously) doesn't work in psql: =&...
1
vote
1answer
805 views

How can I control how Pentaho DI formats integers coming from a text file?

I have a simple pipe-delimited text file with integers recorded as a sequence of numbers: 012345|00678 |various|text|fields There are some leading zeros, and some trailing white space, but no ...
24
votes
1answer
8k views

Why is my PostgreSQL ORDER BY case-insensitive?

I have Postgres 9.4.4 running on Debian and I get the following ORDER BY behavior: veure_test=# show LC_COLLATE; lc_collate ------------- en_US.UTF-8 (1 row) veure_test=# SELECT ...