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

Find all items where the 2 latest statuses meet condition

I have a table in a PostgreSQL database like this: ID|Item-FK |timestamp|status ============================= 1 | 123 | ... | EXPIRED 2 | 123 | ... | PENDING ... I want to query all ...
2
votes
2answers
1k views

Creating a unique constraint on a PostGIS 'raster' type column

I am using the following command to add constraints to one of the raster image in PostGIS-2.1.3 (PostgreSQL-9.1.14). ALTER TABLE schema1.table1 ADD CONSTRAINT enforce_scalex_rast unique (rast); But ...
2
votes
1answer
189 views

Does AWS ever `vacuumlo` PostgreSQL RDS instances?

Link to the long story happened before (and reason to have) this question. Does AWS ever vacuumlo PostgreSQL RDS instances?
2
votes
1answer
7k views

Join multiple PostgreSQL tables that have both common and individual columns

Processing speed matters not for my needs. What I want to do is meant to be done once and then stay that way. It should be an automated process though, which can be performed by sending SQL commands ...
2
votes
0answers
141 views

PostgreSQL database re-design considerations

We have a 2TB PostgreSQL database, which contains two groups of tables that account for about 90% of the storage used. These two groups can be loosely described as 2 big tables partitioned, the first ...
2
votes
1answer
732 views

Operator “~<~” uses varchar_pattern_ops index while normal ORDER BY clause doesn't?

Let's say I have a table with 1 000 000 records. Structure of table is: create table individual ( id serial primary key, surname varchar(128), "name" varchar(128), patronymic varchar(...
2
votes
1answer
2k views

Is there any way to save the execution plan in a file in PostgreSQL?

By saving a plan in a file, I don't mean to view it, but force the optimizer to use the saved plan to execute some query. For example, if the optimizer chooses to do a hash join for a two-table ...
2
votes
0answers
326 views

Debug PostgreSQL lock count with check_postgres.pl

We use check_postgres.pl to monitor our database. We use this to check the count of the locks: https://bucardo.org/check_postgres/check_postgres.pl.html#locks We often see more than 150 locks. The ...
2
votes
1answer
535 views

Query takes hours

Up until now all queries in my PostgreSQL application were fast. During the last days, sometimes a query takes several hours. Index are healthy, because after dump, restore, vacuum -z, it is still the ...
2
votes
2answers
116 views

How can one optimize a query involving a sort and a LIKE?

Here is the query: EXPLAIN ANALYZE SELECT * FROM "Users" AS "User" WHERE "User"."name" LIKE 'garr%' AND "User"."id" NOT IN (2449214) AND "User"."hellbanned" IS NULL AND "User"."...
2
votes
0answers
424 views

ts_headline() not highlighting properly for <-> operator

