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 1rPqb6-00EeFN-0D for pgsql-general@arkaria.postgresql.org; Tue, 16 Jan 2024 21:03:48 +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 1rPqb5-007mIu-8P for pgsql-general@arkaria.postgresql.org; Tue, 16 Jan 2024 21:03:47 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rPqb4-007mIm-Rz for pgsql-general@lists.postgresql.org; Tue, 16 Jan 2024 21:03:46 +0000 Received: from mail-oa1-x2d.google.com ([2001:4860:4864:20::2d]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rPqb2-002BE6-Ix for pgsql-general@postgresql.org; Tue, 16 Jan 2024 21:03:46 +0000 Received: by mail-oa1-x2d.google.com with SMTP id 586e51a60fabf-21086556f32so361041fac.3 for ; Tue, 16 Jan 2024 13:03:44 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1705439022; x=1706043822; darn=postgresql.org; h=content-transfer-encoding:in-reply-to:from:references:to :content-language:subject:user-agent:mime-version:date:message-id :from:to:cc:subject:date:message-id:reply-to; bh=EvfRsBNeIKMuTlFvqE/FQcxAN1yCI/8xa+taOg6wuNU=; b=cNNXWk/mUIDhbhumhWS/KfLeM+MqGIO5j4C7NVR48jHmuyCHd8fh8zdxEzr+KNq2sX FDtAvLAQnYiOsjsZsYixwd7dYTydibmybPgDahNiARAbDuya1atcnFtY61NRtBLoZWrC O76yoHejtTgglXBdjg0qY9+XLRN7C/TCjzPfgnDH6mMEZ5R5f3LAWHRoY3R5Zs0HrunH elkW6/8G3MzLDzMXyXSr/+oJBTn+hDkVVabD81kUwI7APLNsKc3XCHKGkZ8CF2tD38U+ JMjWqF+IYFrhNzFZbUwt7eEWsuPSeLAYOspUhCZMIGmwNo1Sppw1z4LxrW4XPMy6w3gE Xzxw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1705439022; x=1706043822; h=content-transfer-encoding:in-reply-to:from:references:to :content-language:subject:user-agent:mime-version:date:message-id :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=EvfRsBNeIKMuTlFvqE/FQcxAN1yCI/8xa+taOg6wuNU=; b=MyjAY1Kn7OD0c2SRZcKqCj7pvfzVjEt/4hO8QXipBcF+Arh4DvyYg5KVtJPAadY0F2 DQ9IV0CfunSEXXjms9vkTocZrUbzp9I/Xc7YHIxxVURKnzsCGugq5MaoqOM7aHwXUaak ctoj8cCPcIS/j1yvI4fycekR6cRMeD7LTbrhScct3U5ST8mE0sFi9ivakR/+zyIZ6eMR MtJIRi6uhNAEyuRIt+Wp2t2F/H7KSyNcPi+d1NQh9/1ffH0Fc/mNllG2Z9GN6SXAYr73 maF7eO39XLjHBULD5qk20kZhSvO0FEuS5+vts6a03sfQRkHmeemtjov4wuqrNPaynTSe qKHQ== X-Gm-Message-State: AOJu0Yzp0AyVzheX3DgLBpcEWk22NgzTV6pXkS0Iukagz8ghAz9YmuYi f1p9vxBZRdIJXcSPJ0Ewd0c/zX1wgGN1 X-Google-Smtp-Source: AGHT+IHl8yYbUSBteGeaL/YTsGjf0kZviJNx1mT33TIC9AkxVOnHVQZCGDXLjhP7SLTvk3EiDnMN7A== X-Received: by 2002:a05:6871:d281:b0:206:9ffd:b8d8 with SMTP id pl1-20020a056871d28100b002069ffdb8d8mr10369894oac.56.1705439022410; Tue, 16 Jan 2024 13:03:42 -0800 (PST) Received: from [192.168.2.16] (070-113-014-216.res.spectrum.com. [70.113.14.216]) by smtp.gmail.com with ESMTPSA id dj10-20020a0568303a8a00b006d7eaaa65a4sm1984539otb.71.2024.01.16.13.03.41 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Tue, 16 Jan 2024 13:03:42 -0800 (PST) Message-ID: <4db13aae-f1c5-4fc2-96e3-36216755d9ba@gmail.com> Date: Tue, 16 Jan 2024 15:03:33 -0600 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Mimic ALIAS in Postgresql? Content-Language: en-US To: Ron Johnson , pgsql-general References: From: Jim Nasby In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 1/16/24 11:20 AM, Ron Johnson wrote: > Some RDBMSs have CREATE ALIAS, which allows you to refer to a table by a > different name (while also referring to it by the original name). > > We have an application running on DB2/UDB which (for reasons wholly > unknown to me, and probably also to the current developer) extensively > uses this with two schemas: MTUSER and MTQRY.  For example, sometimes > refer to MTUSER.sometable and other times refer to it as MYQRY.sometable. > > My goal is to present a way to migrate from UDB to PG with as few > application changes as possible.  Thus, the need to mimic aliases. > > Maybe updatable views? > CREATE VIEW mtqry.sometable AS SELECT * FROM mtuser.sometable; Based on the schema names one possibility is that the aliases are there as a pseudo-api between people/tools writing queries and the base tables. IE: if you needed to make a (maybe backwards-incompatible) change to "sometable" you now at least have the option of creating a MTQRY.sometable *view* that hides whatever change you're making to MTUSER.sometable. In any case, yes, an updatable view would provide equivalent behavior in Postgres. -- Jim Nasby, Data Architect, Austin TX