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
1answer
32 views

Feasible to have thousands of foreign data wrappers in Postgres?

We are creating enterprise-grade SaaS where we will have many 1,000s of customers. We are considering creating a database in the Postgres server for every customer to ensure enterprise-grade security/...
1
vote
1answer
51 views

How to filter multiple many to many relationship based on multiple tags?

I've a scheme of two main tables: problem and tag and a relation (which is a many to many connector) table: problem_tags which excerpt of them is like: Problem table: ----+---------------------------...
0
votes
0answers
14 views

Removing postgres connection installed with EnterpriseDB on MacOS

I had earlier installed Postgres using Enterprise DB which is installed by default at location: /Library/PostgreSQL/10 However, because of some database issues I had to uninstall it. I followed all ...
0
votes
0answers
16 views

How to import multiple CSV files to a postgres table using pgadmin?

I'm looking to import a folder of over 100+ csv files into one table on PostgreSQL using pgadmin. I have the following script but is there a way to change the file path to all csvs? COPY schema.table ...
6
votes
4answers
39k views

psql denies access to file when using COPY FROM

When I do this: COPY "mytable" FROM '/my/file.csv' WITH DELIMITER AS ',' CSV; On this: -rw-r--r-- 1 peter peter 54819176 2011-07-21 13:17 file.csv psql tells me this: ERROR: could not open file "...
0
votes
1answer
102 views

operator does not exist: timestamp with time zone + integer in PostgreSql

I came across with a strange issue in postgres. I have two databases create in two different periods. Both are running in the same PostrgreSql 9.6 version in the same machine. When I run the query ...
0
votes
2answers
6k views

Unix domain socket “/var/run/postgresql/.s.PGSQL.5432” missing

I try to run an application where its database on postgresql. It won't work and I'm getting the following error whenever I try to interact with the Postgres service: could not connect to server: ...
10
votes
1answer
15k views

Getting a random row from PostgreSQL?

I want to get a random row from my table by id. My table: ID|Word |Dificult|Category_id| 1 |'Dumb' |'Easy' | 3 | 2 |'Leopard'|'Medium'| 6 | If my user selects a category and ...
8
votes
2answers
3k views

Getting SQLSTATE in psql error messages

I have the following problem. SELECT * FROM tgvbn(); ERROR: function tgvbn() does not exist LINE 1: SELECT * FROM tgvbn(); ^ HINT: No function matches the given name and ...
2
votes
1answer
138 views

How to import data from a dump file to a specific schema in postgres?

Let us assume there's a database name myDataBase. There are four schema in this database named 1: schema_1 2: schema_2 3: schema_3 4: schema_4 I have created a dump of schema_1 from this command ...
0
votes
1answer
19 views

Count percentage of column with GROUP BY

Consider the following query: SELECT country , count(id) AS "count" FROM lead WHERE date_part('year', lead.since) = date_part('year', CURRENT_DATE) GROUP BY "country" ORDER BY "count" DESC ; ...
33
votes
3answers
112k views

How to get the timestamp column in only milliseconds from PostgreSQL?

I have a column "created" with type timestamp without time zone default now() in a PostgreSQL database. If I select colums, it has a nice and readable format per default: SELECT created FROM mytable;...
12
votes
2answers
81k views

Permission denied for relation <table>

