Thread: Column name beginning with underscore ("_")?

Is it considered bad Postgresql practice to have a column name that begins
with the underscore character ("_")?

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.

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.

Comments?

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

On 11/4/24 12:02, Jim Rosenberg wrote:
> Is it considered bad Postgresql practice to have a column name that begins
> with the underscore character ("_")?
> 
> 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.
> 
> 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.

To reduce confusion something like?:

my_* or l(ocal)_*



> 
> Comments?
> 
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com