Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Specifically for PostgreSQL version 9.5

24
votes
1answer
1k views

PostgreSQL DELETE FROM fails with `Error: attempted to delete invisible tuple`

The error Trying to delete tuples containing invalid timestamps with DELETE FROM comments WHERE date > '1 Jan 9999' OR date < '1 Jan 2000' OR date_found > '1 Jan 9999' OR date_found < '1 ...
16
votes
1answer
2k views

Why does time zone have such a crazy offset-from-UTC on year 0001 in Postgres?

In Postgres 9.5, I was surprised to see the result seen below while experimenting with year 0001 (no year zero 0000). Offset of -07:52:58? Some example code. Note that I mixed use of TIMESTAMP WITH ...
16
votes
2answers
4k views

How to get the ID of the conflicting row in upsert?

I have a table tag with 2 columns: id (uuid) and name (text). I now want to insert a new tag into the table, but if the tag already exists, I want to simply get the id of the existing record. I ...
13
votes
1answer
3k views

PostgreSQL 9.5 won't start after Windows 10 fall update

I've installed Windows 10 Fall update (1709) and now my PostgreSQL 9.5 server won't start. It worked yesterday before the update and I haven't made any changes to the configuration. I've checked ...
13
votes
1answer
947 views

Postgres: How is SET NOT NULL “more efficient” than CHECK constraint

In PostgreSQL docs for Constraints, it says A not-null constraint is functionally equivalent to creating a check constraint CHECK (column_name IS NOT NULL), but in PostgreSQL creating an explicit ...
11
votes
2answers
22k views

UPSERT with ON CONFLICT using values from source table in the UPDATE part

Given: CREATE TABLE A ( PK_A INT8 NOT NULL, A INT8, PRIMARY KEY (PK_A) ); CREATE TABLE B ( PK_B INT8 NOT NULL, B INT8, PRIMARY KEY (PK_B) ); This query: insert into table_b (pk_b, b) select pk_a,...
11
votes
1answer
2k views

How to compare xmin and txid_current() after transactions ID wraparound?

Besides its regular columns, Postgres tables also have various system columns available. One of them, xmin, stores the transaction ID used to create a row. Its data type is xid, a four byte integer ...
10
votes
2answers
3k views

PostgreSQL UPSERT issue with NULL values

I'm having an issue with using the new UPSERT feature in Postgres 9.5 I have a table that is used for aggregating data from another table. The composite key is made up of 20 columns, 10 of which can ...
10
votes
1answer
3k views

postgres_fdw performance is slow

The following query on a foreign takes about 5 seconds to execute on 3.2 million rows: SELECT x."IncidentTypeCode", COUNT(x."IncidentTypeCode") FROM "IntterraNearRealTimeUnitReflexes300sForeign" x ...
8
votes
1answer
4k views

Permission denied to create “pg_catalog.tablename” during pg_dump targetting another machine

I am trying to dump some tables from a 9.5 beta 2 to a 9.4.4 server. The form of the command I am using is the fairly standard: pg_dump -t table dbname | psql -h hostname -d dbname I am using the ...
8
votes
1answer
8k views

Understanding “max_wal_size” and “min_wal_size” parameters default values from postgresql.conf file

According to documentation, for "min_wal_size" and "max_wal_size" parameters default values are: For max_wal_size: The default is 1 GB For min_wal_size: The default is 80 MB Then I look this ...
8
votes
1answer
194 views

Statistics on index on partitions not used in query on the whole table

