The tag has no usage guidance.

learn more… | top users | synonyms

2
votes
0answers
15 views

How can I drop an index on all partitions?

If I create an index on a partitioned table, each partition gets its own index. These are not dropped if I drop the main index. Is there an easy way to do this? I have written a function that looks ...
1
vote
0answers
23 views

When was a table created? [duplicate]

Is there any way to find out when a table was created in PostgreSQL or Greenplum, other than finding the underlying table and looking at the OS creation timestamp?
0
votes
1answer
28 views

Is a partition a relation?

Is a partition a relation in its own right, or is a partitioned table a single relation? Specifically, do we need to increase max_fsm_relations when we split tables up into loads of partitions?
0
votes
3answers
33 views

How to accomplish local joins between facts and dimensions?

A fact table typically contains a composite primary key made up of the keys to all the dimensions that define its granularity. if we distribute the table by this key, it cannot possibly accomplish a ...
1
vote
1answer
37 views

Get total size for all Postgres or Greenplum tables including partitions

Is there an easy way to get the total size of all tables including partitions? I've written this query for Greenplum, but it's a bit round-a-bout: with part_sz as ( select ts.sotdschemaname sch ...
0
votes
1answer
28 views

Slow select from one billion rows GreenPlum DB

I've created the following table on GreenPlum: CREATE TABLE data."CDR" ( mcc text, mnc text, lac text, cell text, from_number text, to_number text, cdr_time timestamp ...
1
vote
1answer
113 views

How can I add multiple partitions to a Greenplum table using dynamic SQL

I have a function that adds a partition to a table, that works just fine. It takes a schema name, a table name, and a date, and generates and executes an ALTER TABLE statement to add a partition for ...
0
votes
0answers
81 views

How can a select max query return no rows?

I have a process that selects the maximum date from a Greenplum (PostgreSQL 8.2) table, and coalesces it to a default value. Once, and once only, this query returned 0 rows. How is this possible? Even ...
1
vote
1answer
64 views

Why does dynamic SQL behave differently in SELECT FROM than in SELECT in Greenplum? [closed]

We have a function that executes dynamic SQL (I know, SQL injection, the "Bobby Tables" attack, but that's a separate issue). It takes two parameters, interpolates the first into the second, and ...
3
votes
1answer
96 views

Does Greenplum support dynamic sql?

PostgreSQL implements the execute ... using option to pass parameters into dynamic SQL, and as far as I can tell this feature was introduced in version 8.4. We are using Greenplum, which is forked ...
-2
votes
1answer
494 views

How to update values from bytea to varchar in Greenplum?

I need to convert hexa to values and need to update the values in a varchar field, I have tried: select pg_catalog.decode(column,'hex') The above code converted hexa to values but when I try to ...
1
vote
2answers
2k views

How to calculate the cumulative difference between rows in Postgres?

I am in need of calculating the difference from the cumulative values for the previous day in Postgres based on session_id. Example as mentioned below: Date session_id upload_usage ...
0
votes
0answers
121 views

Unable to exit a Greenplum SQL script

Is there any way in psql (Greenplum flavour) of exiting a script? I have tried with \q and \quit, e.g. \echo "Hello"; \q -- or \quit but I get: my_database> \i my_script.sql ...
1
vote
0answers
324 views

Move data from a Greenplum database to Oracle

my company is migrating its Data Warehouse from Oracle to Greenplum. The migration is gonna be progressive, that is they will migrate the ODS first and at a later stage the DDS as well. I would like ...
1
vote
0answers
300 views

How to make Greenplum 4.2.3 only scan the intended partition?

When I uses unnest() in a View, and uses that View in a select statement, Greenplum seems to fail in only searching for the intended partition and search through all the partition of the main table ...