Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sFFyh-000Woe-J2 for pgsql-general@arkaria.postgresql.org; Thu, 06 Jun 2024 16:28:40 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sFFyf-001sAw-Sr for pgsql-general@arkaria.postgresql.org; Thu, 06 Jun 2024 16:28:38 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sFFyf-001sAn-DC for pgsql-general@lists.postgresql.org; Thu, 06 Jun 2024 16:28:38 +0000 Received: from mail-oi1-x231.google.com ([2607:f8b0:4864:20::231]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sFFyd-0001go-Gb for pgsql-general@lists.postgresql.org; Thu, 06 Jun 2024 16:28:37 +0000 Received: by mail-oi1-x231.google.com with SMTP id 5614622812f47-3d1fd550871so605583b6e.2 for ; Thu, 06 Jun 2024 09:28:35 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=istation.com; s=google; t=1717691315; x=1718296115; darn=lists.postgresql.org; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=/m/fm5ibTwV1AXjoAgFNxkKDBWU7zQxSpUMcmK/cfTg=; b=Djwom5T16s5rfzq67pmDjbYkPG8IZ2NToIXEhxmqQPOG174K2zYqQM2+UU/JdsArkE DhGfThuIBPHpOMcJN5FE+/F/bJoD6Ow/W14tuw75P4vCgV4b9fZxKedyCMJCWRy5cHyy 7ZDHL6sf+xtLDl75/bnXT2togxgzH+WyWOtvc= X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1717691315; x=1718296115; h=to:subject:message-id:date:from:mime-version:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=/m/fm5ibTwV1AXjoAgFNxkKDBWU7zQxSpUMcmK/cfTg=; b=cEaXtfl/llaTuzxSDF+X2tQiVSGkVmaMgqD15LTWCyim/SZVr8rhNcftWf9GpCLbLG jAoNh+vD8FRds+XgekfQbQHzS49ybmC0NuIxAeiQ7nZK6Fati7NBCIIyLlBUl4g1UTST XCfv6dT9WZ8THbdI0ipREUPLxjhUJzHMsBXe8BOofmwJosKWa2t5NjFyhPgq109jTCc4 8K6R/MttRKVDAcPfakUv3YU8uZ8jLZ2O5FdbJ+1BbiZzISxlUScHgZSNNZB4KRG9vphb PCddCG3uSmk5K/co+7LTaMy+xWAxDs82PZyjHWV+V9YI/ik6lXJOzoKlQfBO5d1vSGV0 +nRg== X-Gm-Message-State: AOJu0YxvoD2i82YcbNqmv4O24lM545dPzlpYcTnq2VwxiFdO1L7xZMC4 6zF5WIa9q872MycLQ/wOpkafIFODoOhiTBQdyZxJuKXuzE2tBDqFENS5aDi+oD0x1O9SL1RLmLZ fs5JO+WV5GCRoKaUY9wuzGFCo/tszvDH8Vk/te4+ggYUJ26yZlQ== X-Google-Smtp-Source: AGHT+IGxkIkowyjXaehn328cF2Tu+jtXHn4N+M9NLxTIQEUh8qcc1zmoaHCIAc3yi5bC+ajaxjUv9T/knPSs4cveeKc= X-Received: by 2002:aca:130c:0:b0:3c9:7368:2217 with SMTP id 5614622812f47-3d2043a5a53mr6527487b6e.31.1717691314680; Thu, 06 Jun 2024 09:28:34 -0700 (PDT) MIME-Version: 1.0 From: David Barbour Date: Thu, 6 Jun 2024 11:28:20 -0500 Message-ID: Subject: Oracle to Postgres - Transform Hash Partition To: pgsql-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000a54f38061a3b2cf7" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000a54f38061a3b2cf7 Content-Type: text/plain; charset="UTF-8" Hi, New to this list and to Postgres. Been an Oracle DBA for quite a while and we're moving from Oracle to Postgres. I have a table that I need to transform into a hash partitioned table. I've gone through all the steps to create a hash partitioned table, with 8 partitions such as create table idev.assessment_result_2023_dab_part (like idev.assessment_result_2023_dab)partition by hash (district_oid); CREATE TABLE idev.assessment_result_2023_dab_part_assessment_result_2023_p1 PARTITION OF idev.assessment_result_2023_dab_part FOR VALUES WITH (modulus 64, remainder 0) ....etc through partition 2023_p8 FOR VALUES WITH (modulus 64, remainder 7) Now I need to 'attach' the original table. The problem I'm running into is there are no good examples of how to define the values. I've tried several iterations of various 'for values', 'values', 'for values with', etc. but they all error out. Here's an example: alter table idev.assessment_result_2023_dab_part attach partition idev.assessment_result_2023_dab for values with(modulus 8, remainder 1) to (modulus 8, remainder 7) ERROR: syntax error at or near "to" LINE 2: ..._2023_dab for values with(modulus 8, remainder 1) to (modulu... Any assistance would be appreciated. -- *David A. Barbour* *dbarbour@istation.com * *(214) 292-4096* Istation 8150 North Central Expressway, Suite 2000 Dallas, TX 75206 www.Istation.com CONFIDENTIALITY / PROPRIETARY NOTICE: The information contained in this e-mail, including any attachment(s), is confidential information that may be privileged and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient, or if you received this message in error, then any direct or indirect disclosure, distribution or copying of this message is strictly prohibited. If you have received this message in error, please notify Istation by calling 866-883-7323 immediately and by sending a return e-mail; delete this message; and destroy all copies, including attachments. Thank you. --000000000000a54f38061a3b2cf7 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi,

New to this list and to Postgres.= =C2=A0 Been an Oracle DBA for quite a while and we're moving from Oracl= e to Postgres.

I have a table that I need to trans= form into a hash partitioned table.=C2=A0 I've gone through all the ste= ps to create a hash partitioned table, with 8 partitions such as=C2=A0

create table idev.assessment_result_2023_dab_part= (like idev.assessment_result_2023_dab)partition by hash (district_oid);

CREATE TABLE idev.assessment_result_2023_dab_par= t_assessment_result_2023_p1 PARTITION OF idev.assessment_result_2023_dab_pa= rt
=C2=A0 =C2=A0 FOR VALUES WITH (modulus 64, remainder 0)

....etc through partition 2023_p8 FOR VALUES WITH (modulus = 64, remainder 7)

Now I need to 'attach' th= e original table.=C2=A0 The problem I'm running into is there are no go= od examples of how to define the values.=C2=A0=C2=A0

I've tried several iterations of various 'for values', '= values', 'for values with', etc. but they all error out.
<= div>
Here's an example:
=C2=A0alter table idev.= assessment_result_2023_dab_part
attach partition idev.assessment_resu= lt_2023_dab for values with(modulus 8, remainder 1) to (modulus 8, remainde= r 7)

ERROR: syntax error at or near "to" LINE 2: ..._2023_dab for values with(modulus 8, remainder 1) to (modulu...<= /span>

Any assistance would be appreciated.--

David A. Barbour

dbarbour@istation.com

(= 214) 292-4096

Istation

8150 North Central Expressway, Suite 2000

=

Dallas, TX 75206

www.Istation.c= om

=C2=A0

CONFIDENTIALITY / PROPRIET= ARY NOTICE:

The information contained in = this e-mail, including any attachment(s), is confidential information that = may be privileged and exempt from disclosure under applicable law. If the r= eader of this message is not the intended recipient, or if you received thi= s message in error, then any direct or indirect disclosure, distribution or= copying of this message is strictly prohibited. If you have received this = message in error, please notify Istation by calling 866-883-7323 immediately and by sending a= return e-mail; delete this message; and destroy all copies, including atta= chments.

Thank you.

--000000000000a54f38061a3b2cf7--