From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jim Rosenberg <jr@amanue.com>
cc: pgsql-general@postgresql.org
Subject: Re: Column name beginning with underscore ("_")?
In-reply-to: <20241104150203.f808f1e63e67dddcd3204d29@amanue.com>
References: <20241104150203.f808f1e63e67dddcd3204d29@amanue.com>
Comments: In-reply-to Jim Rosenberg <jr@amanue.com>
	message dated "Mon, 04 Nov 2024 15:02:03 -0500"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2892516.1730751053.1@sss.pgh.pa.us>
Date: Mon, 04 Nov 2024 15:10:53 -0500
Message-ID: <2892517.1730751053@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/2892517.1730751053%40sss.pgh.pa.us>
Precedence: bulk

Jim Rosenberg <jr@amanue.com> writes:
> Is it considered bad Postgresql practice to have a column name that begins
> with the underscore character ("_")?

I wouldn't say so.

> I'm not sure where this is documented, but I'm seeing that Postgresql
> accepts prepending an underscore to a data type name as a kind of alias for
> appending [] to define an array data type. So even though Postgresql
> doesn't seem to have this problem, a human reader might confuse a column
> name beginning with _ as an array data type reference.

Yeah, the name of the array type associated with a base type "foo"
is normally "_foo" (with some weird rules if that causes a conflict).
So it's best to avoid naming data types with leading underscores ---
and, because tables have associated composite types, that rule applies
to tables as well.  But there's no direct restriction on column names.

If you want to keep things simple and avoid leading underscore across
the board, nobody will say that's wrong either.

> Here is why I want to have some column names beginning with "_". I'm
> designing a database to shadow a public agency's data. I need some columns
> that reflect *my* shadow copy of the data, (like say download date) that
> don't have any semantic import with respect to the original data.
> Beginning such columns with "_" is a simple way to keep the column names
> uncluttered but indicate to the reader that the column applies to *my
> copy* but are not columns in the original data.

Of course, if they decide to name something "_foo", you're going to
need to figure out what to do with that.

			regards, tom lane