Questions tagged [postgresql-9.5]

Specifically for PostgreSQL version 9.5

Filter by
Sorted by
Tagged with
4
votes
1answer
726 views

Aggregated column causes full table scan even though the right index is present

I have a query where I want to fetch the first few rows from the table datasets ordered by the date_added column. The column that is sorted by is indexed, so the basic version of this table is very ...
4
votes
2answers
3k 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 ...
4
votes
3answers
1k 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'...
4
votes
3answers
2k views

PostgreSQL importing thousands of columns as an array

PROBLEM: I have a CSV file with 16,382 columns with data that looks like this: +-------------+-----------+------+-------+------+------+------+-----+-------+-----+ | PATIENT_ID | DIAGNOSIS | 1 | ...
4
votes
2answers
250 views

GIN index ignored when query has many (more than 18) OR statements

I have an issue where the execution is not using a GIN index when going over 18 OR statements. This is my index: CREATE INDEX emailjobs_externalreferences_gin ON vsko_mailer_api_prod.emailjobs USING ...
4
votes
1answer
909 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 ...
4
votes
2answers
219 views

How can I safely rename a sequence in PostgreSQL, ideally without downtime?

We want to rename some sequences in our production database for certain reasons. Is there a way to do this safely in production without having to first close all connections to the database? It's ...
4
votes
2answers
743 views

PostgreSQL filter/aggregate performance fluctuates depending on condition value

I'm experiencing a strange variation of filter performance depending on the value in WHERE condition with this query: SELECT MIN(revenue) AS min_revenue, MAX(revenue) AS max_revenue FROM ...
4
votes
1answer
6k 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 ...
4
votes
1answer
41k 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 ...
4
votes
2answers
16k views

Using postgres database from another computer

I have PostgreSQL version 9.5 installed at my machine (Windows 7 Enterprise x64 bit version). I have several databases in schema 'public'. I would like to use/access one of these databases from ...
4
votes
1answer
103 views

“Inner” Group By Query sequential scan

I have inner-query in left-join that is using sequential scan instead of index scan, and I have no idea on how to improve it. I have two tables: pages - columns: id shares - each page has many ...
4
votes
2answers
12k views

PostgreSQL: ANY (VALUES(…)) in WHERE clause causes drastic slowdown

Okay, I previously asked a question regarding a large dataset but it was never answered, so I decided to cut it down and ask about a smaller subset of the previous setup and simplify what I am trying ...
4
votes
1answer
501 views

PostgreSQL not using index scan when a second ORDER BY column is present

I need to query the latest entry from a table with >130mn entries using PostgreSQL 9.5. There are two columns of interest, row_id (integer, the primary key), and row_time (timestamp, not null, with ...
4
votes
1answer
74 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::...
4
votes
2answers
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 ...
4
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 ...
4
votes
1answer
823 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 ...
4
votes
1answer
1k 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
3k 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
2k 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
0answers
521 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 ...
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
587 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
2answers
4k 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 ...
3
votes
2answers
1k 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
1k 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
7k 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
6k 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
959 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
1k 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
5k 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 ...
3
votes
1answer
70 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
184 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
47 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
1k 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
1answer
45 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
170 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
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
666 views

Restoring from PostgreSQL 9.5 dump to 10 is super slow

I am trying to restore database dump created on PostgreSQL 9.5.16 to PostgreSQL 10.7 and it is taking a lot of time to restore. Restoring the same dump to 9.5 doesn't take that much. So I am wondering ...
3
votes
0answers
1k 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
562 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
1answer
6k views

Pull Data from one database and insert to another database ( same schema )

I am currently working on moving data from one database to another in PostgreSQL. I am studying python with psycopg2 to connect and run SQL queries in one database. My problem is how can I pull and ...
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
0answers
813 views

Any way to squeeze performance from Postgres JSONB queries?

This is using RDS Postgres 9.5.2 We have a table which contains about 25 columns, and one is a jsonb col that essentially is a simple/flat json object (key/value, not complex objects). The data in ...
3
votes
0answers
249 views

Postgresql row-level security generates different query plan from manually inserted WHERE clause

(Copied from my post to the postgresql mailing list https://www.postgresql.org/message-id/CADBa3wZpuYXwdry2g68NxYmAXOPvx0DLvdAU4kdo2GoPtxKu+Q@mail.gmail.com. Unfortunately I need at least 10 ...
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
465 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
583 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
452 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 ...

1
2
3 4 5
7