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
241 views

What role for plpythonu function's in the file system?

As a preliminary test for a further work I'm trying to use a simple plpythonu function in Postgresql 9.2 to create a folder in my filesystem. So I have this code : CREATE OR REPLACE FUNCTION "...
0
votes
0answers
50 views

Increasing system load due to multiple inserts every millisecond

In my project I need to do multiple database inserts every millisecond and thus Postgres takes huge amount of CPU. I need to find a way to reduce CPU load as I need to process about millions of rows. ...
0
votes
0answers
289 views

Performance: Postgres (Read-Committed) vs MySQL (Read-Uncommitted)

I have a workload that involves very frequent writes and reads. It is very performance sensitive, but not accuracy-sensitive. Hence, to maximize performance and minimize locking-delays, my plan was to ...
0
votes
2answers
101 views

Elements inside a container: How to perform atomic operations on them?

In my DB (Postgres 9.4) I have the following items: Tshirts, Jeans, Shoes and Boxes. Tshirts, Jeans, Shoes can be placed inside a box and they can have a color (the color may be null). Each element ...
0
votes
0answers
64 views

Improve performance of count(*) with two exists

I need some help improve performance of this query that runs in Postgresql 9.4 at Amazon RDS: SELECT count(*) AS total FROM people WHERE EXISTS ( SELECT 1 FROM person_values WHERE (...
0
votes
1answer
41 views

Assign value to a field after checking a condition

I have this query: ALTER TABLE MERCHANTS ADD COLUMN merchant_image bytea, ADD COLUMN merchant_logo bytea, ADD COLUMN merchant_address VARCHAR(100), ADD COLUMN merchant_phone VARCHAR(50), ...
0
votes
0answers
42 views

what happens to WAL files when standby server is in recovery

in the case where we have SERVER1 (prod db) and SERVER2 (hot standby), what happens to the WAL files from SERVER1 when SERVER2 goes into recovery mode and is opened? On SERVER1, the ARCHIVE_COMMAND ...
0
votes
1answer
79 views

How round in Postgres?

when we use SUM on a data table gives a 1 or 2 cents of difference due to rounding in Postgres. We are using Postgres 9.4, the field data types is numeric with 19,3 and the result have to be with two ...
0
votes
0answers
70 views

Rule of Thumb on OS Free Space

This may be a difficult question to answer, but I have a PostgreSQL database running on a Linux server with 1.3 TB of storage. I'm currently at 73% utilization of storage space with a slow but steady ...
0
votes
0answers
20 views

Create a Database locally and run it on a cluster?

I have a very basic question. I have to create a database locally on my computer, that includes tables, stored procedures and so on... I am confused as to how I would place this database on a cluster. ...
0
votes
0answers
199 views

postgres.exe memory consumption

I'm converting a sql server database to postgresql 9.4. One of the database process that was working fine is causing trouble in PG. postgres.exe memory consumption keep going up each time a new ...
0
votes
1answer
79 views

Multiple select for single row insert

I need to insert a row into a table by selecting value from multiple tables. I want a row to be inserted from multiple tables. insert into tab(id,name,date) values ((select max(id)+1 from tab),(...
0
votes
1answer
100 views

Copy postgis layer from S3 to Heroku

I have a dump of a postgis layer (layer.dump), which I am trying to add to my heroku database (mydatabase). The postgis layer is stored on S3 (https://s3.amazonaws.com/layer.dump). I would like to add ...
0
votes
0answers
647 views

Connection Failure for postgres at Talend Open Studio

I have tried to connect my PostgreSQL database in Talend Open Studio. After giving a login name, password, port and server, I tested the connection. It's saying "Connection Failure. You must change ...
0
votes
1answer
63 views

PostgreSQL failed on Select

I am trying to use a result from a select for another select: DECLARE _port int; BEGIN select alter_port into _port from database where ....; select data inot data_list from database where ...
0
votes
1answer
93 views

Stored procedure for adding cascade

I have a DB with 180 tables, and I was doing a logical deletion, now I need to do some testing with physical deletion. Is there a way to alter all my tables and to drop their fk's and add then add ...
0
votes
1answer
991 views

Convert two columns to json

I have a json column with a json array in the form of [{key : "key", value : "value"}, {key : "key2", value : "value2"}]  that I wish to convert to a new form {"key" : "value", "key2" : "value2"}....
0
votes
1answer
2k views

how can I count by week, month, and YTD with postgres?

under Postgres 9.3 I am trying to run a query to summarize some data from table a: | Entry date-Date | Customer ID-charvar | received-Boolean | Processed-Boolean | finalized-Boolean | billed-Date | ...
0
votes
0answers
1k views

Getting TEXT data from postgres throught linked server to SQL Server

I'm using SQL Server 2008 R2 with linked server to postgres 9.4. My problem is: when I select column with data type text it shows me only XXX first chars and adds (...) on the end. select ...
0
votes
0answers
201 views

PostgreSQL becomes very slow after heavy utilization

I have built a Python web application and I am hosting it in a 2GB RAM Debian Server. My application uses PostgeSQL 9.1 with PostGIS. One of my tasks involves importing data from big csv files. ...
0
votes
4answers
2k views

Insert from Another Table + Removing Duplicate Entries in Postgres

I'm trying to insert data from one PostgreSQL table to another while removing duplicate entries but I'm having some trouble with the code. I have two tables I'm working with: mytesttable, which ...
0
votes
1answer
112 views

Postgres INNER JOIN same table failing on CartoDB

I have a table with data which looks like this +----+------+-------+ | cp | year | count | +----+------+-------+ | 1 | 2000 | 10000 | | 1 | 2001 | 9000 | | 1 | 2002 | 9500 | | 2 | 2000 | 8000 |...
0
votes
0answers
230 views

PostgreSQL 9.3 - Move table to tablespace - Slow?

I need a help with moving table to a different tablespace. I have a really large table that is not queried much often so I've decided to move it from tablespace on SSD to tablespace on HDD and save ...
0
votes
1answer
111 views

Problem with UNION ALL

I have to process 200 points (lat, long) to calculate the minor distance of same object (street in this case). So, I made a simple query to obtain 1 result, and replicate the same to the other 199. (...
0
votes
1answer
69 views

Postgres DB user permission

I'm logged in as a user in postgres with PgAdmin tool in Linux connecting remotely but I don't think the user has enough rights. I'm a MySQL experienced user but not so much in PostGres. When I select ...
0
votes
1answer
208 views

Point PostgreSQL on Ubuntu to a new datadir

I have a new Ubuntu 14.04 install on a new drive, with the old main drive containing a PostgreSQL install from the previous Ubuntu 14.04 install. I've been trying to move the PG dbs from the old ...
0
votes
0answers
41 views

Optimize query with access to tiny fraction of data

I am trying to optimize some queries but I can only use a tiny portion of the actual data they will be executed against later. Probably the query optimizer will generate completely different plans ...
0
votes
0answers
46 views

doing a insert while selecting an specific user

I'm importing and legacy database to a new database, and changing data format. In this legacy database, I have data as following: Interaction id: 5458d717cd07870859000004 user_id: ...
0
votes
0answers
81 views

Postgres Query Optimisation (Rails App)

I have a very slow running SQL query that is run from my Rails app using the Model.find_by_sql method. I've made a number of optimisations but it's still extremely slow. It would be great if anyone ...
0
votes
0answers
173 views

how to migrate data between two distincts tables

I have a database with millions of records in a table users. I need to collect this data, tranform to a new format (removing a field, for example) and save it to another database. I'm currently doing ...
0
votes
0answers
27 views

Does installing some PostgreSQL options create some related tables?

After restoring an old pgsql database tables I realized that it contains tables like pg_ts_cfg, pg_ts_dict, geometry_columns and spatial_ref_sys. I can't remember creating those tables and I wonder ...
0
votes
0answers
884 views

How to run pg_bulkload on windows?

Is it possible somehow to run the postgres batch tool pg_bulkload on windows? I could not find any documentation on this, only for linux. Though it should be possible as the docs state: If you use ...
0
votes
0answers
176 views

Heroku Postgres Follower

I'm currently looking at creating a follower db of a live production database. The follower will be used for analytics purposes only. And will not be inserting any data - if it did then it would be by ...
0
votes
0answers
457 views

To split tables is a good idea?

I have a table with more than 40 million rows and I want to know what is the best way I can optimize it for searches. The search will happen by the person's document number, and these document numbers ...
0
votes
1answer
58 views

Limit the number of votes

I have a table of content. Users can vote on a content row. They can vote up to three times. I'm looking for the most efficient way to make sure they don't go over three votes. I can think of two ...
0
votes
0answers
835 views

How to display additional column in select with true/false value [SOLVED]

Solved. Look in comment. I am using PostgreSQL 9.1. I have 3 tables: simcards: id serial NOT NULL, iccid character varying(30) NOT NULL, customer_id integer customer: id serial NOT NULL, name ...
0
votes
0answers
46 views

PostgreSQL: raise error if planner needs to do an sequence scan

I want to check if our code does use only indexed columns. I think it does, but I want to be sure. How can I configure/modify PostgreSQL to raise an exception if it needs to do an sequence scan? It ...
0
votes
1answer
127 views

Inaccurate count when using multiple count functions in PostgreSQL

PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), Redshift 1.0.890 I'm relatively new to SQL and am learning a lot of about writing queries as in ...
0
votes
0answers
504 views

Multilingual Article/ Category database design

I wasn't getting any answers over at Programmers and was advised to go over here instead... I wanted to make a design for a multilingual database and after having drawn it up have arrived at 5 tables,...
0
votes
0answers
1k views

installing pgagent on centos

I have installed pgagent on centos. Now I am trying to connect it to postgres database. I ran: /usr/bin/pgagent_94 -l 2 host = 127.0.0.1 dbname = postgres user = postgres port = 5432 password=mypass ...
0
votes
1answer
112 views

Migrate MySQL 3.23 Data

I have .FRM, .MYD and .MYI files from a MySQL (version 3.23 I think) database from back in the early 2000's. I managed to get the files into a Windows installation of 3.23 and then dump it, however I ...
0
votes
0answers
172 views

New Table vs Java Beans to allow user to dynamically change data representation?

I am building a database to store the assessment records of students, along with other relevant information and functionality like reporting on average scores etc. Currently my company uses Excel for ...
0
votes
1answer
429 views

Behaviour of Bulk Insert in pgsql or any Database

I'm using PostgreSQL 9.2.4. I'm working with multiple tables with large number of data. I have two tables Table_One which has one of the field with varchar(250) and Table_Two with one of the field ...
0
votes
0answers
424 views

Slow query over 10mil rows with group by on indexed column

I've got a slow query which I want to optimize. SELECT pand.bouwjaar AS construction_date, count(*) FROM pand AS pand WHERE pand.begindatumtijdvakgeldigheid <= 'now'::text::timestamp ...
0
votes
0answers
857 views

PostgreSQL trigger copy record to another table with different columns order

I have a trigger that before every update on hundreds of tables it inserts into a table with the same name and the _hv suffix a copy of the record being updated to maintain a kind of historical ...
0
votes
0answers
97 views

List all columns and rows of PostgreSQL table with a view limit

I am currently using select * from items; to show my table during development however I am finding it a bit messy now as some the strings I am adding to the table are 1,000+ chars long. Is there a ...
0
votes
0answers
1k views

Postgresql: Update or insert stuck

I'm running update-or-insert query on a table of approximatelly 3mln rows, 4 columns. Query is part of every day process, and for same days, the query stucks forever. It's just blocked. It's ...
0
votes
0answers
4k views

pg_hba.conf and postgresql.conf in old installation directory

I have installed the PostgreSQL 9.4 on my MacBook via MacPorts, and I am trying to edit pg_hba.conf, but I don't see it in my current installation. Furthermore, psql is reporting that config_file is ...
0
votes
1answer
5k views

Select on multiple tables with group by

Using Postgres 9.3, I have problem with a select over few tables. I have simcards stock and I would like to get something like this: | network | supplier | total topups (EUR) | 1st topup qty| | ...
0
votes
0answers
163 views

Do Statement-level AFTER triggers occur asynchronously after the statement returns?

I'm using an AFTER INSERT OR DELETE OR TRUNCATE ... FOR EACH STATEMENT trigger on a table to refresh a materialized view, and I'm concerned that it will significantly affect the performance of those ...