Skip to main content

Questions tagged [postgresql-9.5]

Specifically for PostgreSQL version 9.5

Filter by
Sorted by
Tagged with
31 votes
2 answers
97k 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,...
Tony Indrali's user avatar
31 votes
2 answers
31k 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 ...
Shaun McCready's user avatar
30 votes
2 answers
25k 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 ...
Oliver Salzburg's user avatar
29 votes
2 answers
47k 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). ...
Golo Roden's user avatar
26 votes
6 answers
145k 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 ...
uberrebu's user avatar
  • 451
26 votes
1 answer
4k 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 ...
Kai's user avatar
  • 781
24 votes
2 answers
38k 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 ...
Oto Shavadze's user avatar
24 votes
1 answer
4k 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 ...
Robin Joseph's user avatar
20 votes
3 answers
69k 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 ...
hky404's user avatar
  • 313
19 votes
1 answer
44k 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 ...
d9k's user avatar
  • 293
17 votes
2 answers
23k 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 ('{"...
expert's user avatar
  • 275
16 votes
1 answer
3k 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 ...
Basil Bourque's user avatar
15 votes
1 answer
9k 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 ...
tomka's user avatar
  • 947
14 votes
1 answer
8k 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 ...
AlexC's user avatar
  • 309
14 votes
1 answer
6k 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 ...
jvb's user avatar
  • 281
14 votes
1 answer
24k 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 ...
James's user avatar
  • 141
14 votes
1 answer
26k 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 ...
TheCloudlessSky's user avatar
13 votes
3 answers
31k views

How to insert 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: ...
Andrew's user avatar
  • 131
13 votes
1 answer
9k 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 ...
John Powell's user avatar
  • 1,578
13 votes
1 answer
9k 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 ...
J-DawG's user avatar
  • 283
12 votes
1 answer
133k 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 ...
falcs's user avatar
  • 223
11 votes
3 answers
18k 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 ...
Jay Cummins's user avatar
10 votes
2 answers
19k 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 ...
tinlyx's user avatar
  • 3,590
9 votes
4 answers
25k views

Postgresql extremely slow count (with index, simple query) [duplicate]

I need to run these simple queries on a table with millions of rows: SELECT COUNT(*) FROM "subscriptions" WHERE "subscriptions"."project_id" = 123; SELECT COUNT(*) FROM "subscriptions" WHERE "...
collimarco's user avatar
9 votes
1 answer
36k 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 ...
Pushpendra Pal's user avatar
9 votes
2 answers
5k 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'...
Stephen Smith's user avatar
9 votes
1 answer
8k 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 ...
Andremoniy's user avatar
9 votes
2 answers
10k 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 |...
CherrySkizz's user avatar
9 votes
1 answer
707 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 ...
Jan Katins's user avatar
9 votes
1 answer
2k 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 ...
ChrisJ's user avatar
  • 591
8 votes
2 answers
12k 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 ...
rkt's user avatar
  • 81
8 votes
1 answer
27k 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 ...
Gen's user avatar
  • 193
8 votes
1 answer
25k views

PostgreSQL; Update different column on condition using CASE statement

I have a table which has different types of balance and I need to deduct the balance from different columns based on availability. Here is the query (Sample) I tried to use but it didn't work update ...
Rajesh Ranjan's user avatar
8 votes
2 answers
32k 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 ...
raphael's user avatar
  • 539
8 votes
1 answer
6k 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....
Andrew Cetinic's user avatar
8 votes
1 answer
5k views

Create Materialized View from foreign table

I'd like to create a simple materialized view from a table which lies in a different database. The two databases are on the same server. What do I have to add to make the query access the foreign ...
andschar's user avatar
  • 237
7 votes
3 answers
23k 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 ...
Dave's user avatar
  • 713
7 votes
4 answers
21k 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 ...
Michael Hooreman's user avatar
7 votes
1 answer
6k 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 ...
Sijan Shrestha's user avatar
7 votes
1 answer
2k 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 ...
LAFK says Reinstate Monica's user avatar
7 votes
1 answer
555 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:...
Kev's user avatar
  • 444
6 votes
2 answers
11k views

Postgresql-Server won't start after moving data directory

I am running a local postgresql server (9.5 & 9.6) on my Ubuntu 17.04 machine. After moving my data directories to another hard drive following this guide the server can't start. I.e. with sudo ...
daveknave's user avatar
6 votes
2 answers
16k views

How to copy a PostgreSQL database at a filesystem level between servers

I have a large PostgreSQL database on a server with a failing disk. I cannot run pg_dump because of disk errors. I have another server with the identical OS (Ubuntu 16.04) and the identical version ...
Cerin's user avatar
  • 1,365
6 votes
3 answers
14k 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 ...
Tim Smith's user avatar
  • 161
6 votes
2 answers
27k 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: | ...
alexanderadam's user avatar
6 votes
2 answers
2k 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, ...
unseen_damage's user avatar
6 votes
2 answers
11k 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"?...
ogirginc's user avatar
  • 265
6 votes
3 answers
6k 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 ...
Mads's user avatar
  • 61
6 votes
3 answers
17k views

Postgresql query is very slow for my big table

My database version is postgresql 9.5. create table if not exists request_log ( id bigserial not null constraint app_requests_pkey primary key, request_date timestamp not ...
barteloma's user avatar
  • 161
6 votes
2 answers
25k 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 ...
Timusan's user avatar
  • 161

1
2 3 4 5
8