ts_headline() does not seem to highlight the input text in a way that respects the <-> operator. The following query: SELECT ts_headline('English','This Commercial Bank does not have any ...
2
votes
1answer
787 views

EXECUTE within function not using index?

I have an accounts table with ~200k rows and an index for these columns: account_type_id BIGINT , member_id BIGINT , external_id VARCHAR(64) CREATE INDEX account_full_qualifiers_idx ON ...
2
votes
1answer
2k views

Create database on new partition

I use postgresql 9.1, on ubuntu 12.04. I had installed the depot package. I have added a new partition to my system, and I would like to create a postgresql database on this partition ( which will be ...
2
votes
1answer
720 views

trigger can't access column loop

SSCCE: The following script: $ cat test.sql CREATE TABLE public.foo ( loop INTEGER ); CREATE OR REPLACE FUNCTION public.foo_fun(loop INTEGER) RETURNS BOOLEAN AS $$ SELECT TRUE; $$ LANGUAGE SQL; ...
2
votes
1answer
1k views

How to set 'timing on' permanently in PostgreSQL?

I work with PostgreSQL in the command line interface. I would like to activate a \timing command permanently for every database in the cluster so every time I connect to a database - this parameter is ...
2
votes
2answers
1k views

Count where previous record exists in either of two tables

I'm trying to COUNT records on one table where both the counted records and their "previous" records exist either in the same table or on another. The table that I'm sampling has a composite PRIMARY ...
2
votes
2answers
691 views

UPDATE table with another table data

I want to update the table car_check field fencing.(total 40 rows).I also use postgis function. I think it's a little difficult.Any suggestions? The code is the first carid example. UPDATE car_check ...
2
votes
1answer
2k views

Reset search_path to the global, cluster default

Say I permanently alter my database's search_path like this: ALTER DATABASE my_db SET search_path TO "$user",public,other_schema; How can I reset it back to whatever the default that a pristine, ...
2
votes
2answers
229 views

Validation of availability for a new order

I have a management application of sales, stock and payment on a warehouse whole saler from a web interface. In particular, when a order is effectuated it must create a line corresponding to each ...
2
votes
1answer
2k views

How do I resolve Postgresql error, “could not determine which collation to use for string comparison”?

When ever I run this SQL script I get: ERROR: could not determine which collation to use for string comparison This is what is on line 34: CREATE TEMPORARY TABLE weight_options AS SELECT ...
2
votes
1answer
3k views

How do I access a old saved tablespace after reinstalling postgres?

So a lot of things might have gone wrong here. I first installed postgres 9.3 on mac 9.1 via homebrew to manage some data. I am using postgres.app. Turns out my harddrive was too small so I decided to ...
2
votes
1answer
2k views

Conditional WHERE in plpgsql function

I'm writing function in plpgsql and stuck with following question: my function has parameter service_id_input and query. I want to create additional clause in WHERE section if this variable is not ...
2
votes
1answer
615 views

Efficiently return two aggregated arrays from a m:n table

I have a table for a many-to-many relationship over a users table to represent a follow relationship between users: CREATE TABLE users ( id text PRIMARY KEY, username text NOT NULL ); CREATE ...
2
votes
1answer
14k views

Using dynamic column names in PostgreSQL

I am using PostgreSSQL 8.3. I want to execute this query: select COALESCE(col_fr,col,'Unknown') from my_table where id = 'abc'; Twist in the story is column name colum_fr should be generated ...
2
votes
1answer
1k views

PostgreSQL parses string literal as record before calling cast

In my PostgreSQL 9.6 database, I have a composite type, let's call that type widget. I have a PL/pgSQL* function called text_to_widget that parses textual representations of widgets to widget values, ...
2
votes
1answer
69 views

Using a trigger function to constrain values according to those already in the table (postgresql)

I'm just learning SQL and I can't seem to find out how to do this. The table is (id, subject, year, grade). I'm supposed to reject any inserts or updates for a student if he has scored an F before ...
2
votes
1answer
4k views

How to insert into view through GUI in pgAdmin?

I have some updateable views for inserting the data. The reason is not just "security layer", inserting into some 10 tables directly would be a pain without the view with INSTEAD OF trigger. In ...
2
votes
2answers
779 views

Should I create an index for non key columns?

I have a table in a PostGreSql database defined as following: CREATE TABLE public."MATCH"( "ITEM_A_ID" bigint DEFAULT 0, "ITEM_B_ID" bigint DEFAULT 0, "OWNER_A_ID" bigint DEFAULT 0, "...
2
votes
2answers
3k views

Does Postgresql support different transaction isolation per connection?

I just discovered that H2 doesn't support concurrent connections using different transaction isolation levels. Meaning, changing the transaction isolation of one connection affects all other ...
2
votes
1answer
1k views

Search entire database(210 tables) for a unique Id (PK) and get the table name

Rewritten Question: I need to look through an entire database, 210 tables for a unique ID that is a number. I have limited access to my database through things called nodes (Visual programming.) ...
2
votes
1answer
745 views

Clarification on UNION ALL of JSONB_EACH result

This function used to merge 2 JSONB and delete certain key when the value is null, refer to this question. CREATE FUNCTION jsonb_merge(JSONB, JSONB) RETURNS JSONB AS $$ WITH json_union AS ( ...
2
votes
1answer
331 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 ...
2
votes
1answer
293 views

whitelist for allowed user queries in postgres

Looking for a way to limited a user to a specific set of queries (i.e. a whitelist). I currently need this as an extra security measure for a client app. Is there a way to enable a table of "user ...
2
votes
1answer
581 views

PostgreSQL replication not working after base backup

Last week we had an incident with our master PostgreSQL server instance which got totally thrashed so, we had to switch to our slave as our one and only DB instance. After the initial chaos was ...
2
votes
1answer
607 views

Creating a summary table for an averaging query

In relation to another post of mine regarding slow performance for an averaging query on a table with ~30 million rows, I tried creating a summary table to store the averages. Firstly, I have a table ...
2
votes
1answer
2k views

How to run ALTER TABLE on a list of tables sequentially in a function

I have a schema (called import) where data are imported as tables, and a function that processes them all. After that I want to move these tables away in another schema (called data_archive). The ...
1
vote
1answer
69 views

Does the Postgres 9.4.9 update fix MultiXactId “does no longer exist” in addition to “has not been created yet” errors?

Postgres 9.4.9 has a fix in it's release notes which reads (emphasis mine): Prevent possible failure when vacuuming multixact IDs in an installation that has been pg_upgrade'd from pre-9.3 (Andrew ...
1
vote
1answer
3k views

Increased performance from single statement, multiple row inserts?

I'm currently obsessing over bulk inserts via prepared statements with libpqxx. I am far from a database expert and even further when it comes to Postgres, but just thinking superficially, it doesn't ...
1
vote
0answers
555 views

Why is the Postgresql planner choosing the slower merge join (instead of hash join)

I'm using Postgres 9.6 on AWS I've run into an issue where depending on the data range for the query the planner will chose to do merge joins (requiring slower sort operations). When the date ...
1
vote
0answers
36 views

When does the PostgreSQL 12 CTE materialized option provide an optimization advantage?

I have been reading about PostgreSQL's new CTE feature - the MATERIALIZED or NOT MATERIALIZED keyword - which can offer additional optimization opportunities under certain circumstances, provided that ...
1
vote
1answer
51 views

How to filter multiple many to many relationship based on multiple tags?

I've a scheme of two main tables: problem and tag and a relation (which is a many to many connector) table: problem_tags which excerpt of them is like: Problem table: ----+---------------------------...
1
vote
1answer
30 views

Equal sized timeseries for each id

What I'm trying to implement is to generate equal-sized time series for each customer. One some days he may have some activity hence some retention value indicator, on other dates he may not hence 0. ...
1
vote
2answers
114 views

Will my query continue to execute after I shut off my computer

I am running a very expensive query that I executed via pgAdmin 4 which is installed on my laptop. I am executing the query on a cloud server. The message that I see is 'Waiting for query execution ...
1
vote
1answer
15 views

Return previous running total when value is null in a time series

This is a continuation of a previous question, where table definition and sample data can be found. (Huge thanks to @Erwin Brandstetter for the help there). All of this is being done on a PostgreSQL ...
1
vote
1answer
26 views

What type of rows does `SELECT my_table FROM my_table` return?

I sometimes encounter this SELECT statement (in JSON-related answers on stackexchanges): SELECT my_table FROM my_table; It seems to turn a one-column result, with multiple rows. What would be the ...
1
vote
1answer
33 views

How do I optimize my transaction level running balances cartesian join?

This is a continuation of an question found here: Generate multiple running totals with GROUP BY day This is a continuation of a previous question, where table definition and sample data can be ...
1
vote
1answer
591 views

Chunking data into a PostgreSQL bytea column?

I have column of type bytea in PostgreSQL database. If I update a row, is it possible to chunk the data into the bytea column in several small steps? The size of one bytea entry will be mostly below ...
1
vote
2answers
6k views

Use array expression from subquery in ANY() condition

I have a relatively complicated query with a subquery fetching an array like so: ... ARRAY(SELECT category_id FROM category_schedule_con con WHERE s.id = con.schedule_id ORDER BY category_id) ...
1
vote
1answer
690 views

Index by subkey in jsonb

I have a table payment with a jsonb column application. It contains payments and the total amount for one or more tours. For example, a parent can pay 1000 $ for all his family, e.g. 4 children and ...
1
vote
2answers
151 views

Difficult categorising of rows

First off, I'm not a DBA at all, I'm more of a front-end guy, but I'm currently hitting a brick wall and can't figure out how to tackle it. A database I am working on has a table with several columns. ...

1
19 20
21
22 23
25