Questions tagged [postgresql]

All versions of PostgreSQL. Add an additional version-specific tag like postgresql-11 if that context is important.

Filter by
Sorted by
Tagged with
0
votes
0answers
43 views

Sum on distinct and group by and filtering

This is the next version of Sum on distinct and group by But It goes deeper I have this : CREATE TABLE active_connections(id serial PRIMARY KEY, source text NOT NULL, user_id int NOT NULL); INSERT ...
0
votes
1answer
763 views

Postgres taking 3h to DELETE CASCADE

I have a Postgresql 9.3 with a "mother" table containing items, and a number of "child" tables containing parts of different kinds (e.g. item "AAA" is composed of 1000 parts "P1", 800 parts "P2", 40 ...
0
votes
1answer
2k views

Query for all the Postgres configuration parameters‘ current values?

You can set various configuration parameters for Postgres by either editing the postgresql.conf file manually or by calling ALTER SYSTEM commands. These are two avenues for writing the settings, but ...
0
votes
1answer
1k views

ERROR: column X specified in USING clause does not exist in left table

I have a rather complex query I want to execute. The data on table import_temp_table is imported from a CSV file coming from Google Datastore and CouchDB. The table looks like this: CREATE TABLE ...
0
votes
1answer
919 views

Check lock count for PostgreSQL

Up to now we used check_postgres.pl to monitor our database. We used this to check the count of the locks: https://bucardo.org/check_postgres/check_postgres.pl.html#locks We often see more than 150 ...
0
votes
1answer
174 views

A (simple?) solution to the “logical decoding / sequence” problem in Postgres?

After reading this mind-blowing answer by Craig Ringer to my previous question Achieving "sequence number", rather than dateTime, for updates; postgres specifically I've become interested in ...
0
votes
1answer
150 views

PostgreSQL utilize data from SELECT in BEGIN or use WITH?

I went through the documentation for 9.3 and did not find anything suggesting that I can or can not use data from a SELECT query within a BEGIN query. Is it possible or will I have to resort to a WITH ...
0
votes
1answer
9k views

no pg_hba.conf entry for replication connection from host

(Also posted to pgsql-general) I have been configuring a slave server that needs to connect to the host. Both the master and the standby servers have a pg_hba.conf that looks like this: # Allow ...
0
votes
2answers
868 views

Materialized views Performance

Postgres’ docs note: While access to the data stored in a materialized view is often much faster than accessing the underlying tables directly or through a view, the data is not always current; ...
0
votes
1answer
213 views

Constraint exclusion to fetch latest N rows

I'm managing a table with several million records which are being inserted in real time. A part of my app needs to display the last N inserted rows, so at the beginning I just queried for: select id, ...
0
votes
1answer
3k views

How can I create a bool column for a view based on the result of a statement?

I'm using PostgreSQL as my database. CREATE TABLE groups ( id SERIAL NOT NULL, name TEXT NOT NULL, PRIMARY KEY (id), UNIQUE (name) ); CREATE TABLE users ( id ...
0
votes
1answer
168 views

What does a CONSTRAINT have to do with my unique index?

I have to indexes on my table. The first was created by Django and the second by me. I'm not completely certain what the extra CONSTRAINT means in the first index and am wondering how I would change ...
0
votes
1answer
114 views

Postgres to_tsvector is case sensitive on mac os

On PostgreSQL 10.4 on Ubuntu 10.4-2.pgdg16.04+1 select to_tsvector('simple', 'БОЛЬШИЕ БУКВЫ'); returns 'большие':1 'буквы':2 But on postgres 10.3 (installed by brew) on Mac Os High Sierra version ...
0
votes
2answers
133 views

Store HTTP response in PostgreSQL

I want to store a whole HTTP response in PostgreSQL database row. I need the HTTP status, the headers and the body. AFAIK there is no native data type for this. How could I store a HTTP response? ...
0
votes
1answer
527 views

PostgreSQL: how do I query over a column with a custom range?

I'm trying to write a query over a string column called grades_served that can have values such as k-5 1-5 5-9 The query could be for a specific grade or it could be a range with a start and end ...
0
votes
1answer
47 views

How to update an element in json array with where clause?

I came across this question on how to update nth element of array, but it has nested json values in it instead of a plain array of strings. Here are some sample json values from jsonb column of the ...
0
votes
1answer
978 views

PostgreSQL synchronous replication time out?

I'm using PostgreSQL 9.3 with synchronous replication. I have 2 synchronous replicators and 1 async replicator. My problem is when 2 synchronous replicators are down, transaction will wait ...
0
votes
1answer
7k views

Combining 2 SELECT queries and printing the results in PostgreSQL

I have 2 queries which I want to use together to produce one output result to the user. The output describes to the user the journey to take to from A to B. I keep getting a syntax error when I try to ...
0
votes
1answer
444 views

How to change a web application that now needs to become OLTP and OLAP application?

Previously, I did a simple web application for an enterprise client using PHP and MySQL. After some time, it has grown to a point where I realized after talking with people here that I need to treat ...
0
votes
3answers
720 views

Database Design Help: MySql/PostgreSQL Single Database/Multiple Databases

We are planning to build a web based custom business analytics tool using PHP and MySQL/PostgreSQL. In our application, we are planning to upload customer’s data using a Excel sheet and these data ...
0
votes
1answer
112 views

Migrating a database from one PostgreSQL server to another cleanly

I have a Rails app server that uses Postgres for the DB backend. I'm spinning up a new server and I want to get a clean and up to date copy of the database. I've tried it once so far while the ...
0
votes
1answer
2k views

String replace using concatenated strings from various columns

I'd like to remove a substring in a column via update statement. The substring to replace consists of multiple strings from other different columns but in strict order. The specification says: ...
0
votes
1answer
241 views

Postgresql - Get Oid and relation name for relations created by the query

Consider the following TPC-DS benchmark query 50. with ss as (select ca_county,d_qoy, d_year,sum(ss_ext_sales_price) as store_sales from store_sales,date_dim,customer_address where ss_sold_date_sk = ...
0
votes
1answer
2k views

Using the correct postgres server

I'm not sure what happened but I logged in on friday and did psql -d name to access a db that was perfectly running just fine. Then I log in this monday morning and try to access the same database ...
0
votes
2answers
152 views

What configurations affect the speed of DDL statements in PostgreSQL

I would like to setup PostgreSQL in such a way that dropping and recreating the entire database (~100 tables/views + a few rows per table) is as fast as possible. This is for a development environment ...
0
votes
1answer
43 views

Give particular privileges for a table to existing role

My database have some tables such as: Table A Table B Table C I'm using user monitoring with read-only permission. SELECT grantee, privilege_type FROM information_schema.role_table_grants WHERE ...
0
votes
1answer
29 views

Use Common Table Expression (CTE) to reduce locks (max_locks_per_transaction)

I am getting "ERROR: out of shared memory" presumably because my plpgsql function drops/creates a lot of temporary tables. Searching for a solution to the issue, I found this answer that says: ...
0
votes
1answer
164 views

To what extent do the “non-durability features” affect COPY's performance?

COPY is documented as not being WAL-logged if it's loaded on the same transactions.. In minimal level, WAL-logging of some bulk operations can be safely skipped, which can make those operations ...
0
votes
1answer
346 views

Using PostgreSQL both with (not only) SQL Workbench - how to apply alternate delimiters

I have created a PostgreSQL database. Now I try to work with it in SQL Workbench, but I have a problem with the way it understands statement delimiters. The documentation on alternate delimiters sais ...
0
votes
1answer
234 views

Can PostGIS use spatial indexes on foreign tables?

I'm attempting to perform a query on some foreign spatial tables which is taking several orders of magnitude longer than running on the normal spatial table because the index is not used. Indexes ...
0
votes
2answers
2k views

Sum on distinct and group by

I have this : CREATE TABLE tickets(id serial PRIMARY KEY, source text NOT NULL, user_id int NOT NULL, shop_id int NOT NULL, created_at time DEFAULT current_time); INSERT INTO tickets VALUES (0, 'web-...
0
votes
1answer
32 views

Populate a column using the values of other ones

I would like to add a new column in my table using SQL code in PostgreSQL / PostGIS, the values in this column must be filled from the first eight columns and updated with a trigger as I insert data ...
0
votes
2answers
1k views

Blank (but not null) columns - PostgreSQL 9.2

Doing by: select * from ja_customers where id = 8154501 I receive: You can see that all the required fields are "blank" with some invisible character, so they're not null, so they're valid (...
0
votes
1answer
180 views

How to import an existing schema and tablespace into a postgres database, from a filesystem-level backup

Follow-up question from: Have postgres' pg_dump export an index Say I have an existing database, and I want to add to it another schema with content. I saved that content previously through a ...
0
votes
2answers
302 views

How can I manage an exception generated by Postgis?

I am running the following query on Postgres 10.3/Postgis 2.4: create table table_c as ( select A.*, B.area_name, B.id from table_a A left join table_b B on ST_Contains(A.geom, B.geom)...
0
votes
1answer
52 views

Creating a composite type with “char”?

PostgreSQL has a type called char that's stored as an signed 1 byte int The type "char" (note the quotes) is different from char(1) in that it only uses one byte of storage. It is internally used ...
0
votes
1answer
59 views

Special syntax sth.name() in the CREATE FUNCTION statement, what does it mean?

In this SO question about stored procedures in plpgsql, the stored procedure look like sth.name(). I don't know what is the meaning of the prefix sth. For example: create or replace function ...
0
votes
1answer
25 views

PostgreSQL: What is the best way to cast from from text to point[]?

I'm looking for a way to cast from a text value: '(180,90),(-180,-90)' into a point[] value: {"(180,90)","(-180,-90)"} What is the best way to do this? One possibility that I can think of is to ...
0
votes
1answer
859 views

Postgres datetime sort slow and not using index

Using Postgres 9.4, I'm trying to order a query by an indexed datetime column. explain SELECT g.*, array_agg(gp.platform_id) as platform_list, array_agg(gm.metacritic_id) as ...
0
votes
1answer
685 views

Use serial ID for multiple columns and return it from INSERT

I am using Npgsql and I want to be able to do an insert, and if statement that will do an update and then return a value so when ExecuteScalar is called it reads that value. This was done fine in ...
0
votes
2answers
5k views

SQL command to clean job queue in pgAdmin 4

I have a Postgres 9.6 installation on a developer pc and a restore seems to have failed. Even after reinstalling (remove Postgres and PgAdmin completely, incl. database and installed new version) the ...
0
votes
1answer
37 views

Extract all rows until and including

I would like to extract all the rows from a table until and including check_type = 'schedule' and checked_at < '2016-09-26', in this situation I don't have a lower bound for checked_at, the lower ...
0
votes
1answer
85 views

How do I select the value with SERIAL data type in postgres [closed]

I want to know how to select values from a column that has SERIAL data type. I already try to do that but the output is like this 'Resource id #3', and not just '3' I use SERIAL so I dont have to ...
0
votes
3answers
49 views

Create a view that summarize vehilce owned from two tables cars and bikes [closed]

I have 3 tables owner, car and bikes where car and bike are dependent on owner. owner(_name,_ssn) cars(_ownername,_ownerSSN, model) bikes(_ownername,_ownerSSN, model) I want to create a view that ...
0
votes
2answers
30 views

Why does PostgreSQL FDW talk to multiple backend servers for this point query?

I've set up a PostgreSQL FDW server with the following table, sharded by user_id over four servers: CREATE TABLE my_big_table ( user_id bigint NOT NULL, serial bigint NOT NULL, -- ...
0
votes
2answers
41 views

Create integer id columns from existing string columns (integer coding?)

I have an PostgreSQL server with an existing table which has two fixed-width-non-unique-string (variable size) columns such as this: | ID_STRING_A | ID_STRING_B | | 'AAAA' | 'BBBB' | | ...
-1
votes
2answers
67 views

In Postgres txid_current() should return biginteger but instead an integer is returned

In Postgres 11, according to documentation, txid_current() should return biginteger but I'm getting small integer that doesn't look like is extended by epoch counter. My txid looks like this: 1123 "...
-1
votes
1answer
138 views

Indexing the condition lower(f) LIKE '%foo%'

How do I index the following statement with an un-anchored search pattern SELECT somefield FROM sometable WHERE lower(somefield2) like '%foo%'; Some rows have more than 2k bytes.
-1
votes
2answers
48 views

Create an unique integer id column for an existing string column

I have an PostgreSQL server with an existing table which has an fixed-width-non-unique-string column such as this: | ID_STRING | | 'ABCDEFG' | | 'HIJKLMN' | Now I want to compute integer ids for ...
-1
votes
1answer
2k views

postgresql function CAST json ARRAY to A TYPE

I try to cast a JSON to a TYPE VOID_EVENT_CREATE, but I didn't work because of the array. I'm going to use the query with NodeJS that why I would like to cast the JSON to a special type. It's easier ...