All versions of greenplum. Add a version-specific tag like greenplum-4.3 if that context is important.
Questions tagged [greenplum]
42 questions
9
votes
1 answer
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.pg_attribute a, pg_namespace n, pg_class c
WHERE…
PhilHibbs
- 539
- 1
- 7
- 22
6
votes
2 answers
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 for all the indexes that match the top level index…
PhilHibbs
- 539
- 1
- 7
- 22
4
votes
2 answers
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 from PostgreSQL at version 8.2, so it does not have…
PhilHibbs
- 539
- 1
- 7
- 22
3
votes
0 answers
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 EXCLUSIVE lock, what's the reason for Greenplum…
XiangZzz
- 31
- 1
3
votes
1 answer
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 delete from these tables. It's only about 2% of the…
PhilHibbs
- 539
- 1
- 7
- 22
2
votes
0 answers
Greenplum/PostgreSQL: Querries run individually, but won't run in plpgsql function
We are running Greenplum 6.28.1, which is built on PostgreSQL 9.4.26. We have a master node and 6 server nodes, all running Oracle Linux release 8.10.
We have 5 queries that progressively build temp tables that are use to generate a final result…
lpscott
- 33
- 4
2
votes
1 answer
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 all the 1222 rows from this query as columns.…
Reshmi Nair
- 31
- 1
- 3
2
votes
1 answer
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 2 2017-05-20 200
B …
user8545255
- 123
- 3
2
votes
2 answers
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 column length. I created a table with a column with…
user5319411
- 21
- 2
2
votes
1 answer
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. So, if the loop contains three dates, it runs…
PhilHibbs
- 539
- 1
- 7
- 22
2
votes
2 answers
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 version of 4.3.9.1, but we're running into a mess of…
John Eisbrener
- 9,547
- 6
- 31
- 65
1
vote
0 answers
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 instead. The same thing also apply when using a…
Sufendy
- 111
- 4
1
vote
1 answer
Greenplum/Postgres - Why not backward index scan?
I have this query running on Greenplum 6(Postgres 9.4) too slow as it performing a sequential scan. But I am thinking why it's not performing an index backward scan to fetch the records since there is an index on the same column.
explain analyse…
goodfella
- 589
- 4
- 14
1
vote
1 answer
How a B-Tree index on a columnar table would look like?
Greenplum database supports B-tree index on append-optimized columnar tables which allows UPDATE operations as well . Even though it's not a recommended practice to have index on such tables(probably because they are intended for append-only and do…
goodfella
- 589
- 4
- 14
1
vote
1 answer
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 working in Greenplum 6.22/Postgresql…
lpscott
- 33
- 4