PostGre popularity

[nimasdj]

How popular is PostGre? HostGator charges 50$ to install it. Does it worth my money and time to get and learn it?

[Dormilich]

Postgre is certainly one of the more capable RDBMS.

[Lemon_Juice]

@nimasdj

How popular is PostGre?

Not PostGre but PostgreSQL or Postgres.

Much less popular than MySQL but still used by many people and very actively maintained. It is regarded as the best free database. If you plan to build medium to large scale applications it’s well worth the effort to learn it. It’s a bit more complex and difficult than MySQL but has more features that help in the long run.

Recently, it’s my database of choice for all serious projects but if I build a very simple web site or small system I still choose MySQL. I’d say for simple needs MySQL is easier but for serious needs Postgres is easier.

[nimasdj]

I created a pg database and assigned a user to it via cPanel.
When I use below in phpPgAdmin

grant all privileges on database [username]_mydb to [username]_username

under the database name I get no error there, but I still get no permission error. when I run the command in upper level than the database under PostGreSQL, I get login failed error. I also tried to public. no success yet.

what should I do?

And does PostGre has something equivalent to enum?
from here https://www.postgresql.org/docs/9.1/static/datatype-enum.html it appears it has. I ran that command and got no error, but in phpPgAdmin I still see it is ‘varying characters’. Why?

[Lemon_Juice]

@nimasdj

grant all privileges on database [username]_mydb to [username]_username

under the database name I get no error there, but I still get no permission error

I suppose granting privileges is only available for super users - by default the super user is postgres - you should log in as postgres, probably.

@nimasdj

And does PostGre has something equivalent to enum?from here https://www.postgresql.org/docs/9.1/static/datatype-enum.html it appears it has. I ran that command and got no error, but in phpPgAdmin I still see it is ‘varying characters’. Why?

Not exactly an equivalent but a much better and fuller implementation of enum - you create your enum types for the whole database and then use them in any tables you want.

I’ve never used phpPgAdmin because it’s very basic, it’s even more primitive than phpMyAdmin - don’t use it because you won’t learn what Postgres really has to offer. Try pgAdmin 3 (pgAdmin 4 is not production ready yet and I would hold off now).

[nimasdj]

If I install the Linux version of pgAdmin3 on my website (I am root) how can I use it as this is graphical? how possible?

[Lemon_Juice]

@nimasdj

If I install the Linux version of pgAdmin3 on my website (I am root) how can I use it as this is graphical? how possible?

You don’t have to install it on your web site. Install it on any computer you are working on and connect to your database over the network. If it was installed on the same system as your web site then you’d connect to localhost but on other machines you connect via the remote IP address or host name. The default port for connection is 5432.

[nimasdj]

I understood. I asked if I install its linux version on my website under /usr folder as mentioned in docs, then how can I access its GUI on my website as it is graphical?

[Lemon_Juice]

PgAdmin 3 is a desktop application so you can’t run it through a web site. Apparently, PgAdmin 4 will be able to do so because it’s written in javascript in order to run it both on the desktop and in the browser - but I haven’t used it apart from a very short test run so I can’t say anything more about it.

[nimasdj]

I set below to add my ip for remote but I get error when I want to restart.

in pg_hba.conf I added:
host all all 2.177.0.0/16 trust

and in postgresql.conf I changed the line below to:
listen_addresses = ‘*’

[chorn]

@nimasdj

I get error when I want to restart

which

[Lemon_Juice]

Sorry, I have no experience with installing Postgres on Linux. It was installed for me by my host. The windows installer took care of everything automatically. Maybe try asking in the database forum.

[nimasdj]

Finally I was abale to login with pgAdmin, but even here I don’t set “enum” data type! I think you said phpPgAdmin is not complete, so why enum is not in pgAdmin neither?

[Mittineague]

So you declared an enumerated type as described in the documentation linked to in post # 5 but whatever you named it isn’t showing in pgAdmin?

[nimasdj]

Yes, neither in pgAdmin nor in phpPgAdmin. Why?

[Mittineague]

AFAIK Postgres does not have an easy equivalent to MySQLs “SHOW CREATE TABLE table_name”

You could do a dump file and look at that.

But I think it might be easier to query the information_schema table and look for it there.

37.1. The Schema

33.1. The Schema
The information schema itself is a schema named information_schema. This schema automatically exists in all databases. The owner of this schema is the initial database user in the cluster, and that user naturally has all the privileges on this schema, including the ability to drop it (but the space savings achieved by that are minuscule).

By default, the information schema is not in the schema search path, so you need to access all objects in it through qualified names. Since the names of some of the objects in the information schema are generic names that might occur in user applications, you should be careful if you want to put the information schema in the path.

[Lemon_Juice]

@nimasdj

Finally I was abale to login with pgAdmin, but even here I don’t set “enum” data type! I think you said phpPgAdmin is not complete, so why enum is not in pgAdmin neither?

Enum is there but it is different than in MySQL and it is not in the types list to choose from because there is no generic enum type like in MySQL - you create each enum type yourself and then use it - then the enum type is not named enum but it has the name you have given it:

1. Go to options > Display and make sure the Types are selected to be visible in the tree.
2. In the tree right click on Types → New Type and create data type Enumeration, name it and add your values (labels)
3. Your type will be visible in the types list when creating tables (under the name like public.mytypename)

[felgall]

@Lemon_Juice

there is no generic enum type like in MySQL

mySQL would be dramatically improved by the removal of that useless type. Any enum is best replaced by adding another table consisting of keys and values where the key then replace the enum with the key field - that way you don’t need to redefine the table when the enum values change.

[Lemon_Juice]

@felgall

mySQL would be dramatically improved by the removal of that useless type. Any enum is best replaced by adding another table consisting of keys and values where the key then replace the enum with the key field - that way you don’t need to redefine the table when the enum values change.

Postgres enum is a bit more useful because it is a defined entity separate from the table and you can add values without a table scan (but you can’t change them easily). Of course, enums are not meant for values that change or are provided by user, they are meant only for internal programmatic values that will stay constant for the lifetime of the database. They can save some disk space and unnecessary tables and if you are aware of their limitations and can accept lack of portability then they are perfectly fine.

[felgall]

@Lemon_Juice

they are meant only for internal programmatic values that will stay constant for the lifetime of the database

in practice there is no such thing - at some point there is always a need to add or remove a value for any enum. So it really comes down to how difficult you want to make it for when that occurs as opposed to a minor saving in storage.