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
3
votes
1answer
144 views

Is 'internallength' always useful in custom type definition in Postgres?

In our database we use a custom type to store 3D locations: CREATE TYPE float3d AS ( x real, y real, z real); In many examples I see INTERNALLENGTH used as a parameter with the definition of ...
3
votes
1answer
3k views

Attributes from an hstore-column as separate columns in a view?

Let's assume we have a table where it has two columns, one an ID and one an hstore column, which stores key/value pairs. You could query that column as such: SELECT name, attributes->'device' as ...
3
votes
1answer
135 views

PostgreSQL + query planner + amount of entities

I'm working on a college assignment about how the query planner uses statistics and makes the most optimal query plan. I have read the 57.1. Row Estimation Examples article and I know how PostgreSQL ...
3
votes
1answer
579 views

Preserve order of array elements after join

I have a query that returns a CTE looking like +-----------+-------------+ | node_id | ancestors | |-----------+-------------| | 1 | [] | | 2 | [] | | 3 |...
3
votes
2answers
2k views

Installing a language with Amazon RDS?

I am using an instance of Postgres on RDS and I am trying to run: => create language plperlu; but I get: ERROR: must be superuser to create procedural language "plperlu" If I run: => grant ...
3
votes
1answer
7k views

How can I configure PostgreSQL 9.0 to start automatically under Windows XP?

Yesterday I installed PostgreSQL 9.0 on Windows XP, and after the installation the DB was running. After booting the system this morning, the data base server did not start automatically. How do I ...
3
votes
2answers
134 views

Use PostgreSQL builtin operator <@ after including extension intarray

I added the intarray extension to my PostgreSQL database in order to make use of the - operator, but I still want to use the builtin <@ operator, not intarray's <@ operator. Is there a way to do ...
3
votes
1answer
7k views

Optimizing slow performance of simple SELECT query

I have an app called 'Links' where 1) users congregate in groups and add others, 2) post content for each other in the said groups. Groups are defined by a links_group table in my postgresql 9.6.5 DB, ...
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
0answers
374 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
2answers
201 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
762 views

Combining array_agg and unnest

Given a dataset (with a GIN index on values): key | values ------------- 1 | {4,2,1} 1 | {2,5} 2 | {4,1,3} I want to aggregate the arrays: key | values ------------- 1 | {4,2,1,5} 2 | {4,...
2
votes
4answers
6k views

ERROR: check constraint is violated by some row

I've got the following error: ERROR: check constraint "cc_at_least_one_mapping_needed" is violated by some row Query: ALTER TABLE integrations.tax_aggregates DROP COLUMN IF EXISTS myob_id, ADD ...
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
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
0answers
126 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
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
1answer
259 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
1answer
2k views

Is varchar(x) as fast as `text CHECK ( char_length(x) )`?

In an Twitter exchange, Simon West asks to Brandur, Out of interest, why do use email TEXT CHECK (char_length(email) <= 255) rather than email VARCHAR(255)? Not a pattern I've seen before ...
2
votes
1answer
543 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
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
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
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 ...
2
votes
1answer
489 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
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
695 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
1answer
269 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
858 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
1answer
534 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
680 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
5k views

How to edit `postgresql.conf` with pgAdmin 4?

Same Question as this: How to edit postgresql.conf with pgAdmin 3? but updated for the completely new (total re-write) pgAdmin 4. ➥ How does one edit the configuration files with the pgAdmin 4 app? ...
2
votes
1answer
234 views

PostgreSQL Geo Spatial Query is slow

After given up with MySQL i have tried Elasticsearch and now wan't to see if I could go with PostgreSQL/PostGIS that could allow me to go with PostgreSQL only. I need to fetch records from a Table ...
2
votes
1answer
3k views

unable to start the standby postgres database

Situation I have 2 separate instances. Both running on Ubuntu 14.04 server. Both have installed postgres 9.5 the following way: sudo apt-get install software-properties-common python-software-...
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
265 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
410 views

Safely quoting type names to protect against SQL-injection

How would I quote the type name to protect against SQL Injection. For example, take this SELECT FORMAT('SELECT CAST(%L AS %s);', '5.42', 'int'); ^ ...
2
votes
4answers
206 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
12k 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
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
1answer
208 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
2answers
985 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
966 views

How do you declare a set-returning-function to only be allowed in the FROM clause?

I was just reading this answer by Erwin Brandstetter. Informative as usual, it makes note that (multi-parameter) unnest is only allowed in the FROM clause. I had never tried it in the SELECT clause so ...
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
2answers
1k views

Adding standard set of constraints (rules) to PostGIS `raster` type column

Following on from my related prior question, let me elaborate this topic a bit. raster2pgsql is a raster loader executable that loads GDAL supported raster formats in PostGIS. It has a -C flag ...
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
164 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
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
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
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
534 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 ...