New answers tagged

0

You need \set (not \pset!) to set psql variables. Unlike assignment in a Unix shell the psql assignment is not capable of arithmetic operations. You could use the psql command \! to execute shell commands, but I'll suggest two different approaches: Option 1: Let Postgres calculate and set new variables with \gset The manual about \gset: Sends the ...


0

There are good reasons ALGORITHM=INPLACE may not be used and switches to ALGORITHM=COPY. REASON #1 In the link you gave in the question, there is a chart that shows the following: Adding a FULLTEXT index trigger a table copy is not supported for online operation Many operations can revert to copy because it says in the notes Although ALGORITHM=INPLACE is ...


0

You cannot alter the LOGGED attribute of a LOB column. However, you can alter the table by adding a new LOGGED LOB column, update the new column from the existing column, then drop the original column. It may require less wasted disk space but is unlikely to be faster than using ALTOBJ.


0

Please try this: set long 99999999 set pages 1000 exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'PRETTY', true); exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'SQLTERMINATOR', true); select dbms_metadata.get_ddl('<object_type>','<object_name>','<owner>') from dual;


1

The following will get rid of the LF and 2 spaces using a function though it would probably be better/easier to do it in your text editor. select REPLACE(dbms_lob.substr( dbms_metadata.get_ddl('FUNCTION','REMAP_SCHEMA', 'ETL'), 37, 1), CHR(10)||CHR(32)||CHR(32)||'CREATE','CREATE') text from dual Obviously the first word that you are ...


0

Direct answers to your three questions would be; Yes it does. When you replace your view, the lock of view object will be AccessExclusiveLock. Yes, even the 'select' queries that uses that specific view will wait until the 'create or replace' statement is committed. Yes they do. After the "view changer" query is committed the queries that are waiting will ...


1

In Oracle a schema and a user is the same thing. In Postgres it isn't. So there is no direct "mapping" on what you did in Oracle in "Postgres land". A table is always owned by the user who created it initially there is no way to change that. If you do not want to give the user ink the privilege to create tables, the "Postgres" way would be to give that ...


0

Give ink the right to create tables in the database and connect yourself as ink to the database. Then all the tables that you create there are owned by ink. PostgreSQL has a different approach on this then Oracle. The schema and owner are 2 different 'things'.



Top 50 recent answers are included