Questions tagged [greenplum]
All versions of greenplum. Add a version-specific tag like greenplum-4.3 if that context is important.
39
questions
1
vote
1
answer
25
views
Merging wifi session data if time between them is less than 15 minutes
I am trying to process network logs and join sessions together if the time between them is less than 15 minutes. The relevant fields are start time, end time, mac address, and wifi access point.
I am ...
- 13
0
votes
0
answers
31
views
Incorrect results when using group by date_part
We have a table with a date field and ids. A simple query like this returns the correct result:
SELECT count(DISTINCT id_field) AS distinct_count FROM schema1.table1 WHERE date_part('month', ...
- 101
1
vote
1
answer
260
views
Greenplum add a column to table and insert value to it in incremental order
I have a table st.student like following, which has one column.
STUDENT_ID
100001
100002
100003
100004
100005
100006
I need to insert a column to this and values in it should be incremental ...
- 13
0
votes
1
answer
228
views
What is the fastest way copy 800Gb data from Greenplum to MS SQL Server
Our company has a base on Greenplum with a total volume of almost 800 GB. What is the fastest way to transfer all data from Greenplum to a clean database on the SQL Server?
2
votes
1
answer
2k
views
Transpose rows to column in postgresql
The following query gives me a list of 1222 distinct columns:
select distinct column_name from information_schema.columns where table_name like 'fea_var%';
I want to create one base table which has ...
- 31
0
votes
0
answers
242
views
MS SQL Linked Server Insert Error
As part of data migration, I have created linked server for Greenplum database in MS SQL 2019. I am able to query using linked server. Select & insert commands are working fine for most of the ...
- 1
1
vote
2
answers
795
views
Replicating PostgreSQL data into Citus/Greenplum?
I need to integrate data from 3 different PostgreSQL databases (OLTP application backends) in a data warehouse. For the data warehouse itself I consider using Citus or Greenplum. There is a ...
- 101
1
vote
1
answer
850
views
Resolve parametrized schema in PostgreSQL
I'm looking for a way to use a parametrized schema in the DECLARE section of a PostgreSQL function.
Why am I looking this way ?
This functions will refer %ROWTYPE from multiple schema like
Var1 s1....
0
votes
1
answer
4k
views
Greenplum ERROR: Canceling query because of high VMEM usage
We have a GreenPlum Cluster which we have set up recently and getting this error on a single query run:
current group id is 140611, group memory usage 40720 MB, group shared memory quota is 31320 MB, ...
- 141
0
votes
1
answer
73
views
How to delete all rows in a Greenplum row oriented table
In Greenplum, what is the best practice to delete all rows from a table? DELETE FROM TABLE or TRUNCATE TABLE? I dont have any child tables or foreign key constraints.
- 1
0
votes
2
answers
168
views
Using IMPORT FOREIGN SCHEMA with Greenplum database
I have setup a PostgreSQL 12 database and am trying to connect to a Greenplum database in order to create proxy tables. I am able to connect to the Greenplum db, but I get an error when I try to use ...
- 1
0
votes
1
answer
74
views
Fetching rows from million rows table(Optimization)
I have millions of rows in a table of Greenplum database out of which I have to fetch around 45k rows and store them in a python List.
It's taking more than 2hrs to fetch the data. How can I optimize ...
2
votes
1
answer
280
views
How to narrow down records in SQL based on conditions
Customer Rank Joining_date salary
A 2 2017-10-12 500
A 1 2017-10-10 800
A 1 2017-10-20 400
B ...
- 123
0
votes
1
answer
273
views
Count days between dates and find largest gap
I need to find all members in our database from a calendar month that do not have a gap of 10+ days between sending data / or in the month total i.e. everyone in April 2018. The table is set up like ...
- 13
1
vote
2
answers
76
views
Are sql tuning ways same for different DB engine？ [closed]
I used Oracle for the half past year and learned some tricks of sql tuning,but now our DB is moving to greenplum and the project manager suggest us to change some of the codes that writted in Oracle ...
- 19
2
votes
2
answers
211
views
Greenplum DB changing table column length
I am new to using Greenplum DB, was working on Oracle DB. I understand conceptually Greenplum in columnar DB and has different workings then Oracle.
The question I have is regarding altering table ...
- 21
1
vote
1
answer
372
views
Can a plpgsql stored procedure monitor for a query and kill it?
I want to write a function that monitors for a particular query becoming a blocker (i.e. it is both blocked by a query, and is blocking another query) and terminate it. Here is my current code, which ...
- 539
1
vote
0
answers
58
views
Why gather stats on all columns?
Is there a good reason why I should not turn the column statistics down to 0 on columns that are not used in JOIN, WHERE, GROUP BY, or window functons etc.?
It strikes me that the default statistics ...
- 539
9
votes
1
answer
16k
views
How to get the sequence name for a serial column
As far as I can tell, this query should show the expression for the new value of a serial column:
select d.adsrc
from (
SELECT a.attrelid, a.attnum, n.nspname, c.relname, a.attname
FROM pg_catalog....
- 539
3
votes
0
answers
359
views
Why `update` and `delete` acquire EXCLUSIVE lock rather than ROW EXCLUSIVE lock in Greenplum?
As we known, insert,update and delete commands automatically acquire ROW EXCLUSIVE lock in PostgreSQL. As a fork of PostgreSQL 8.2, why in Greenplum update and delete commands automatically acquire ...
- 31
1
vote
1
answer
110
views
How to retrieve a view column derivation in PostgreSQL or Greenplum
I want to retrieve the derivation expression of a column in a view, and I want to do it dynamically from within a stored procedure, so psql \d+ or pgAdmin is no use to me.
Where can I retrieve this ...
- 539
0
votes
1
answer
1k
views
Get partition column for a table in Greenplum
How can I find what the partitioning scheme is for a table? Is it in information_schema or in the pg_class related tables?
- 539
1
vote
2
answers
1k
views
Greenplum : Inserting non-duplicate rows on unique constraint violation
I have a stored procedure that does the following :
INSERT INTO schema.my_unique_values
SELECT DISTINCT id, value
FROM schema.a_huge_table
WHERE NOT EXISTS (SELECT 1
...
- 111
2
votes
1
answer
108
views
Why would a Greenplum procedure not recognize a view change?
I have a procedure that loops through a query that returns a set of dates, recreates a set of views using the date in the WHERE clause, and the calls a series of other procedures that use these views. ...
- 539
3
votes
1
answer
763
views
How bad is it to delete from a Greenplum AO table?
We have a few AO tables (which nowadays means "Append Optimized", not "Append Only"). Normally, we just insert into these tables.
As part of a significant system change, I need to do a fairly large ...
- 539
2
votes
2
answers
313
views
Upgrading Greenplum DB that includes PostGIS extension
We've got a number of 4.2.3.2 Greenplum databases that are running PostGIS 1.4. We are trying to get these databases onto a more recent version of Greenplum, such as 4.3.8.1 or the re-released ...
- 9,342
0
votes
1
answer
121
views
Partition elimination and date variables
I have a table that is partitioned on a date column, with a default partition. In our deletion process, we drop partitions that are older than the retention date and then delete records that are older ...
- 539
1
vote
1
answer
73
views
What could cause a text comparison to fail?
DB Version: PostgreSQL 8.2.15 (Greenplum Database 4.3.4.1 build 2) on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.4.2 compiled on Feb 10 2015 14:15:10
I'm having a problem in one of our ...
- 539
6
votes
2
answers
3k
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 ...
- 539
1
vote
0
answers
44
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?
- 539
0
votes
1
answer
77
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?
- 539
1
vote
3
answers
705
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 ...
- 111
1
vote
1
answer
3k
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
...
- 539
-1
votes
1
answer
687
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 ...
- 99
1
vote
1
answer
1k
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 ...
- 539
4
votes
2
answers
911
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 ...
- 539
-2
votes
1
answer
5k
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 ...
- 217
0
votes
0
answers
192
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
psql:/home/josh/...
- 2,085
1
vote
0
answers
344
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 ...
- 111