All versions of PostgreSQL. Add a version-specific tag like postgresql-9.4 if that context is important.

learn more… | top users | synonyms (1)

0
votes
1answer
95 views

Pass tablename as sql script argument

I'm trying to automatize a bunch of postgreSQL process, namely to use PostGIS and pgRouting extensions, so for that purpose I created a sql script file that I could run on command line passing in an ...
1
vote
0answers
74 views

Zero downtime copy/restore of a PostgreSQL database

I have a production server with static (no deletes/updates) PostgreSQL database, serving a huge amount of read queries. Once a week or two I have to update my database with new data. Right now the ...
0
votes
0answers
116 views

A “best practice” Postgresql function template for ETL processes

I use SQL Server in my day job, and have a personal project to build a star schema data warehouse in Postgres 9.4 on Ubuntu 14.04 to extend my knowledge (it'll be approximately 300GB uncompressed). ...
0
votes
1answer
24 views

postgresql trigger unexpected behaviour by many copies of inserted row

On insert in tbl1, the trigger I am working on, copies rows in tbl2 with some updates. Trigger: create or replace function myprocedure() returns trigger as begin IF (TG_OP = 'INSERT')THEN insert ...
1
vote
1answer
37 views

Adding new user in Postgres

I am using AWS RDS PostgreSQL and currently up and running. I would like to know how I can add a new user but only grant SELECT on an existing DB. The way I connect to my RDS from my PC is by using a ...
1
vote
0answers
81 views

Connecting Tableau and PgPool

I'm trying to connect Tableau to our database Postgresql via PgPool, for load Balance. I have Postgresql configured with Streaming Replication between one master and two slaves. And have another ...
1
vote
1answer
76 views

Discover missing foreign keys and/or indexes

We have a pretty big database (around 700GB of data), which has been inherited to us, and has had several DBAs. With time we've realized that there are several missing foreign keys and indexes. I'd ...
1
vote
1answer
33 views

how to do a count on a grouped by value in Postgres 9.4

I have a list of orders and would like to output a list by day of when they were created at of (1) total orders and (2)orders that have a fulfillment_status of 'paid' id | fulfillment_status | ...
0
votes
1answer
75 views

RHEL Postgresql 9.4 and PostGIS

I'm a dba new to postgresql. I have Linux RedHat 7 without openshift. I have to install some DB on 9.4 version with postgis. My sysadmin has published the following repositories : RHEL 7 server ...
0
votes
1answer
96 views

Microsoft SQL Server -> Postgres Data Migration

I have data in a Microsoft SQL Server that needs to be migrated to Postgres. The problem is that the SQL Server gets continuous updates and is hosted on our local net, while the Postgres instance is ...
2
votes
2answers
66 views

What parts make up pg_database_size?

I'm trying to write a munin plugin to graph DB sizes. Alongside using pg_database_size I want to graph the components thereof as well. So far, I've come up with the following: SELECT ...
1
vote
1answer
39 views

Understanding rows count computation

PostgreSQL 9.4 I have a table called customers which has a column income integer. After runnig ANALYZE against it I got the following statistic for the column: most_common_vals ...
0
votes
1answer
50 views

User cannot create schema in PostgresSQL database

I'm trying to setup a "deployment" user which can create and alter tables on an existing database in addition to selecting, updating, inserting and deleting records. Here is what I've tried so far: ...
3
votes
1answer
121 views

For each category, find the count of foreign-key items in all child categories using a PostgreSQL Recursive CTE

I have a typical tree structure stored as an adjacency list in PostgreSQL 9.4: gear_category ( id INTEGER PRIMARY KEY, name TEXT, parent_id INTEGER ); As well as a list of items ...
0
votes
0answers
77 views

psycopg2 - drawbacks of reusing cursors?

I am new to Python and the psycopg2 module. context Working on a script that select data from MySQL, perform some operations (conversion of data-types and other "transformations"), and finally ...
5
votes
1answer
223 views

Restrict Postgres roles by IP address outside of the pg_hba.conf file

I am currently adding Postgres support to a deployment tool that currently only supports MySQL. This tool dynamically provisions and configures a database, among other things. In MySQL, we can ...
1
vote
1answer
72 views

Get triggers/table names in PostgreSQL

How can I get a listing of all triggers for a database along w/ the table that it belongs to? I assume that I need pg_trigger and pg_tables but I'm not quite sure how to join them together.
0
votes
0answers
33 views

Which would be a recommended approach for migrating a Postgresql database from one server to another?

We are moving out of our current dataserver based on Santiago, Chile to Amazon (AWS) in North Carolina, all other servers have been successfully migrated with no problem, but our "migration consultant ...
1
vote
1answer
59 views

Join external data in SELECT query

I have few (~20) names of products corresponding to an integer id that needs to be joined on products while exporting products data (using COPY (<query>) TO). The burden is that these names do ...
0
votes
0answers
18 views

What command should I use to make my void function display a paticular table using a variable from the function in the where clause?

I basically have a void function that creates a tuple on a existing table. Now at the end of the function I want display the table with the updated tuple. I am running it problems when trying to do ...
0
votes
2answers
72 views

SELECT fields from table having some conditions

I have two tables 1. Named as t1 2. Named as t2 example of the tables are : t1 : id name 1 one 2 two 3 three t2 : id name 2 five I need to select all the data ...
0
votes
1answer
18 views

First field from my return table is being eaten up

Another problem with some PostgreSQL stored procedure code... Same from this question: I have this function drop function uptimes(inicio date, fim date); create or replace function uptimes(inicio ...
0
votes
1answer
102 views

postgres window functions: using CASE to partition by age group

I am using postgres window functions to get a list of users taking part in a competition and their corresponding ranks based on a number of columns, all good so far....now I need to get rank based on ...
0
votes
0answers
15 views

PostgreSQL dump import only imprts tables and not data

I'm trying to import a postgreSQL dump into my DB. The dump is saved in a .sql file. I use the following command : psql -U postgres -d dhris < dbDump.sql and the table was created before. The ...
1
vote
0answers
56 views

Which indexes should I create?

I need assistance in which indexes to create on a table, and the best approach for querying it. We use PostgreSQL. Here’s the table: create table myt ( the_time timestamp, oid int, ...
0
votes
1answer
180 views

See entire string from a lengthy text field in pgAdmin?

Using pgAdmin, how can I see the entire string value for a row when the value is very long? For example, the value may be comma-separated values (CSV) content with hundreds or thousands of ...
0
votes
1answer
189 views

Simple select query taking too long

I have been having a hard time with this query, I have a webpage which is supposed to run this query a dozen of times, and with an execution time up to 40 - 50 seconds per query, you can see how this ...
1
vote
0answers
27 views

PostgreSQL: labels vs columns

I have a data structure similar to this: date foo bar baz boo ---------------------------------------- 2015-11-18 1 null 2.5 81.0 2015-11-19 1 78.4 12.8 ...
2
votes
1answer
178 views

“ERROR: there is no parameter $1” in “EXECUTE .. USING ..;” statement in plpgsql

I have a plpgsql function to create child tables using table inheritance in PostgreSQL like this: CREATE TABLE parent_table ( value integer, end_time timestamp without time zone ); CREATE OR ...
0
votes
1answer
47 views

Is there a way to optimize this using indexes?

I'm just now learning indexes, and I was wondering if it's possible to optimize this code using them. Currently this code runs in 1.4 seconds. SELECT C.yearID as year, name as teamName, ...
0
votes
1answer
40 views

Designing a database where records can reference different foreign keys

I can use PostgreSQL or Microsoft SQL, whichever provides the needed capabilities. I need help designing a database that has records that can reference FKs in different tables. Given the following ...
0
votes
0answers
40 views

Postgres 9.3.9 problems

I have a problem with my database backup. I was unable to find solution so I decided to ask :) Situation: Database (Postgresql 9.3.9 with PITR set up) was runinng about 3 months. I downloaded backups ...
5
votes
3answers
2k views

Where column not like multiple values

I am trying to select records in a postgresql db where username is not like a list of strings. SELECT * FROM rails_db WHERE username NOT LIKE 'j%' AND username NOT LIKE '%eepy%'; The issue is there ...
1
vote
1answer
46 views

Delete query from MSSQL 2008 to linked Postgres [closed]

i want to delete some data from MSSQL 2008 in linked PostpresSQL. Iam find some solution --DELETE data DELETE FROM OpenQuery('MSDASQL','SELECT name FROM sometable wHERE name LIKE ''test%'' ') ...
0
votes
1answer
39 views

Restrict root user access to postgres user [closed]

I know this is a little weird but bear with me. If a hacker gains access to a server's root user, he can type "su postgres" and then "psql" and boom - instant access to everything. I was wondering ...
0
votes
1answer
72 views

Improve performance of query with many joins

From the following explain plan: explain SELECT imei, cliente_avl_equipo.id_cliente AS idcliente, cliente_avl_vehiculo.grupo AS grupo, avl_vehiculo.id, ...
0
votes
1answer
86 views

DECIMAL and NUMERIC datatype in Postgres [closed]

what is the use of decimal and numeric datatype in postgreSQL. As per the reference the following is the explanation given to these datatypes. decimal,numeric --> It is a user specified ...
0
votes
1answer
64 views

Change postgresql data directory location

Currenly my postgresql service running with /usr/local/psql/bin/postmaster -d /usr/local/pgsql/data Due to insufficient disk space (data directory having 10GB of data), I planned to do the ...
1
vote
2answers
57 views

DENY insert,update,delete to user (PostgreSQL)

I wonder if Postgres support the DENY command. I search in the documentation but I couldn't find anything about deny. I have GRANT ALL ON table1 TO user1 WITH GRANT OPTION but I don't want user1 to ...
5
votes
3answers
109 views

Aggregate discarding values in one column that haven't a match in another column

Say, I have a table representing colored and labeled items inside numbered boxes. Each box can not contain more than one item with a particular label, but items with the same label (and the same or a ...
0
votes
1answer
23 views

DO INSTEAD in postresql Rules

I have a simple table like this : CREATE TABLE gateway_text ( id BIGSERIAL NOT NULL PRIMARY KEY, text TEXT ...
0
votes
0answers
35 views

Send Keyboard Interrupt in PostreSQL psql console?

Is it possible to send a keyboard interrupt signal to the PostgreSQL psql console that will take me back to a new console command line? Sometimes I'll type a command incorrectly and then hit enter ...
0
votes
1answer
206 views

Sessionizing Log Data Using SQL window functions: tweaking a query (incrementing session ID and event length)

I'm referencing to a very good blog entry, that describes how to sessionize log data with SQL window functions. I'm no database pro and I don't really understand the logic behind this query (you can ...
1
vote
1answer
96 views

How can I select records which are between 2 dates based on a date in a field in PostgresSQL?

I have two tables: 1 with an Unique ID with the first time they visited (in date format) and a historical addresses table with the same Unique ID, a start field (date field), an end field (date ...
1
vote
0answers
32 views

Enterprisedb, PostgreSQL cast to date

How to cast '2015-11-13 00:00:00' (text or timestamp) to '2015-11-13'(date) in Enterprisedb (EDB) ? With 4 queries below: 1/ SELECT '2015-11-13 00:00:00'::date ; --> result: "2015-11-13" 2/ ...
1
vote
1answer
39 views

Reset postgres password v9.1

I'm trying to reset my lost password for Postgres v9.1 (windows 7) like written at https://wiki.postgresql.org/wiki/FAQ#I_lost_the_database_password._What_can_I_do_to_recover_it.3F : 1. edit ...
4
votes
1answer
118 views

How to convert a Postgresql ltree structure to a nested set by using VIEW?

I would like to ask a question about a way for transforming a PostgreSQL ltree structure to a nested set structure with only one query by using only VIEWs. For example, I have a table which has data ...
1
vote
1answer
40 views

How to optimize query with max on Postgres?

I'm trying to make a relatively simple query, but it's taking much longer than I'd expect. I have an index in place, but it doesn't seem to be helping much. Here's the query. It takes over an hour ...
0
votes
0answers
124 views

Could not locate a valid checkpoint record postgres

I have a database postgres in windows server and two slaves for reading. I need do Incremental Backup. What I've done: - Create Base Backup from pg_basebackup - Save WAL files Restore What ...
0
votes
1answer
18 views

Returning results from many functions / statements

I have a function that I will be running 10-50 times at once: importsitechanges(33, 209703, 'Store Name', 'Addr', 'city', 'state', 'zip', 'phone') This function that I made returns the number of ...