The following join has very different row estimates when doing a join on the partitions vs when joining on the whole table: CREATE TABLE m_data.ga_session ( session_id BIGINT ...
7
votes
2answers
2k views

Constraint - one boolean row is true, all other rows false

I have a column: standard BOOLEAN NOT NULL I would like to enforce one row True, and all others False. The are no FK's or anything else depending on this constraint. I know I can accomplish it with ...
7
votes
1answer
2k views

How to create an index on CASE expression in Postgres

I am trying to create an index on CASE expression, as follows CREATE TABLE test(i INT, j INT); CREATE UNIQUE INDEX test_index ON test(CASE WHEN i=1 THEN j END); Getting this error: ERROR: syntax ...
7
votes
2answers
562 views

Grouping on any one of multiple columns in Postgres

Is it possible to create some sort of chain of grouping in Postgres? Let's say I have the following chart: CREATE TABLE foo AS SELECT row_number() OVER () AS id, * FROM ( VALUES ( 'X', 'D', 'G', 'P'...
7
votes
1answer
4k views

Refresh a PostgreSQL materialized view automatically without using triggers

I created a materialized view named view_table_A on a foreign data wrapper table table_A. What I want is for the view to be automatically updated after every new insert in table_A. I tried to do this ...
6
votes
3answers
8k views

How can I return multiple rows of records in PL/pgSQL

I am trying to return multiple records using RECORD data type, is there a way I can append to RECORD and add/append a new value with each iteration to this RECORD. that is, I want to append to rec so ...
6
votes
1answer
115 views

Why is PostgreSQL 9.5 not using my newest index for ORDER BY, even though it uses similar indices just fine?

(Follow up from this post: Why is my PostgreSQL expression index not being used when I ORDER BY in a subquery?) PostgreSQL 9.5. I can't divulge full details, but table has 22 columns and 5 indexes:...
6
votes
2answers
2k views

How to get minimum and maximum for grouped timestamps

I would love to know how to create a view that groups timestamps in 10 minute nearest 10 minute intervals and contains each minimum and maximum timestamp for each. So a table that looks like this: | ...
6
votes
1answer
7k views

Querying JSONB in PostgreSQL

I have a table, persons, which contains two columns, an id and a JSONB-based data column (this table has just been made for demonstrational purposes to play around with PostgreSQL's JSON support). ...
6
votes
1answer
97 views

How to improve this union to ensure consistent results?

I've been trying to find an reliable and economic way of counting rows over a 7 day period and so far, this is the best I've been able to come up with: Schema CREATE TABLE IF NOT EXISTS "data" ( ...
5
votes
3answers
20k views

Delete all data in Postgres database

I have created a fresh db dump from a production server with the --data-only and --column-inserts flags, so I only have a bunch of insert statements to insert data when performing a restore on a ...
5
votes
2answers
276 views

Why do my queries take longer together than when I run them separately?

I have a series of update statements that follow a general pattern: one update aggregates values from another table (or sometimes multiple tables), the next update produces a rank based on the ...
5
votes
1answer
285 views

Why does this implicit join get planned differently than an explicit join?

In this answer I explain SQL-89s implicit syntax. But I noticed different query plans while playing around: EXPLAIN ANALYZE SELECT * FROM (values(1)) AS t(x), (values(2)) AS g(y); ...
5
votes
1answer
1k views

Execute a series of commands in PSQL

I am looking for a solution to execute multiple commands in PSQL. Lets say I have a file with 2 name for database. 1. slave1 2. slave2 I already have a master template named "master_db". If I ...
5
votes
1answer
426 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 ...
5
votes
1answer
68 views

Postgres WITH not performed first

I am working in PostgreSQL 9.5 on a SQL query that pulls from two views. Individually, the views take 93 msec and 530 msec to perform, resulting in ~1500 and ~6200 rows, respectively. This seems ...
5
votes
2answers
200 views

Postgres Update Limit

I'm curious if Postgres has anything in place where I could limit the amount of rows kept for a ID. For example, say I have a users table and login_coordinates table. Every time the user logs in, ...
5
votes
2answers
4k views

How to upgrade PostgreSQL from 9.5 to 9.6.1 without losing data?

When I tried to run psql, I got psql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/tmp/.s.PGSQL.5432"?...
5
votes
2answers
6k views

How to insert values into a table with default values from a select query in PostgreSQL?

Is it possible to INSERT values into a PostgreSQL table from a SELECT statement and to use DEFAULT values for the columns that are null? In my case, the SELECT statement is selecting from JSON. In ...
5
votes
1answer
498 views

Postgres long autovacuum halting database

I have a fairly large table (1 million rows) and my database is stuck on an autovacuum (>30 mins) on the this table, causing the whole database to chock. Application won't even load now. -00:37:31....
5
votes
2answers
366 views

Help with Recursive CTE query with INSERT, ON CONFLICT and RETURNING

I'm trying to write a query to insert filenames to the following table table path CREATE SEQUENCE path_id_seq; CREATE TABLE path ( id INTEGER PRIMARY KEY DEFAULT NEXTVAL('path_id_seq'), ...
5
votes
1answer
594 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 ...
5
votes
1answer
418 views

Postgresql improve select performance/parallelism in huge table

I have a huge table (> 3000 million rows, totaling 3 TB) using Postgres 9.5 defined as follows: CREATE TABLE user_events ( user_id VARCHAR NOT NULL, datetime BIGINT NOT NULL, field1 ...
5
votes
1answer
597 views

Installation of PostgreSQL 9.5.2-1 got stuck [closed]

Installation of PostgreSQL 9.5.2 got stuck in Windows 7 (64-bit). I let the installation in that state for 4 days but nothing happened. I have posted the log of bitrock installer that it creates in '...
5
votes
1answer
143 views

How to not “duplicate” a table if it has a unique constraint over many columns? [duplicate]

I have a very large table (35GB) that is unique over a combination of four of its columns. The table isn't very wide and the four columns it is unique over are the columns that are larger (in bytes)...
4
votes
3answers
434 views

Trigger function to update column

I'm a newbie to PL/pgSQL ... I use Postgres 9.5.0, and need to update a column every time a new record is inserted. The column shall be filled in from the values entered in area_pol and area_ofi. I'm ...
4
votes
3answers
717 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
3answers
2k views

How do I disable escaping when using COPY FROM in PostgreSQL?

I have a large tab delimited file that I want to read into a table in PostgreSQL 9.5. It contains double-quotes and backslashes that I want to treat as regular characters. I think COPY FROM is the ...
4
votes
1answer
6k views

Does a COMMIT work within an anonymous plgpsql function in PostgreSQL 9.5?

I am importing a large number of large files into a number of tables to be partitioned using loops within an anonymous plpgsql code block $do$. $do$ BEGIN FOR yyyy in 2012..2016 THEN ...
4
votes
1answer
56 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
1answer
671 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 = '...
4
votes
1answer
807 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 ...
4
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
4answers
405 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
2k 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
520 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
1answer
858 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
2answers
324 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 ...
3
votes
1answer
10k views

psql 9.5: gen_random_uuid() not working

SELECT gen_random_uuid() produces output ERROR: function gen_random_uuid() does not exist SQL state: 42883 Hint: No function matches the given name and argument types. You might need to add ...