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
2answers
99 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
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
1answer
6k 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 ...
2
votes
1answer
10k 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
0answers
19k views

List of escape sequences for Postgresql string data types

I'm trying to find the documentation of a complete list of escape sequences for string data types in Postgresql. So for example, if you need to escape a quote character inside of a quoted string, you ...
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
221 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
687 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
788 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
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
1answer
181 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
315 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
2answers
975 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
645 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
2answers
675 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
901 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
2answers
184 views

How to install Postgresql on Ubuntu from PPA with data-checksums enabled?

We are currently installing Postgresql from the PostgreSQL apt repository. Is there some way to get "apt-get install postgresql-9.6" to use the --data-checksums option when setting up the cluster? Or ...
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
176 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
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
1answer
4k 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
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
321 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
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
1answer
193 views

What is the SQL Standard method of doing LIMIT OFFSET?

I know LIMIT and OFFSET are pretty common but unstandardized methods to page through a result set. I know there is a standardized method, but I can never find this when I'm going to look it up, The ...
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
362 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
0answers
285 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
148 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
942 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
688 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
2answers
818 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
232 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
7k views

Cascade deletes from multiple tables

I have a fairly standard restaurant model with a restaurant table in a PostgreSQL DB. All restaurants have ratings (which consist of average, vote_count and vote_sum) but in order to avoid repeating ...
2
votes
3answers
73 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
2k views

List MATERIALIZED VIEWs in PostgreSQL that do not have a UNIQUE index

Postgresql 9.4 allows to REFRESH materialized views CONCURRENTLY, if they have a UNIQUE index. I am looking fo a SQL-query that lists all materialized views that DO NOT have such a UNIQUE or PRIMARY ...
2
votes
1answer
57 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
4answers
4k 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
1answer
300 views

Postgres partial index on IS NULL not working

Postgres version Using PostgreSQL 10.3. Table definition CREATE TABLE tickets ( id bigserial primary key, state character varying, closed timestamp ); CREATE INDEX "state_index" ON "tickets"...
2
votes
4answers
201 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 ...
1
vote
2answers
14k views

RETURN value directly from INSERT with RETURNING clause

I have a function that ends with: INSERT INTO configuration_dates ( cols... ) VALUES ( values... ) RETURNING id INTO ret_id; RETURN ret_id; And I would like to remove the ...
1
vote
1answer
754 views

PostgreSQL function parameters JSON to TYPE

I try to connect my NodeJS API to my database. I have created some type of object for the transport. I use LANGUAGE plpgsql. One example : API_CAR: this type exists on my database I receive an ...
1
vote
1answer
838 views

PostgreSQL on RDS: How to bulk import data with FK constraints?

Is there an efficient way to bulk import data into a PostgreSQL 9.4 database on RDS that makes extensive use of foreign key constraints? The standard method of using pg_restore fails because RDS does ...
1
vote
1answer
2k views

Self-join unique pairs

I have a table (players) containing a list of players, I would like to pair up these players in a unique way, so that every player's opponent is unique every round (ie. every time the SELECT query is ...
1
vote
1answer
326 views

Show all variations of 2 characters in postgres

How would I do this in postgres : Lets say I have an x and a y and I want to show all possible combinations : xx yy xy yx How can I make postgres do this for me ?
1
vote
1answer
785 views

Passing array from one plpgsql function to another

I have a function in PostgreSQL 9.4 that calls another function. Both are written in plpgsql. The child function takes an array argument representing IDs from a table that is used to create a new ...
1
vote
3answers
671 views

How to store short stories for access to individual sentences?

I am building a database for the first time ever (using PostgreSQL), and am very conflicted over the most efficient/logical way of storing a body of text (aka, a story). The conflict stems from the ...
1
vote
1answer
5k 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 ...
1
vote
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 ...
1
vote
1answer
2k views

Recovering a dropped column?

I have a PostgreSQL table: CREATE TABLE essays ( id serial primary key, content text ); I inserted a few rows, then ran: ALTER TABLE essays DROP COLUMN content; How can I recover the data? ...