New answers tagged

2

Not an Ideal Configuration PostgreSQL was created such that the permissions internal to the database are intended to mirror the system directly. You can get around that and it won't be too painful, but you're supposed to run psql as user1 and user2 per your example. Now what you're asking to do is an especially bad idea. postgres isn't a regular user, it's a ...


2

Create unix user accounts for each person, and set user-name maps in pg_ident so that everyone connects as postgres. https://www.postgresql.org/docs/10/auth-username-maps.html or alternatively create individual database users for each account and give them superuser privs. Optionally "set session authenticaton postgres" can be set as a startup ...


2

pg_total_relation_size() sums up everything that belongs to a table, including bloat. But \dt in psql does not list all tables of a database, it only includes schemas listed in the current search_path. Check with: SHOW search_path; System tables in pg_catalog are always excluded. And there may be additional schemas. To see all tables in the database: \dt *.*...


1

Your insert syntax is incorrect, it should look like insert into <table> select <something> from <table> -- not like insert into <table> (something, something, (select .... And don't sprinkle parentheses at random -- they are meaningful. INSERT INTO samples SELECT nextval('my_sequence'), 'newsample', locations FROM samples WHERE ...


3

\ds without argument doesn't show sequences that are not accessible through the current search_path, whereas information_schema.sequences ignores the search path. On the other hand, information_schema.sequences won't show sequences that the current user cannot actually use, whereas \ds won't filter those out. According to the documentation: Only those ...


1

Don't write a trailing semicolon. If you go through the history, remove the trailing semicolon. That will put the query in the buffer, and you can edit it with \e.


Top 50 recent answers are included