Questions tagged [postgresql-9.5]

Specifically for PostgreSQL version 9.5

Filter by
Sorted by
Tagged with
4
votes
1answer
5k views

Changing foreign key to ON DELETE CASCADE with least amount of impact

I have an existing foreign key that has ON DELETE NO ACTION defined. I need to change this foreign key to ON DELETE CASCADE. I can do this within a transaction: begin; alter table posts drop ...
4
votes
1answer
966 views

Filter is applied after DISTINCT ON

There's a script at the end that will fully create the schema and populate it with sample data. Schema Consider these two tables: Table of polygons: CREATE TABLE my_polygon ( my_polygon_id ...
4
votes
3answers
2k views

Index on JSON field with dynamic keys

I'm on PG 9.5 and I have a table Visitors(id, data::json) Example: Visitor(id: 1, data: {name: 'Jack', age: 33, is_user: true }) I'd like to perform queries like Give me all visitors named Jack ...
4
votes
3answers
1k views

Is there anyway to UPDATE a series of columns on a row with a composite type record/row?

Take a table foo, CREATE TABLE foo(a,b,c,d) AS VALUES (1,2,3,4); I can insert into foo with a row type.. INSERT INTO foo SELECT (foo).* FROM foo; INSERT INTO foo SELECT (v).* FROM ( ...
4
votes
1answer
1k views

Inserting in PostgreSQL Foreign Table Violates Primary Key Constraint10

I have two local databases in one of them I have created a foreign table that is referencing a table in the second database. When I want to insert in foreign table it inserts the primary key all over ...
3
votes
4answers
457 views

How to best store pixels in a database?

When loading data into a table, I get the following error: ERROR: row is too big: size 8680, maximum size 8160 The table has 1000+ columns in it, which appears to be the problem. The general ...
3
votes
3answers
5k views

Find rows in which the column doesn't contain a “space”

I'm using Postgres 9.5. I want to search for rows in which my name column does not contain a space. I'm a little murky on how to define a space to you, though. I thought it would just be the space ...
3
votes
3answers
872 views

Why is my PostgreSQL expression index not being used when I ORDER BY in a subquery? [duplicate]

I have a query that gets about 80k rows from said table with about 3m. I only need to paginate ten rows at a time, but with ORDER BY (a+b) DESC (a and b are integer type columns.) Right now my query'...
3
votes
4answers
178 views

Postgres LIKE query using a GiST index is just as slow as a full scan

What I have is a very simple database that stores paths, extensions and names of files from UNC shares. For testing, I inserted about 1,5 mio rows and the below query uses a GiST index as you see, but ...
3
votes
2answers
908 views

How do I set the isolation level for SELECT statement?

Using PostgreSQL 9.5 I would like to make sure that when I run a SELECT statement I get consistent results, i.e. if the table is modified while the SELECT is running, I want to get the state that was ...
3
votes
1answer
996 views

Use specific array column in the where clause

Having a function that returns an array and in a query we use the function to return results such as: {"TUNDUMA GOING (BORDER)","ARRIVAL DATE",09/01/2016,22,"AT CHECKPOINT"} {"TUNDUMA RETURN (BORDER)"...
3
votes
1answer
706 views

Why am I getting old data?

Today I discovered something weird on PostgreSQL 9.5. (I have no idea whether this is because of beta or not.) When I want to fetch data, I get old and deleted data from query. I then do VACUUM FULL ...
3
votes
1answer
3k views

How to allow user access to non-owned objects in Postgres?

I am in the process of creating a pre-prod Postgres database, and want to ensure the accessing user has limited access. I want to setup tables in such a way that the accessing user can't CREATE, DROP ...
3
votes
1answer
576 views

How do I build a PostgreSQL ORDER BY expression based on a concatenation of columns?

I'm using Postgres 9.5. I want to write an order by expression for a series of concatenated strings, but I'm not sure if that's possible or how to do it. I have cobbled together the below myproject=...
3
votes
2answers
801 views

Uniqueness constraint in union of two columns' values

Using postgresql 9.5, assume I have a table: CREATE TABLE t ( id INT, primary TEXT, secondary TEXT ) I want to guarantee that the union of values in primary and secondary does not contain ...
3
votes
1answer
4k views

Assignment of a column with dynamic column name

I get the name of a column to set in a (BEFORE UPDATE) trigger, I want to set it to the OLD value and ignore anything coming in. I've tried the following: CREATE OR REPLACE FUNCTION ...
3
votes
1answer
4k views

ON CONFLICT ON CONSTRAINT fails saying constraint doesn't exist

I'm trying to use new Postgresql 9.5 upsert feature. But for some reason my query saying constraint doesn't exist (when it does). My query is this INSERT INTO journals (ext_ids, title) VALUES ('{"...
3
votes
1answer
5k views

How do I select arrays that are not empty?

Why is this so tricky, what is token set to that it isn't equal to null nor an empty string? SELECT lexemes FROM ts_debug('This is a title') WHERE alias = 'asciiword'; lexemes --------- {} {} {}...
3
votes
1answer
68 views

Hint on some possible query improvement

I am currently learning SQL on my own and I am looking for a review of one of my query. I have the following problem. "Given a table of players and a table of played match output the ranking of the ...
3
votes
1answer
59 views

Why does PostgreSQL scan a lot of rows?

I have a small table of 135,000 rows. Here is the schema: id integer NOT NULL nextval('history_sum_id_seq'::regclass) zone_id integer NOT NULL spot_id character varying(24) NULL::...
3
votes
1answer
4k views

Alter default privileges for role

I have database called Blackacre and two roles who have access to it: johnrole and janerole. janerole has been granted ALL privileges and johnrole has only been granted the SELECT privilege. Problem ...
3
votes
1answer
92 views

Issue with Aggregate Function

I want to insert a tuple in my leggeapprovata table, but this table takes the attributes from 4 other tables. One of these 4 tables is the votazione table, from which it takes 6 attributes. Two do not ...
3
votes
1answer
45 views

How to retrieve the value of deleted key in jsonb?

Using the minus operator of jsonb, we can delete the required key, which is super cool, but what if we need to retrieve the value of deleted key? Thus, if the key was not found, a null is returned or ...
3
votes
1answer
840 views

PostgreSQL logical replication using pglogical

I am testing logical replication using Pglogical with Postgresql v9.5. Simple configuration seems OK. It means: create pglogical extension in the two nodes create a set and add tables from public ...
3
votes
2answers
2k views

Encryption with pgcrypto

I am working on encrypting a column in a postgre table using pgcrypto. The postgre version is 9.5.1. I have figured out how to encrypt using pgp_sym_encrypt() and decrypt using pgp_sym_decrypt(). My ...
3
votes
1answer
22k views

PostgresSQL date/time field out of range on seconds-since-epoch

I am importing a csv file into a PostgreSQL database. Most of the file imports without any trouble until I get this message ERROR: date/time field value out of range: "1421088300" HINT: Perhaps you ...
3
votes
1answer
40 views

Login using database user accounts

I want to know is it advisable to directly use the database user and passwaord as the user of web application generally i have seen everywhere they use a separate table inside database for managing (...
3
votes
1answer
125 views

Postgres: Preventing objects with same name

I just spent one hour debugging a query which had previously worked but suddenly created strange results. The cause of this bug was that I have a table in one schema which I reference in the query. ...
3
votes
1answer
2k views

How to use RETURNS TABLE with an existing table in PostgreSQL?

From the PostgreSQL documentation here: There is another way to declare a function as returning a set, which is to use the syntax RETURNS TABLE(columns). ... This notation is specified in recent ...
3
votes
1answer
527 views

Why so many loops in pkey index scan?

My query executes very slowly despite all indexes in place: SELECT * FROM "entry" INNER JOIN "entrytag" ON ("entry"."id" = "entrytag"."entry_id") WHERE "entrytag"."tag_id" = 323456 ORDER ...
3
votes
1answer
1k views

How to restore PostgreSQL standby server from archive?

I have a PostgreSQL 9.5 cluster with master and standby server that are replicated using repmgr. Master is configured to keep 4000 WAL files: wal_level = logical hot_standby = on archive_command = '...
3
votes
0answers
544 views

Setup a PostgreSQL HA with synchronous replication in windows

I am fairly new to postgres ans would like recommendations for setting up HA system in windows Server machines. My current plan is to run 2 server Primary (Data center) with postgres 9.6 and same ...
3
votes
0answers
263 views

AWS RDS postrgres massive disk use, small tables

I can't figure out why our AWS postgres server has consumed all of it's space. We just had to up the storage space allocated to it, but can't find any hint from postgres that it's using that much ...
3
votes
0answers
408 views

pg_restore error: Extensions installed out of sequence

I have a PostgreSQL 9.5 server with the pg_cron extension installed. I'm creating a backup of a database named testdb using the command: $ pg_dump -c -U postgres -Fc -f test.dump testdb When I ...
3
votes
0answers
2k views

PostgreSQL slower query after partitioning

I have a 75GB table with more than 100M records. I've partitioned that table by month. Each partition is ~8M records big. Most of the queries works on recent data and almost every query filters by ...
3
votes
1answer
2k views

Postgres 9.5 xlog fills all disk space in replication setup with replication slot

I have replication set up on Postgresql 9.5 using replication slot. I have one primary and one slave machine, both Ubuntu 14.04 with a cluster called main that is in a directory other than the default....
3
votes
1answer
408 views

Why is my tsv index not being used?

I'm trying to get the postgres full-text-search facility functional. I have two tables, one I created just for testing, and the actual one I want to be able to search: Test Table: webarchive=# \d ...
2
votes
2answers
2k views

Create INSERT INTO from SELECT in PostgreSQL

How to create INSERT INTO statements, using SELECT statement? I need it because I want to delete 10k of records from a table, but if someday some of that record need to be restored I want to do that ...
2
votes
2answers
456 views

What are the possible performance implications of dropping a not used ‘surrogate key’ column?

I have a very large (>10m rows) table in a Postgres 9.5.4 database that collects readings from sensors. This table handles many inserts (~2k/minute) but isn't queried too often--only during a cache ...
2
votes
2answers
2k views

What is the best way to create Primary Key on a large table in Postgresql 9.5?

I have to create a primary key on a large table (~100Million records) in Postgtres database. What is the best and fastest way to create pkey? This column is a sequence column and i don't want to lock ...
2
votes
2answers
341 views

PostgreSQL on Desktop vs. Server

How does running PostgreSQL on a Windows desktop with pgAdmin III compare to running it on a dedicated server? I am trying to develop a database with very large tables (~60 million rows) with the ...
2
votes
1answer
2k views

PostgreSQL streaming replication stops updating

I have four PostgreSQL 9.5 instances running on EC2 using m4.8xlarge instances with five PIOPS SSD in a RAID0 setup, and a separate XLOG drive. Until this morning, I never had more than a minute or ...
2
votes
1answer
1k views

Postgres 9.5 foreign table inheritance not using indexes

In PostgreSQL 9.5.0 I have a partitioned table that collects data by months. I tried to use the new PostgreSQL feature of foreign table inheritance and pushed one month of data to another PostgreSQL ...
2
votes
4answers
51 views

How can I get an accurate count of rows within a PostgresSQL table based upon a record type?

How can I get an accurate count of subscribers that are "active" and subscribers that are "inactive/suspended"? I tried following the documentation, but it's a bit vague on how to match records ...
2
votes
1answer
2k views

Update value in timestamptz column

I have a table column with the data type timestamptz. Time zone of server is America/Denver. Going through some strange issue while inserting a value in that column. When I update the column to the ...
2
votes
1answer
388 views

Go back in time from a PostgreSQL backup using xlog

I have a PostgreSQL backup and WAL archived before the db snapshot was taken. Is it possible to restore the database using PITR to a time before the snapshot was taken?
2
votes
1answer
668 views

Is it possible to modify postgresql.conf using pg_ctl?

I am programmatically starting PostgreSQL instances using pg_ctl init and pg_ctl start. I need to modify the configuration in postgresql.conf (specifically listen_addresses, and maybe some other ...
2
votes
1answer
3k views

Postgres ignoring wal_level setting in conf

I am trying to configure replication in Postgres, and running into an issue where the server is ignoring the setting for wal_level in the configuration file. I have set in the config file: wal_level ...
2
votes
1answer
360 views

Postgres cascades truncates to unrelated tables

I've created a number of tables in my public schema. Occasionally, I need to run a seeder to refresh the users table when I perform tests. To do this, I'm executing TRUNCATE TABLE users RESTART ...
2
votes
1answer
4k views

INSERT into three tables with many-to-many from one table

With Postgres 9.5 using a trivial example. Given I have this data: Table everything: | fruit | country | |--------|---------| | Banana | USA | | Banana | Panama | How would I create these ...