Questions tagged [postgresql-9.5]

Specifically for PostgreSQL version 9.5

Filter by
Sorted by
Tagged with
27
votes
2answers
75k 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,...
25
votes
1answer
3k 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 ...
23
votes
1answer
31k 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). ...
22
votes
2answers
13k 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 ...
21
votes
2answers
17k 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 ...
21
votes
2answers
27k views

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

Default values are, according to documentation for min_wal_size and max_wal_size parameters: For max_wal_size: The default is 1 GB For min_wal_size: The default is 80 MB Then I look this parameters ...
21
votes
1answer
2k 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 ...
20
votes
2answers
16k 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 ...
18
votes
1answer
32k 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 ...
17
votes
6answers
79k 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 ...
16
votes
3answers
46k 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 ...
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 ...
13
votes
1answer
17k 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 ...
13
votes
1answer
5k 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
6k 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 ...
13
votes
1answer
6k 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 ...
12
votes
1answer
7k 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 ...
10
votes
2answers
17k views

Inserting 10000 new rows?

Presumably there is a straight forward and easy solution. I'm wanting to create 10000 new rows - that are numbered sequentially with no data per row (except sequentially numbered id). I have used: ...
10
votes
2answers
10k 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 ('{"...
10
votes
3answers
13k 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 ...
9
votes
1answer
5k 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 ...
9
votes
1answer
12k 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 ...
9
votes
1answer
399 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 ...
8
votes
2answers
4k 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'...
8
votes
2answers
23k 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 ...
8
votes
2answers
6k 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 ...
8
votes
1answer
4k 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....
7
votes
3answers
13k 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 ...
7
votes
1answer
28k views

How to use function parameters in dynamic SQL with EXECUTE?

I have written a PL/pgSQL function in PostgreSQL 9.5. It compiles well but when I call it from pgAdmin3 it gives me an error. It seems like the dynamic query with columns to be replaced with the ...
7
votes
1answer
368 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:...
7
votes
2answers
6k views

Postgresql json column to view

I have a table that looks like this id | json_column ---+------------ 1 | {"text1":"a", "text2":"b", "text3":"c", ....} 2 | {"text1":"b", "text2":"c", ....} and I want it to be like this text1 |...
6
votes
2answers
5k 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 ...
6
votes
1answer
4k views

Find which functions are used

I have a legacy PostgreSQL database schema with hundreds of functions. I know that some of them aren't used anymore, and I want to delete them. What is the easiest way to find which procedures are ...
6
votes
1answer
1k views

Width in Postgres EXPLAIN?

Explain plan in Postgres has width, which (per docs) is: Estimated average width of rows output by this plan node (in bytes). My question: when will width be 0? Because in examples from docs, it ...
6
votes
2answers
16k 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
67k 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 ...
6
votes
2answers
10k 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"?...
6
votes
3answers
12k views

“using stale statistics instead of current ones because stats collector is not responding”

My postgresql 9.5 logs shows every minute the message: using stale statistics instead of current ones because stats collector is not responding Except a post on serverfault which is not helpfull ...
6
votes
1answer
109 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" ( ...
6
votes
0answers
921 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 ...
6
votes
0answers
419 views

Roles and policies in Postgresql 9.5 row-level security

Following the the info in posts like these i would like to use the role system and policies, i.e. each user in my system would have a db role. I want to model the following properties but i can't come ...
5
votes
3answers
1k 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 ...
5
votes
2answers
346 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
1k 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
3k 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
3answers
9k 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 ...
5
votes
1answer
93 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
1answer
6k 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 ...
5
votes
2answers
22k 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 ...
5
votes
2answers
1k 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, ...

1
2 3 4 5
8