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
0answers
194 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). I'...
0
votes
2answers
38 views

Combining two similar queries into one (PostgreSQL)

I have 2 queries like this: SELECT c.id FROM component c, base_vee bv WHERE c.id IN (SELECT component_id FROM registry_row WHERE registry_id = 199) AND c.id = bv....
3
votes
2answers
151 views

What's the quickest way to periodically export PostgreSQL data to a local file on Windows?

I have access to a PostgreSQL database that I need to get data from for another system. The other system is running Windows 7 and does not (yet?) have PostgreSQL installed. Ideally, I'd like to have ...
0
votes
1answer
25 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
42 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
112 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
82 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 ...
4
votes
3answers
11k views

Permission denied in file trying to import

When I try to use \i on a file that is not in the psql.exe folder it says C:: permission denied. For example I have a file with SQL command at C:\Users\Work\Desktop\School Work\load_database.sql and ...
1
vote
2answers
231 views

Query for a table with paging and filtering vs. CTE (common table expression)

Intro In PostgreSQL 9.3: I am building a query that fetches data for a table that supports sorting, filtering and paging. Think Customers for example, you want to show name, surname, some detailed ...
1
vote
1answer
185 views

How to use values from table column as field names in SELECT query?

Scenario: Field names of Table1 named as testtable id,name,size,width,height Field names of Table2 named as errortable id,desc,field1,field2,operator Values of errortable +----+----------------...
0
votes
1answer
109 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
251 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 | ...
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 | ...
1
vote
1answer
40 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 {20000,80000,...
2
votes
2answers
88 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 SUM(...
1
vote
1answer
70 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 ...
4
votes
1answer
176 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
1answer
84 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: -...
0
votes
1answer
68 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 ...
60
votes
1answer
153k views

How to insert values into a table from a select query in PostgreSQL?

I have a table items (item_id serial, name varchar(10), item_group int) and a table items_ver (id serial, item_id int, name varchar(10), item_group int). Now I want to insert a row into items_ver ...
0
votes
0answers
126 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 ...
1
vote
1answer
136 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
35 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
0answers
57 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
75 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, avl_vehiculo....
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 ...
10
votes
2answers
9k views

Multicolumn index and performance

I have a table with a multicolumn index, and I have doubts about the proper sorting of the indexes to get the maximum performance on the queries. The scenario: PostgreSQL 8.4, table with about one ...
2
votes
1answer
312 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 ...
2
votes
2answers
368 views

How can I get PostgreSQL to not advance sequences when COPY fails?

I'm importing CSV files into a relatively simple PostgreSQL database. (The CSVs are sometimes created manually from information in a book, sometimes created from spreadsheets with a little massaging ...
0
votes
1answer
525 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
2answers
80 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
154 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
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
16 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
1answer
757 views

Print to console as SQL executes

When executing multiple SQL commands, such as in the pgAdmin SQL Editor, how can I print some strings to the console (output pane in pgAdmin). I want to monitor execution. UPDATE x SET a = b WHERE a ...
4
votes
2answers
461 views

How to reinstall PostgreSQL over an existing installation

I am trying to upgrade an installer created with NSIS that installs postgresql. It used to install postgresql 8.3 but we want to upgrade the DB to 9.4.4. The old version on of the DB used to be an ...
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 ...
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, C....
5
votes
3answers
4k 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
0answers
137 views

Creating and connecting to a postgresql server on ubuntu 15.10

I have been struggling with creating a postgresql server that I can connect to externally (using pgadmin3). I am installing it on a virtual Ubuntu 15.10 x64 server. I have been following the guide ...
0
votes
0answers
50 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 ...
1
vote
1answer
50 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%'' ') ...
4
votes
2answers
1k views

Do I risk losing the benefits of indexing if I have an index on every column?

I use PostgreSQL 9.2. I have a table with ~5 million rows and 150 columns. The table does not change at all (I replace it once a year). Users query this table with all kinds of filters on any some of ...
0
votes
1answer
44 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 ...
5
votes
3answers
112 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 ...
1
vote
1answer
112 views

Find unmatched rows between two tables dynamically

I have a function here that is supposed to take two tables as arguments and check if they are the same. create or replace function testing.equal_tables( varchar, varchar) returns void as $...
1
vote
1answer
262 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 ...
0
votes
1answer
152 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 precision,...
0
votes
1answer
24 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 ...