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
1
vote
1answer
513 views

Use NEW in FROM clause in Postgres trigger?

I am trying to write a Postgres trigger to unnest an array field before inserting or updating a new row. E.g. SELECT unnest(something) FROM NEW However, this seems to lead to an error: relation '...
1
vote
2answers
110 views

Storing time ranges as JSON?

I have table with jsonb field - timetable which has next structure: { 1: [{start:0, finish: 12}], 2: [{start:0, finish: 12}, {start:12, finish:17}], 3: [{start:12, finish: 17}], 4: [{start:0, finish: ...
1
vote
1answer
348 views

Is there a way to enforce uniqueness of arbitrary key-value pairs in a JSONB object column

I use Postgres JSONB columns to store translations for dynamic content of a multi-language site — locales are keys, translated strings are values ({"en": "Hello", "ru": "Привет"}). There're ...
1
vote
1answer
2k views

How to convert Postgres from 32 bit to 64 bit

I would like to convert from PG 32 bit to 64 bit. I am testing with pg_upgrade (in several scenarios) but I think it cannot do that. With pg_upgrade, I can upgrade PG version 8.5 to v.9 (both of ...
1
vote
1answer
508 views

Replicating from master to slaves and from slaves to master

I have a master and multiple slave machines. All SQL operations on the master node should be sent to the slave nodes. But the tables on the slave nodes may also be written to locally. These local ...
1
vote
3answers
298 views

Get lines of pg_hba.conf with trust method from user with minimal privileges

I try on PostgreSQL 9.3 to get the lines of pg_hba.conf where the authentication method is trust from user with minimal privileges. I wrote a function, but it doesn't work: create function public....
1
vote
1answer
2k views

Use system timestamp comparison in SELECT query

I have to backup some tables on a daily basis using the condition that the backup will be only for one previous day. I am planning to use copy to dump the records in to a csv file. I need help in ...
1
vote
2answers
4k views

What is the default size of text field in Postgres?

In the official documentation (http://www.postgresql.org/docs/9.1/static/datatype-character.html), it states text field is variable unlimited length. I wonder what is the default size of this field ...
1
vote
1answer
18 views

How do you select “array”-type columns via the DBLINK function?

Using PostgreSQL, I can easily do a select statement to return a column that is an array of the datatype point. i.e. colA is the datatype point[] select colA from tbl Returns a column that contains ...
1
vote
2answers
70 views

Can I find errors without using testing data for all the branches of each function?

In PostgreSQL, syntax errors (except for trivial syntax errors) are reported while running the code in question; unlike Oracle, PostgreSQL can't find errors without running the part of the code. This ...
1
vote
1answer
366 views

Multi-valued range subtraction

I have a table (actually a query, but I think it's the same thing) with two range columns. I need to perform a 'subtraction' on the two ranges. Precisely, I need Intersection(a, Complement(b)). Since ...
1
vote
1answer
618 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.) ...
1
vote
1answer
61 views

Is there any slowdown in casting from varchar to text?

In writing this answer, I'm just wondering if there is any slow down in casting from varchar to text or if that cast is just a compile-time trick. I've been told they're represented the same way and ...
1
vote
2answers
148 views

postgres parse_indent() function hang [closed]

select parse_ident('abc,efi&xyz'); Will stay there for ever, not returning. But a similar command will finish. select regexp_split_to_array('abc,efi&xyz', '[,&]'); ...
1
vote
1answer
2k views

Problem with pgpoolAdmin installation

I got a problem with permissions during installation of pgpoolAdmin(newest version). The problem is when I get to window with main config that should be all okay to move to next installtion step. The ...
1
vote
1answer
48 views

Why doesn't jsonb constructor from a NULL record behave like hstore constructor?

When I construct an hstore CREATE EXTENSION hstore; SELECT hstore(null::my_type); hstore ------------------------------------------------ "name"=>NULL, "...
1
vote
1answer
786 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
160 views

How to apply outer limit offset and filters in the subquery to avoid grouping over the complete table used in subquery in Postgresql

I have legacy tables similar to the following: employee ------------------------------------ | employee_id | name ------------------------------------ | 1 | David | 2 ...
1
vote
1answer
4k views

Authentification failure for PostgreSQL server (9.3)

I can currently not connect to my Postgresql server (9.3) from pgadmin (Running on Ubuntu 14.04). The server according to pg_lsclusters is on port 5433. Ver Cluster Port Status Owner Data ...
1
vote
1answer
66 views

Combining separate columns into smallest possible related ranges

I'm trying to discard multiple records that may or may not overlap based on the smallest possible contiguous ranges. I thought of doing something similar to This, however the ranges are numeric ...
1
vote
1answer
56 views

Surprising results from assigning multiple variables in FOR loop

CREATE OR REPLACE FUNCTION my_function(input jsonb) RETURNS jsonb LANGUAGE plpgsql AS -- language declaration required $func$ DECLARE _key text; _value text; BEGIN FOR _key, _value IN ...
1
vote
1answer
426 views

Cluster command locking read only queries on replica

I noticed that clustering can take a long time and lock the table entirely, even for reads. Index Organized Tables and incremental clustering would be perfect, but sadly they aren't supported by ...
1
vote
1answer
435 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
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 ...
1
vote
1answer
602 views

How to pass an array to a plpgsql function with VARIADIC parameter

I am trying to define a plpgsql function in Postgresql 10 with several parameters, including an array. I have already defined the function CREATE OR REPLACE FUNCTION mix_table_fields(...
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
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 ...
1
vote
2answers
830 views

Insert values from a record variable into a subclass table

My goal is to create a stored procedure that builds up a _tbl_out which is a subclass of a _tbl_in. I'm following the results of this question, but for this to work, the tables need to have the SAME ...
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
2answers
1k views

postgres user is not superuser

Im having some problems with my postgresql server. In particular, some databases cannot be dropped. On further inspection, I see that the postgres user is not a SuperUser. There are two other users ...
1
vote
2answers
162 views

Why is this seemingly simple insert trigger function causing deadlocks?

This is a simplified version of my prior question. I removed the many-to-many complexity and still deadlocks. It happens WAY less frequently, but still happens. 😕 The situation... I have a tweet ...
1
vote
1answer
1k 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, ...
1
vote
1answer
3k views

unable to create schema on amazon rds for postgres

I'm trying to create a schema on a postgres instance located on Amazon rds. That schema would be used to import data from an existing schema built on a local db. Issue is I do not have permissions ...
1
vote
0answers
265 views

Implementing a graph-like design on postgresql using CTEs and inheritance

This question is about attempting to model/design an elementary graph-like database within postgres. The starting point is that graphs have: Nodes Nodes have properties Node are connected by (bi/...
1
vote
2answers
135 views

Collapsing a column to yes/no on query

I have a table with two columns name and value. There are many rows with the same name, but with different values. I want to create a function that returns a table with 2 columns. The first column ...
1
vote
1answer
896 views

Function quote_nullable(timestamp without time zone) is not unique

When I run this query on "enterprisedb 9.2", it raises an error: select quote_nullable(to_date('09-02-2014 ','dd-MM-yyyy'))::date; Error: LINE 1: select quote_nullable(to_date('09-02-2014 ','dd-MM-...
1
vote
1answer
953 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 ...
1
vote
2answers
5k views

How to make a table name an attribute in PostgreSQL?

I have received hundreds of tables in PostgreSQL with identical structure and each containing similar data of a different day, so that the table name is the date of the data. I would like to Union ...
1
vote
0answers
119 views

Non-commutative exclusion constraint?

If I create the following table.. CREATE TABLE foo ( x int ); I can EXCLUDE the following, WHERE x = <a duplicate value> (generic unique constraint) WHERE x = <specific value> (type ...
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
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? ...
1
vote
1answer
851 views

Detailed optimal column order with large text and bytea

I read this excellent answer on optimal column ordering and noticed the summation: Generally speaking, if you put the 8-byte-types first then the 4-byte-types and the 2-byte-types last you can't go ...
1
vote
1answer
662 views

pg_dump issue while dumping single schema

pg_dump does refuse to dump a given schema in my DB. pg_dump -d <dbname> -s -n dm_geo -f <filename> Result -- -- PostgreSQL database dump -- -- Dumped from database version 9.5.4 -- ...
1
vote
2answers
148 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
vote
1answer
2k 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
1answer
757 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
144 views

PostgreSQL what happens if TimeZone is not set?

From the docs on 9.1, I see If timezone is not specified in postgresql.conf or as a server command-line option, the server attempts to use the value of the TZ environment variable as the default ...
1
vote
3answers
119 views

PostgreSQL - maximum of sum over different months with ties over multiple years

This question is the PostgreSQL version of a question about MySQL 5.6 here. Originally, it was one question for both RDBMS's but it was suggested to me that, given the different capabilities of the ...
1
vote
3answers
673 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
2answers
648 views

Select column to be inserted based on input column value

Assume the following two tables (and ignore the quality of the database design): Table 1 has columns id, sequence_number, value: it contains many rows. Table 2 has columns id, seq1, seq2, seq3, seq4, ...