I have ran the following SQL in psql: CREATE USER bspu LOGIN; CREATE DATABASE bsp OWNER bspu; GRANT ALL PRIVILEGES ON DATABASE bsp TO bspu; \c bsp CREATE TABLE users ( id SERIAL PRIMARY KEY, ...
0
votes
0answers
18 views

PostgreSQL limiting number of rows for a certain condition

arena_stat_id | user_id | date_set | level | trophies ---------------+--------------------+----------------------------+-------+---------- 1 | ...
0
votes
0answers
16 views

Why isn't autovaccum running/completing?

My PostgreSQL instance (AWS Aurora) isn't autovacuuming. Or it is, but it is somehow being interrupted or stopped. I'm not canceling any autovacuum queries (though I suppose AWS itself could be). The ...
0
votes
0answers
13 views

Function in Postgres that uses the table where I'm passing the function inside the function body?

I've got two tables, one with street_codes and street_numbers (addresses) and another with sidewalks_keys that correspond to each address (address_sidewalks_keys). I would like to create a function in ...
1
vote
1answer
10 views

Can PostgresSQL cluster have synchronus and asyncronus standbys at the same time for the same dbs?

In this question, Write concern in PostgreSQL Laurenz Albe explained how to control the set of standby servers when data is committed to PostgresSQL, and that gave birth to another question. What if I ...
0
votes
1answer
26 views

PostgreSQL covering index [closed]

I'm using Postgres 12. Let's say I have a table player with 2 columns: first_name and last_name (along with the RowID) with 3 rows first_name. last_name RowID paul pogba ...
-1
votes
1answer
9 views

how can I fake/mock the postgres now function to behave like freeze_time

Basically would like to be able to mock out the time functions to return a time I want for unittesting.
0
votes
0answers
23 views

Postgresql - slow distinct on million records table

I have a table with a million of records, i need to select the distinct values of log_type. In other queries i also need to filter log_types result of the distinct and limit the result. But the query ...
0
votes
0answers
9 views

Polymorphic association in PostgreSQL [closed]

I have three entities video_guide text_guide and discussion that represent type of learning material available on the platform. A student can create topic-wise boards and add learning material to them....
0
votes
1answer
18 views

Data migration: copy data with insert select and fill unique column

I have some tables in Postgres like this. create table foo ( txt text ); insert into foo (txt) values ('a'); insert into foo (txt) values ('b'); insert into foo (txt) values ('c'); create table ...
0
votes
1answer
45 views

relation “table” does not exist

i created the following schema , inside that schema i created a table and inserted a some values. After that i created a function that get a value in the table based on an ID. Here is all the ...
1
vote
1answer
5k views

Error Receiving WAL files with barman

I'm having a similar issue reported here but no solutions have helped: Unable to receive WAL files with barman Trying to get streaming replication to work. PostgreSQL 9.6 on the master. Barman 2.3 ...
2
votes
2answers
40 views

How to determine or predict space on disk per table row?

I'm very new to Postgres so my math could be off here ... This is my table: CREATE TABLE audit ( id BIGSERIAL PRIMARY KEY, content_id VARCHAR (50) NULL, type VARCHAR (100) NOT NULL, ...
0
votes
1answer
160 views

Barman: What's the best way to switch from backup_method postgres (streaming) to rsync (ssh)?

My production postgresql server is currently backed up with barman using the backup_method postgres (streaming server). I would like to switch to rsync method (ssh) to benefit from better compression. ...
2
votes
2answers
256 views

Postgresql return results of dynamic SQL

I'm using Postgresql, version 12. My goal is to create a function, or stored procedure, which generates a dynamic select which is returned by the function or stored procedure. The difficulty is that ...
0
votes
2answers
741 views

Psycopg2 Errors on SQL statement when trying to copy data from CSV file into PostgreSQL database

I am not a developer or PostgreSQL DB admin, so this may be basic questions. Logistics: Windows 10 server / pgAdmin 4 / Postgres 10 / Python 2.7.13 I'm using a python script to ingest external data, ...
0
votes
1answer
335 views

How to filter a value in json field type on Postgres 9.2?

My json field data is like this: {"active":true,"id":"xxxxxxxxxxxxxxxxx","settings":{"secret":"xxxxxxxxxxxxxxxxxxxxxxxxxx","token":"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx","expires":"2019-12-16 01:11:23"},...
3
votes
1answer
3k views

Maximum sum() of overlapping ranges

Essentially my question is: how does one do aggregate operations involving overlapping ranges in PostgreSQL 9.3 (or 9.4)? The specific problem I have at hand is that given a range, I want to find the ...
1
vote
0answers
22 views

How to return most recent element with distinct subquery

I have such query but it returns duplicated rows for same speaker SELECT "speakers"."id", "speakers"."name", "speakers"."surname", "speakers"."photo_url" as "photoURL", "talks"."...
5
votes
1answer
9k views

Drop table taking too long

I have a table with approx. 200 million rows (approx. 0.5 TB) and I want to drop it, but it is taking a really long time. It's been running for 2 days now. I suspect the rollback function to be the ...
0
votes
1answer
101 views

Can't create new tablespace for PostgreSQL data. Permissions denied

For some reason I don't seem to be allowed to set my PostgreSQL data folder to a drive other than the C drive (Windows 8 machine). 1) If I try to do so when installing PostgreSQL, the installation ...
1
vote
1answer
30 views

Write concern in PostgreSQL

In MongoDB it is called write concern, that is the number of the servers in a replica set, that the data should be committed before the cluster acknowledges the write request. How is it called in the ...
3
votes
3answers
12k views

How to create and populate a PostgreSQL table from CSV automatically?

I am new to database management and we are using PostgreSQL. All I need to do is to migrate CSV files (corresponding to around 200 tables) to our database. Manually creating tables for every CSVfile ...
1
vote
2answers
23 views

Show values from list that are NOT returned by query

I have been given a list of values that may or may not be found in a table's column. I just need the subset of the list that is NOT found in a SELECT query. Pretending that the list I was given is: '...
1
vote
1answer
2k views

When exactly does pg_stat_all_tables.last_autovacuum get updated?

I'm looking into issues with our autovacuum settings and trying to understand last_autoanalyze and last_autovacuum from the pg_stat_all_tables. I understand autovacuum to work in an incremental way, ...
1
vote
1answer
32 views

Why is my indexed DISTINCT ON so much slower than my INNER JOIN?

I have two tables, customers and purchases. There are a lot (thousands) of purchases per customer. I usually only need the most recent purchase for each customer, which is why I have the ...
0
votes
1answer
24 views

Postgres foreign data wrappers weird join behavior

So I have tables in two different databases that I need to analyze together. I am using fdw to do so. However I am running in to weird behavior with my joins. Keep in mind the following snippets are ...
0
votes
1answer
100 views

Calculate the duration in an interval and outside of events that can last over several days

I need to calculate the total duration of events in a given time interval and on a given day of the week with PostgreSQL 11 For an event on the same day no problem. But over several days I manage to ...
0
votes
0answers
7 views

Library that can generate random geospatial data in GeoJSON

Is there a library that can help to quickly generate random geospatial features in GeoJSON format (preferably, but can be anything), based on the parameters that we pass in. For example - generate ...
2
votes
1answer
44 views

Best indexing strategy for spatial join and ranged lookup

Context We have two tables (not the real tables used in the examples below. They are from a toy DB im using for testing): Incidents_2 (Columns of interest are a geom, and a reported_at (int8)) ...
2
votes
1answer
11k views

Row with latest timestamp

How to get the row with the latest value in a TIMESTAMPZ column? Is an index needed? Does an index change the strategy? Would behavior vary by database (I'm using Postgres 9.4)? My app records data ...
6
votes
3answers
428 views

`pg_restore` with `jobs` flag results in `pg_restore: error: a worker process died unexpectedly`

I have a script which runs the following: pg_restore tmp/latest.backup --verbose --clean --no-acl --no-owner --dbname hub_development --jobs=12 This frequently fails with the following error: error:...
0
votes
1answer
22 views

Migration path to unify column names in backward compatible way

We have several naming schema for columns (like id/key or created/creation_date/create_date) and want to unify it. Also we want to do it in backward compatible way. We want to be able to run old code ...
0
votes
1answer
19 views

Manually trigger “Write current results to disk” during query

Using PostgreSQL 12, I have the requirement that some data needs to be "refreshed" each night. That means: The database has to do some calculations based on the current date and write the result to ...
0
votes
1answer
27 views

Prevent execution of heavy-loading and / or runaway queries in postgres

Is there a way to stop/kill queries being executed on a postgres database based on criteria such as: maximum execution time load (CPU and/or memory) imposed on the server?
1
vote
3answers
37 views

Pre-filtering on right table in a LEFT JOIN

I have the following schema/query where I basically need to filter parent table by some criteria and then collect aggregated data from its child table(s): create table parent (id integer primary key, ...
2
votes
2answers
152 views

Getting Postgres CREATE TABLE statements

I created some tables (9, to be exact) with a Django (1.9.6) migration and now I'm trying to get simple CREATE TABLE statements for them. I tried this answer, but using pg_dump in this way gives me ...
1
vote
1answer
50 views

Unable create partition by trigger

1) I have table CREATE TABLE tor ( events_date timestamp without time zone NOT NULL, message text CONSTRAINT tor_pkey PRIMARY KEY (events_date, message) )PARTITION BY RANGE (events_date); 2) ...