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
1answer
267 views

Exclusion constraint generates: ERROR: operator &&(anyrange,anyrange) is not a member of operator family “range_ops”

How come when I try to create an exclusion constraint with GIST, CREATE TABLE foo ( a tsrange, EXCLUDE (a WITH &&) ); ERROR: operator &&(anyrange,anyrange) is not a member of ...
2
votes
2answers
1k views

Reset the sequence of a serial PK with dynamic SQL

How can I dynamically reference a sequence name (as below) using a combination of strings and DECLARE variables? Is the code below proper or is there another method of doing this? My goal is to do ...
2
votes
1answer
267 views

Dealing with out-of-range values on a PARTITION BY RANGE

I have a table for storing events which is partitioned on a timestamptz column by month using PARTITION BY RANGE. There are 5 partitions at the moment, containing a span of a month each, starting ...
2
votes
0answers
298 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
2answers
996 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
3answers
1k views

Intentionally corrupting an index in PostgreSQL

I need to know if there is any method for intentionally corrupting an index in PostgreSQL. I have been searching for it for a long time but never seen anyone mentioning such an act.
2
votes
2answers
685 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
2answers
214 views

How can the concatenatation of a text and a NULL value return a non-null result?

Assert 1 The concatenation operator || can concatenate any string type values, returning text. In Postgres, every type has a text representation and can be cast to text. Consequently, quoting the ...
2
votes
1answer
3k views

ERROR: “sql ” is not a known variable

This must one of the dumbest questions I've ever asked here, but there must something really sick hiding in my SQL script that is blocking it from running. I'm invoking the cobertura.sql file using ...
2
votes
1answer
3k views

Extract JSON array of numbers from JSON array of objects

I have a table containing a json array of objects similar to this: id | record ____________________ name1 | [{"a":0, "b":x}, {"a":1, "b":y}, {"a":2, "b":z}, ...] I would like to get a table ...
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
2answers
3k views

Select rows with repeat data within certain timeframe

As a follow-up question to my previous question on difficult row categorisation, I have another problem which I'm sure will be easily solvable. After this I plan on reading a good book or two on ...
2
votes
2answers
7k views

pg_upgrade doesn't find config file

I'm trying to upgrade PostgreSQL on Ubuntu Server from 9.6 to 10. This is the first time I'm doing this. The server doesn't contain any useful data yet but I want to make sure I can do this properly ...
2
votes
2answers
684 views

How to optimize a table for a very high-frequency updates?

I have a table that contains a list of tasks that need to be run periodically: applaudience=> \d+ maintenance_task Table "...
2
votes
1answer
354 views

Can I change CITEXT columns to VARCHAR without encountering any surprise difficulties?

I went overboard on CITEXT columns on a particularly large table in my application. I would like to back some of these out as it's confusing how to trigger lookups on the desired indexes. My question ...
2
votes
4answers
207 views

Make IN clause behave like AND clause

I've asked the same question on stackoverlow, but got no answer, so that's why I'm asking it here (I've read that simple sql questions should be asked at SO, but I have no choice). Suppose I have 3 ...
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
3answers
81 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
1answer
13k 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
682 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
12k views

Show only duplicate values

How to select rows with duplicate values (non-distinct rows) and summarize with a count of the number of duplicate values in order of the most frequent duplicates? Given a table of: person_ name_ ...
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
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
1answer
2k views

Using pg_prewarm to load X number of latest rows into cache

We have a large query that is extra slow when customers "run it the first time, early in the morning..." So, I found pg_prewarm that I would like to use load into PG's buffer cache a certain amount ...
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
376 views

ORA-00937 when calculating a sub-value in an aggregate select statement

The problem described below applies to Oracle 11g databases. I also tested the statements on MySQL and PostgreSQL with no problems of the kind mentioned in this question. I want to use an aggregate-...
2
votes
2answers
2k views

Can I configure the PostgreSQL locale so that it ignores umlauts in ILIKE queries?

I'm using PostgreSQL to look for city names matching a user-entered string. I'd like to be able to match a row with the name "München" (German for "Munich") if the user types "Munchen". In other words,...
2
votes
0answers
379 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
878 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
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
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
863 views

Install PostgreSQL using source code or using apt-get?

I want to install PostgreSQL on a server that runs Ubuntu 14.04.2 LTS. I understand that I have two options: Install PostgreSQL using the source code distribution Install PostgreSQL using apt-get ...
2
votes
2answers
3k views

How to get OS user in Postgres?

The title says it all. In Oracle, we can easily get the OS user using: select sys_context( 'userenv', 'os_user' ) from dual; I usually use that for logging purposes (know who inserted/updated what ...
2
votes
1answer
2k views

Decrease WAL file size for backup

I'm setting up a backup strategy based on WAL files archiving and transmission via rsync. As DB activity will propably be very low (less than 100 inserts/updates/deletes per day), at least in the ...
2
votes
1answer
209 views

Slow Query with LIMIT

We have this query: SELECT "messages".* FROM "messages" WHERE "messages"."account_id" = 1 AND "messages"."status" = 'unread' AND (created_at >= '2014-04-24 01:00:00.000000') ORDER BY id DESC It ...
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 ...
2
votes
1answer
550 views

Was my Postgres cluster built with the ICU libraries available for Postgres 10 and later?

I wonder if my installation of Postgres 10 Beta 2 has been built to include the new International Components for Unicode (ICU) collations. For background info, see More robust collations with ICU ...
2
votes
1answer
579 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
2answers
38 views

History table design for deletions in PG 11.5

I've got a question about the design of a history table in Postgres. The setup is that I've got a table that holds a needs list. A location recalculates in-demand items every five minutes, and pushes ...
2
votes
1answer
4k views

Wal segment still exists on master, but logs on slave and master say it's been removed

I'm trying to set up streaming replication on Postgresql 9.5 The master and slave are configured as below and WAL files are accumulating on the master. However, something is wrong as I get complaints ...
2
votes
0answers
1k views

Cannot drop a Postgres table

I want to drop a few tables in my postgresql database. I am able to drop a few tables but there are 3 tables which I am unable to drop. I need to drop a few tables in my Postgres DB. I tried the ...
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
1answer
273 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
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
1answer
1k views

PostgreSQL indexing on bit string

What is the best way to create an index on a bit string column? Let's say I had the column of type bit(4) and I wanted to search for all entries that had had a specific bit set. So if I had the ...
2
votes
2answers
2k 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
551 views

Search on concatenate columns

I have this : CREATE TABLE users ( first_name varchar(60) NOT NULL, last_name varchar(60) NOT NULL ); INSERT INTO users (first_name, last_name) VALUES ('Sylvia ', 'Hoeks'), ('...
2
votes
1answer
225 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
724 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
5k 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 ...